As mentioned, the purpose of the Python Database API is to provide a database-neutral interface from Python to access data stored in database management systems.
The Python Database API Specification consists of the following sections:
Module interface
Connection Objects
Cursor Objects
Type Objects and constructors
Because access to RHDB is made available through Connection Objects, the Python module provides a constructor for the connection; the constructor returns a Connection Object proper. Also, the interface defines a set of global variables that describe the API. See the Section called Code: example.py for the about() method, which displays some of this information for the RHDB Python Database API implementation.
This string specifies the supported Python DB-API level. This implementation supports the Python Database API Specification 2.0 and is set to '2.0'.
This integer specifies the supported level of thread safety. Threads may share the module but not connections. This attribute is set to 1.
This string specifies the type of parameter marker formatting expected by the interface. This attribute is set to 'pyformat', which is Python extended format codes.
Constructor for creating a database connection. An exception will be raised if the connect call is not successful.
dsn = data source name (host:database) userid = user name / identifier (optional) password = password for given user (optional) host = host name (optional) database = database name (optional) |
The Python Database API states as a guideline that the connection parameters should be implemented as keyword parameters and follow the above ordering.
Returns: Connection Object
All error information in the Python Database API is made available through exceptions. The following exceptions can be raised:
| Type | Description |
|---|---|
| Warning | Raised for warnings, not errors. |
| Error | The base class of all other exceptions. This exception can be used to catch all exceptions in one except statement. |
| InterfaceError | Raised for errors related to the database interface, not the database itself. |
| DatabaseError | Raised for errors encountered by the database proper. |
| DataError | Raised for errors related to the processed data (such as divide by zero and out of range). |
| OperationalError | Raised for errors related to the database's operation, which may not be under a programmer's control (such as data source name not found). |
| IntegrityError | Raised for relational integrity errors (such as foreign key check failures). |
| InternalError | Raised when the database encounters an internal error. |
| ProgrammingError | Raised for application programming errors (such as table not found). |
| NotSupportedError | Raised if an API method is used that is not supported by the database. |
Once a connection has been established, you can use the Connection Object to handle transaction control (commits and rollbacks) and Cursor Object instantiation.
Closes the database connection. The connection will be unusable after a call to this method. Any operation attempted with this connection after it is closed will raise an exception.
Commits the transaction. A new transaction will be initiated as part of the commit() call, after the current transaction is committed. An exception will be raised if the commit call is not successful.
Rolls back (aborts) the transaction. A new transaction will be initiated as part of the rollback() call, after the current transaction is rolled back. An exception will be raised if the rollback call is not successful.
Allocates a Cursor Object using the connection. An exception will be raised if the rollback call is not successful.
Returns: Cursor Object
These objects represent database cursors. Cursor Object methods allow the user to query and manipulate database data and also describe the contents of the cursor itself.
This integer specifies the number of rows to fetch at a time when using the fetchmany() method call. This value can be set by using the size parameter of the fetchmany() call.
Describes the columns of the cursor's current result set.
This integer specifies the number of rows that the last execute()/executemany() call produced.
Closes the open cursor. The cursor will be unusable after a call to this method. Any operation attempted with this cursor after it is closed will raise an exception.
Prepares and executes a single database query or command operation. Parameters can be provided and will be bound to variables in the operation.
Prepares and executes an operation multiple times, binding each parameter from the sequence list.
Fetches the next row of the query result set.
Returns: Single row. If no more data is available, the call will return None.
Fetches the remaining rows of the query result set. If it is not possible to return the indicated number of rows, fewer may be returned.
Returns: Sequence/list of rows. An empty sequence is returned if no more data is available.
Fetches the requested number of rows of the query result set.
If the size parameter is not specified, fetchmany() will return the cursor's arraysize attribute number of rows of the query result set.
fetchmany() will set the cursor's arraysize attribute to the value indicated by the size parameter unless keep is set to a value other than 1, which is the default.
Returns: Sequence/list of rows
Type Objects allow database-independent data types to be used when programming with the Python Database API. These types and constructors are mapped in the DB-API implementation to the underlying RHDB data types.
The following mappings are used between the API and Red Hat Database:
| Python API Type Object | RHDB Type |
|---|---|
| STRING | char, bpchar, name, text, varchar |
| INTEGER | int2, int4, serial |
| LONG | int8 |
| FLOAT | float4, float8, numeric |
| BOOL | bool |
| MONEY | money |
| DATETIME | abstime, reltime, tinterval, date, time, timespan, timestamp |
| ROWID | oid, oid8 |
The following mappings use the mxDateTime package from eGenix.com:
| Python API Type Constructors | mxDateTime |
|---|---|
| Date(year,month,day) | DateTime.DateTime(year,month,day) |
| Time(hour,minute,second) | DateTime.TimeDelta(hour,minute,second) |
| Timestamp (year,month,day,hour,minute,second) | DateTime.DateTime (year,month,day,hour,minute,second) |
| DateFromTicks(ticks) | apply (Date,time.localtime(ticks)) |
| TimeFromTicks(ticks) | apply (Time,time.localtime(ticks)) |
| TimestampFromTicks(ticks) | apply (Timestamp,time.localtime(ticks)) |
Given the concepts from the Python Database API Specification, and taking an object-level view, the process of connecting to a database, inserting a row, and then disconnecting is as follows:
Connect to the database by creating a Connection Object
Use the Connection Object to create a Cursor Object
Insert a row using the Cursor Object
Close the Cursor Object
Commit the transaction using the Connection Object
Disconnect from the database using the Connection Object.