PYTHON-DB Interface Specification

Threadsafety

Thread-safe level. Threadsafety This is an integer, the value range is as follows:

• 0: Thread-safe is not supported, multiple threads cannot share this module
• 1: Primary thread-safe support: threads can share modules but cannot share connections
• 2: Intermediate thread-safe support threads can share modules and connections, but cannot share cursors
• 3: Full thread-safe support threads can share modules, connections, and cursors.

If a resource is shared, it is necessary to use a synchronous primitive such as a spin lock or a semaphore to lock atomic targets. For this purpose, disk files and global variables are unreliable and may be hindered.

Paramstyle

The module supports the style of the SQL statement parameters. DB-API supports multiple ways of SQL parameter style. This parameter is a string that indicates how the string is substituted in the SQL statement.

Connect

The connection function. The Connect method generates a Connect object that we use to access the database. Modules that conform to the standard will implement the Connect method. The parameters of the Connect function are as follows:

User            Username  password        password  host            hostnamedatabase        Database namedsn             Data Source Name

Database connection parameters can be provided as a DSN string, or in the form of multiple positional-related parameters (if you know the order of the parameters explicitly), or as a keyword argument. Examples of applications:

Connect (dsn= ' myhost:mydb ', user= ' Guido ', password= ' 234$ ')

Of course, different database interface programs may differ, not all strictly by specification, for example, MYSQLDB uses the DB parameter rather than the canonical recommended database parameter to represent the databases to be accessed:

• MySQLdb.connect (host= ' Dbserv ', db= ' inv ', user= ' Smith ')
• Pgsql.connect (database= ' sales ')
• Psycopg.connect (database= ' template1 ', user= ' Pgsql ')
• Gadfly.dbapi20.connect (' Csrdb ', '/usr/local/database ')
• Sqlite3.connect (' Marketing/test ')

Abnormal

Modules that are compatible with standards should also provide the following exception classes:

Warning            Warning Exception base class error              Error exception base class Interfaceerror     Database interface error databaseerror      database error DataError           Error operationalerror    database execution Command error integrityerror      data integrity error INTERNALERROR      database internal Error Programmingerror    SQL execution failed Notsupportederror   attempt to perform an attribute not supported by the database  

Connection object

To communicate with a database, you must first establish a connection to the database. The basic functions of how the connection object handles commands to the server, and how to receive data from the server. Once the connection is successful (or a connection pool), you are able to send a request to the database server and receive a response.

Method

The connection object has no data properties that must be defined, but at least the following methods should be used:

Close ()         closes the database connection commit ()        commits the current transaction rollback ()      cancels the current transaction the cursor ()        object that creates and returns a cursor or class cursor using this connection ErrorHandler (cxn , cur,  errcls, Errval)  

Once the close () method is executed, attempting to use the method of the Connection object will result in an exception.

The commit () method does nothing for a database that does not support transactions, or if it supports transactions, but sets up an autocommit (auto-commit) database system. If you really need to, you can implement a custom method to turn off autocommit behavior. Because DB-API requires this method to be implemented, this method only needs to have a pass statement for a database that does not have a transactional concept.

Like commit (), the rollback () method is only meaningful for databases that support transactions. After executing rollback (), the database reverts to the state before the transaction was committed. Depending on PEP249, closing the database connection before committing commit () will automatically call the rollback () method.

For a database that does not support cursors, the cursor () method still returns an object that mimics the cursor object as much as possible. These are the minimum requirements. Developers of specific database interface programs can add additional properties to their interface programs.

Cursor Object

A cursor allows the user to execute database commands and obtain query results. A Python Db-api Cursor object always plays the role of a cursor, regardless of whether the database actually supports cursors. It is also said that the database interface program must implement the cursor object. After you create a cursor object, you can execute a query or other command (or multiple queries and multiple commands), or you can remove one or more records from the result set.

