Python database API Specification V2.0

1. Module Interface
A. Method:
Connect (parameters ...)
Create a database connection and return the connection object.
Possible parameters include:
DSN Data Source Name
User Username
Password
Host Name
Database Name
B. Constants
Apilevel
String constant used to specify the supported dB API versions. Currently, only "1.0" and "2.0" are allowed. If not specified, the default value is "1.0"

Threadsafety
An integer constant that indicates the supported thread safety (thread safety) levels. Possible values include:
0 threads cannot share modules
1. Threads can share modules in addition to connections
2 threads can share connections and modules
3 threads can share connections, cursors, and modules
Sharing means that two threads can use resources without using mutex locks to lock resources.

Paramstyle
String constant, used to specify the supported SQL parameter formats. Possible values include:
'Qmark' supports '... where name =? '? 'Placeholder format
'Numeric 'supports the location parameter'... where name =: 1'
'Named' supports the naming parameters of '... where name =: name'.
'Format' supports the C printf style of '... where name = % s'.
'Pyformat' supports '... where name = % (name) s' Python extended format

C. Exceptions
Standarderror
|
|-Warning
| When operations such as truncation occur when data is inserted, a warning event is triggered. The warning must be a python standarderror event.
| (Defined in the exceptions module) subclass
|-Error
| For other exceptional superclasses, you can use error to capture all database operation exceptions. Warning is not considered as an exception. error must be
| Subclass of Python standarderror
|
|-Interfaceerror
| Indicates an interface error implemented by the python dB API instead of a database error. It is a subclass of error.
|
|-Databaseerror
| Indicates database-related errors, subclass of error
|
|-Dataerror
| Data processing errors, such as the division of 0 errors and the integer exceeding the precision, and The subclass of databaseerror
|
|-Operationalerror
| Database operation errors, such as data sources not found, transactions cannot be processed, memory allocation errors, etc., subclass of databaseerror
|
|-Integrityerror
| Data integrity inconsistency errors, such as foreign key check errors and other subclasses of. databaseerror
|
|-Internalerror
| Database internal errors, such as invalid cursor (continue calling after the cursor is closed), non-synchronization of transactions, and other child classes of. databaseerror
|
|-Programmingerror
| Programming error. For example, the table does not exist or the SQL syntax is incorrect. It is a subclass of databaseerror.
|
|-Notsupportederror
The database does not support some method errors in the db api. For example, you can call the rollback operation on a database that does not support transactions.
Subclass of databaseerror

2. connection object
A. Method
Close ()
Close the connection. After close () is called, the connection object will no longer be available. After that, the method will be called on the connection object and the cursor object created for the connection.
This will cause an exception. Note: closing the connection will implicitly roll back the transaction if no transaction is committed.

Commit ()
Submit the current transaction. If the database supports the automatic commit feature, this feature is disabled when the connection object is initialized. This method is invalid in databases that do not support transactions.

Rollback ()
Roll back the current transaction. An exception is thrown on the database that does not support the transaction.

Cursor ()
Returns the cursor object created by the connection object.

3. cursor object
The cursor object represents the database cursor and is used to manage the context of database operations. The cursor objects created by the same connection are not isolated, that is,
The data modified by a cursor object can be immediately reflected to another cursor object. The cursor objects created by different connections can also be isolated.
Depends on the degree to which the database supports isolation.
A. Attributes
Description
A sequence composed of seven elements in the format
(Name, type_code, display_size, internal_size, precision, scale, null_ OK ).
The first two elements (name and type_code) are required. The last five elements are optional and must be set to none when meaningful values cannot be provided.
The description attribute may return none if no record is returned or the cursor object does not call the executexxx () method.
Type_code can be interpreted by the Type object described below

Rowcount
Number of records returned by the last query (select) or the number of records affected by the last operation (insert, update)
If executexxx is not executed,-1 is returned for this attribute (the db api may change to none in the future)

Arraysize
This setting specifies the number of rows to be retrieved by the fetchmany method at a time. The default value is 1.

B. Method
Callproc (procname [, parameters])
This method is optional because not all databases support stored procedures.
The parameter procname is the name of the stored procedure, and the parameter parameters sequence must meet the parameters required by the stored procedure. The result of the call is after the input parameters sequence is modified.
The input parameters of the stored procedure will not change, and the output parameters and input/output parameters may be replaced with new values.
A stored procedure can also return a result set, which can be obtained through the fetchxxx method.

Close ()
Close the cursor. After you call close (), the cursor object will no longer be available. Then, an exception will be thrown when you call the method on this cursor object.

Execute (operation [, parameters])
Execute database operations (queries or commands). parameters can be bound to operation in sequence or ing form.
The cursor object can retain the reference of operation, so that when the same operation is executed again, the cursor object can be optimized.
Operation is the most effective optimization algorithm when different parameters are provided.

Executeparameters (operation [, seq_of_parameters])
Use all the elements of seq_of_parameters (sequence or ing) to perform database operations (queries or commands)
Some dB API implementations may call execute multiple times in the method to complete this operation.

