python--Database Support

One, the Python database Programming Interface (API):

1. Global variables: Any database module that supports the 2.0 version of the DB API must define 3 global variables that describe the properties of the module, because the API design is flexible enough to support different underlying mechanisms.

1. APILevel: string constant that provides the version number of the API being used.

2. Threadsafety: The thread safety level of the module is an integer with a value range of 0-3. 0 means that the thread does not share the module at all. 3 indicates that the thread fully shares the module. 1 means that the thread itself shares the module, but does not share the connection.

3. Paramstyle: The parameter style used in SQL queries (how parameters are stitched into SQL queries when performing several similar queries).

' Format ' represents a standard string format that can be inserted in the parameter where the%s is to be spliced.

' Pyformat ' represents an extended format code for use in dictionary stitching, such as% (foo).

' Qmark ' uses a question mark.

' Numeric ' uses: 1 or: 2 format field (number indicates the ordinal of the parameter).

' Named ' represents a field such as Foobar, where Foobar is the parameter name.

2. Exceptions: The API defines a hierarchy of exceptions so that error handling is possible.

1, StandardError: Generic base class for all exceptions.

2, Waring: It's Super class StandardError, in the non-fatal error when thrown.

3, Error: Its superclass standarderror, all the error conditions of the generic super-class.

4, Interfaceerror: It's super-class error, about the interface and not the database errors.

5, Databaseerror: Its superclass error, the base class for database-related errors.

6, DataError: Its super-class databaseerror, data-related problems, such as the value out of range.

7, Operationalerror: It's Super class Databaseerror, database internal operation error.

8, Integityerror: Its Super class databaseerror, the relationship integrity is affected, such as the key check failed.

9, Internalerror: It's Super class Databaseerror, database internal error, such as illegal cursor.

10, Programingerror: Its super-class Databaseerror, user programming errors, such as the table is not found.

11, Notsupportederror: Its superclass databaseerror, request unsupported features (such as rollback)

3. Connections and cursors:

1. Connect function:

Parameters commonly used by the Connect function:

1. DSN: Data source name, given this parameter indicates database dependency.

2, User: username.

3, Password: User password.

4, Host: hostname.

5: Database name.

The Connect function returns a Connection object that represents the current and database session. Supported Methods for connection objects:

1, Close (): Closes the connection, and the connection object and its cursors are not available after closing.

2. Commit (): Commit the pending transaction if supported, otherwise do nothing.

3. Rollback (): Rolls back the pending transaction. (may not be available).

4. Cursor (): Returns the connected Cursor object.

2. Cursor object: Executes the SQL query through the cursor and checks the results.

Methods for cursor objects:

1. Callproc (name,[, params]): Invokes a named database procedure with the given name and parameters.

2, Close (): Close the cursor.

3. Execute (oper,[, params]): Executes an SQL operation, possibly with parameters.

4, Executemany (OPER,PSEQ): Performs SQL operations on each parameter set in the sequence.

5. Fetclone (): Saves the next row in the result set of the query as a sequence, or none.

6, Fetchmany ([size]): Gets multiple rows in the query result set, the default size is arraysize.

7, Fetchall (): The sequence of all remaining rows as sequences.

8, Nextset (): Jump to the next possible result set (optional).

9, Setinputsizes (sizes): Pre-defined memory area for parameters.

10. Setoutputsize (size[, col]): Sets the buffer size for the obtained large data value.

2, the characteristics of the cursor object:

1, Description: The result column describes the sequence, read-only.

2, RowCount: The number of rows in the result, read-only.

3, the function returned in Arraysize:fetchmany, the default 1.

4. Type: The DB API defines constructors and constants for special types and values (Singleton mode).

1. Date (Year,month,day): Creates an object that holds a date value.

2, Time (Hour,minute,second): Creates an object that holds the value of a period.

3, Timestamp (y,mon,d,h,min,s): Creates an object that holds the timestamp value.

4. Datefromticks (Ticks): Create an object that holds the number of seconds since the new era.

5, Timefromticks (ticks): Creates a time value object that holds the number of seconds.

6, Timestampfromticks (ticks): Creates an object that holds a timestamp value from the number of seconds.

7, Binary (string): Creates an object that holds the binary string value.

8. SIRING: Describes string-based column types (such as char).

9, binary: Describes the binary columns (such as long or raw).

10. Number: Describes the column of numbers.

11. DATETIME: Describes the date \ Time column.

12. ROWID: Describes the row ID column.

5. Database application Example:

1. Create a People table that contains the Id,name,age field.

2. Insert 1 rows of data into the people table, id=1,name=lisa,age=25

3. Enquiry

Use Fetchone (): Sets the next line of the query's result set as a sequence or none:

Use Fetchall (): Sets all rows of the query result set as sequences of sequences.

Iterative Object Traversal:

4. Delete data

5. Insert more than one data at a time:

1. Parameter binding:

2. Using Executemany ():

3, the use of generators to achieve:

5. Update:

6. Delete:

7. Select:

8, the use of Executescript:

python--Database Support