Properties and methods that are owned by the cursor object:

 ArraySize uses the Fechmany () method to remove as many records at a time as the default value is 1connectionn the connection (optional) that creates this cursor object description returns the cursor activity state (a meta of seven elements Group): (Name, Type_code, display_size, internal_ size, precision, scale, NULL_OK); Only name and Type_code are required LASTROWID return the ID of the last updated row (optional), if the database does not support row IDs, default returns to None) rowcount the last Execute () operation returns or  The number of rows affected. Callproc (Func[,args]) calls a stored procedure close () Closes the cursor object execute (Op[,args]) executes a database query or command Executemany (Op,args) like Execut The combination of E () and map () prepares and executes a database query/command for each given parameter Fetchone () gets the next line of the result set Fetchmany ([Size=cursor.arraysize]) to get the next few rows of the result set (= S      ize) Fetchall () returns all remaining rows in the result set __iter__ () creates an iterative object (optional; see next ()) messages The list of information returned by the database after the cursor executes (tuple collection) (optional) Next ()  Use an iterative object to get the next row of the result set (optional; Fetchone (), see __iter__ ()) Nextset () move to the next result set (if supported) RowNumber the index of the current result set (in behavior units, Starting from 0) (optional) setinput-sizes (sizes) sets the input maximum (must have, but the specific implementation is optional) setoutput-size (Size[,col]) to set the buffer capitalization of the large column (must have, but the implementation is optional) 

The most important properties of a cursor object are the Execute() and Fetch() methods. All requests to the database server are completed by them. For the Fetchmany () method, it is useful to set a reasonable ArraySize property. Of course, it is best to turn off the cursor object when it is not needed. If the database supports stored procedures, you can use the Callproc () method.

Type objects and constructors

Typically two different system interfaces require inconsistent parameter types, such as when Python calls the C function, the data format is required for the conversion between the Python object and the C type, and vice versa. Similarly, between a Python object and a native database object. For Python Db-api developers, the parameters you pass to the database are in string form, but the database will convert it to a number of different forms as needed. To ensure that each query can be executed correctly.

For example, a Python string might be converted to a VARCHAR, or a text, or a blob, or a native BINARY object, or a DATE or time object. What type of a string will be converted to? Care must be taken to provide input as much as possible with the data type expected by the database, so another DB-API requirement is to create a constructor to generate a special object so that the Python object can be easily converted to the appropriate database object. The following list describes the classes that you can use for this purpose. The null value of SQL is mapped to a null object of Pyhton, which is None.

Date (yr,mo,dy)      datetime value Object Time (hr,min,sec)   time value object Timestamp (yr,mo,dy, HR, min,sec) timestamp      object Datefromticks (Ticks) Get the date timefromticks (ticks) by the number of ticks seconds since 1970-01-01 00:00:01 UTC Get the time value object by 1970-01-01 seconds since 00:00:01 ticks UTC Timestampf Romticks (ticks) Gets the timestamp object binary (String) object that corresponds to the binary long string value by ticks seconds since 1970-01-01 00:00:01 UTC, the object string that        describes the string column, For example        , varcharbinary describes a binary long column object such as RAW, Blobnumber describes the object        of the numeric column DateTime describes the object of the      date Time column rowid The object          that describes the row ID column

Db-api Manipulating Database processes

Database Operations Example