Fetchone ()
Obtain the data of the next row in the query result set. A sequence is returned, which contains the values of each column in the data table. If there is no data of the next row, none is returned.
If the previous executexxx method does not return a result set or does not call executexxx at all, this method throws an exception.

Fetchmany ([size = cursor. arraysize])
Returns a subset of the specified length in the query result set. It is usually a sequence containing sequence elements (for example, a list composed of tuple). If no subset exists, an empty sequence is returned.
The length of a subset is specified by the size parameter. If no value is specified, the arraysize attribute of the cursor object is used. If no data row with the specified length exists in the data table,
The maximum possible value is returned.
If the previous executexxx method does not return a result set or does not call executexxx at all, this method throws an exception.

Fetchall ()
Returns the sequence of all the remaining rows in the query result set. It is usually a sequence containing sequence elements (for example, a list composed of tuple ).
Note: The arraysize attribute of the cursor object affects the performance of this method.
If the previous executexxx method does not return a result set or does not call executexxx at all, this method throws an exception.

Nextset ()
Jump the cursor object to the next result set. This method is optional because not all databases support multiple result sets.
If there are no more result sets, this method returns none. Otherwise, true is returned, and data will be obtained after the fetchxxx method is called later.
If the previous executexxx method does not return a result set or does not call executexxx at all, this method throws an exception.

4. Type object and constructor
Many databases need a special format to bind parameters to a specified operation. For example, when inserting data in a column of the net data type, different formats are required.
The problem is: Since the parameters passed to the executexxx method have no type (only string), how does the database know that you want to insert a date format?
To solve this problem, the db api implementation module must provide the following constructor to create objects of the specified type. After these objects are passed to cursor, you can
Accurate Detection type and correct binding.

Constructor

Date (year, month, day) Date
Time (hour, minute, second) Time
Timestamp (year, month, day, hour, minute, second) Date and Time
Datefromticks (ticks) date calculated in seconds since the New Age
Timefromticks (ticks) time calculated in seconds since the New Age
Timestampfromticks (ticks) Date and time calculated in seconds since the New Age
Binary (string) specifies the binary value of a string.

In the sequence returned by the descriptor method of the cursor object, type_code must be of the following type, and the type object can be equal to multiple type_codes.

String describes the character columns of the database, such as char.
Binary describes the binary columns of a database, such as long, raw, and blobs.
Number indicates the number column of the database.
Datetime indicates the date/time column of the database.
Rowid: Specifies the "Row ID" column of the database.

The SQL null value is represented by none in Python.

5. Example of connecting to MySQL database using mysqldb (Python dB API)

# Database Name Misc
# Table script
# Create table user (
# ID int (11) not null auto_increment,
# Name varchar (20) not null default '',
# Email varchar (255) default null,
# Mobile varchar (40) default null,
# Createtime timestamp (14) not null,
# Primary key (ID)
#) Type = InnoDB;

Import mysqldb as MySQL
Import time

Class user (object ):
Attributes = ['id', 'name', 'email ', 'mobile'] # attributes allowed by the user object
Def _ init _ (self, dbrow = ()):
"""
Initialize a user

Dbrow-one row of data in the data table
"""

# Construct a user object from a row of data tables
If dbrow:
For ATTR, value in zip (user. attributes, dbrow ):
Self. _ dict _ [ATTR] = Value

Def _ setitem _ (self, key, value ):
If not key in user. attributes:
Raise attributeerror, "% s not acceptable" % key
Self. _ dict _ [Key] = Value

Def _ getitem _ (self, key ):
If not key in self. _ dict __:
Raise attributeerror, "% s not found" % key
Return self. _ dict _ [Key]

Def _ STR _ (Self ):
Return "% (name) s, % (email) s, % (mobile) s" % self. _ dict __

Class userservice (object ):
Def _ init _ (self, connection ):
Self. Connection = connection

Def createuser (self, user ):
"""
Create a new user

User-user instance
"""
Insertsql = "insert into user (name, email, mobile, createtime) values (% s, % s )"
Cursor = self. Connection. cursor ()
Cursor.exe cute (insertsql, (user. Name, user. Email, user. Mobile, MySQL. timestampfromticks (Time. Time ())))
Self. Connection. Commit ()
Cursor. Close ()

Def findallusers (Self ):
"""
Query all users
"""
Selectsql = "select * from user"
Cursor = self. Connection. cursor ()
Cursor.exe cute (selectsql)
Allusers = [] # The method returns the list. The elements in the list are the objects of each user.
For row in cursor. fetchall ():
Allusers. append (user (ROW ))
Cursor. Close ()
Return allusers

If _ name _ = "_ main __":

Connection = mysql. Connect (DB = "Misc ")
Print connection
Userservice = userservice (connection)
User = user ()
User ['name'] = 'Chinese Username'
User ['email '] = 'snowway. xue@gmail.com'
User ['mobile'] = '000000'
Userservice. createuser (User)

For user in userservice. findallusers ():
Print user
Connection. Close ()