#! /usr/bin/env python#-*-Coding:utf-8-*-# *************************************************************# Filename @ operatemysql.py# Author @ huoty# Create Date @ 2015-08-16 10:44:34# Description @# *************************************************************ImportMySQLdb# Script starts from here# Connect to DatabaseDb_conn=MySQLdb.Connect(Host=' localhost ',User=' Root ',passwd=' 123456 ')# If you have already created a database, you can connect to the database directly as follows#db_conn = MySQLdb.connect (host = "localhost", user = "Root", passwd = "123456", db = "TestDB")Common parameters for the Connect method: Host: The database hostname. The default is the local host User: the database login name. The default is the current user passwd: Database login secret. The default is NULL DB: The name of the database to use. No default value port: The TCP port used by the MySQL service. Default is 3306 CharSet: Database Encoding "" "# Get Action CursorCursor=Db_conn.Cursor()# Execute SQL statements using the Execute methodCursor.Execute("Select VERSION ()")# Use the Fetchone method to get a database.Dbversion=Cursor.Fetchone()Print"Database version:%S "%Dbversion# Create a databaseCursor.Execute("CREATE database if not exists dbtest")# Select the database to manipulateDb_conn.select_db(' Dbtest ');# Create a data table SQL statementSql="" "CREATE TABLE if not exists employee (first_name char (a) not NULL, last_name char (+), age INT, Sex CHAR (1), Income FLO at) "" "Try:Cursor.Execute(Sql)ExceptException,E:# Exception is the base class for all exceptions, which means capturing all exceptionsPrint"Error to create TABLE:",E# Insert DataSql="" "INSERT into Employee (first_name, last_name, age, sex, income) VALUES ('%S ', '%S ',%D, '%S ',%d) "" "# Sex:male Male, female girlEmployees=({"First_Name":"Mac","Last_Name":"Mohan","Age":20,"Sex":M,"Income":2000},{"First_Name":"Wei","Last_Name":"Zhu","Age":24,"Sex":M,"Income":7500},{"First_Name":"Huoty","Last_Name":"Kong","Age":24,"Sex":M,"Income":8000},{"First_Name":"Esenich","Last_Name":"Lu","Age":22,"Sex":F,"Income":3500},{"First_Name":"Xmin","Last_Name":"Yun","Age":31,"Sex":F,"Income":9500},{"First_Name":"Yxia","Last_Name":"Fun","Age":23,"Sex":M,"Income":3500})Try:# Clear data in tableCursor.Execute("Delete from employee")# Execute SQL INSERT statementForEmployeeInchEmployees:Cursor.Execute(Sql%(Employee["First_Name"],Employee["Last_Name"],Employee["Age"],Employee["Sex"],Employee["Income"]))# Commit to Database executionDb_conn.Commit()# for a database that supports transactions, in Python database programming,# when the cursor is established, an invisible database transaction is automatically started.# Commit to commit thingsExceptException,E:# Rollback In case there are any errorPrint"Error to insert data:",E#b_conn. Rollback ()Print"Insert RowCount:",Cursor.RowCount# ROWCOUNT is a read-only property and returns the number of rows affected after executing execute (method. )# Database Query Operations:# Fetchone () Get the next row of the result set# Fetchmany ([size=cursor.arraysize]) to get the next few rows of the result set# Fetchall () returns all remaining rows in the result setTry:# Execute SQLCursor.Execute("SELECT * FROM Employee")# get a row of recordsRs=Cursor.Fetchone()PrintRs# get 2 rows of records in the remaining recordsRs=Cursor.Fetchmany(2)PrintRs# get all the remaining recordsArs=Cursor.Fetchall()ForRsInchArs:PrintRs# You can use Fetchall to get all the records and then traverseExceptException,E:Print"Error to select:",E# Database Update operationsSql="UPDATE employee SET Age = age + 1 WHERE sex = '%C ' "%(M)Try:# Execute SQL statementCursor.Execute(Sql)# Commit to Database executionDb_conn.Commit()Cursor.Execute("SELECT * FROM Employee") ARS = cursor.  Fetchall() print "After update:------" for rs in ars: print rsexcept Exception, e: # rollback print "error to update:" When an error occurs, e db.  Rollback()# Close database connection db_conn.  Close()                 

Resources

https://www.python.org/dev/peps/pep-0249/
Https://wiki.python.org/moin/DatabaseProgramming
Https://wiki.python.org/moin/DbApi3

PYTHON-DB Interface Specification