Python Database API Version 2.0 Concepts

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

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.

Global Attributes/Constants

apilevel

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'.

threadsafety

This integer specifies the supported level of thread safety. Threads may share the module but not connections. This attribute is set to 1.

paramstyle

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.

Constructors

connect (dsn, userid, password, host, database)

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

Exceptions

All error information in the Python Database API is made available through exceptions. The following exceptions can be raised:

TypeDescription
WarningRaised for warnings, not errors.
ErrorThe base class of all other exceptions. This exception can be used to catch all exceptions in one except statement.
InterfaceErrorRaised for errors related to the database interface, not the database itself.
DatabaseErrorRaised for errors encountered by the database proper.
DataErrorRaised for errors related to the processed data (such as divide by zero and out of range).
OperationalErrorRaised for errors related to the database's operation, which may not be under a programmer's control.
IntegrityErrorRaised for relational integrity errors.
InternalErrorRaised when the database encounters an internal error.
ProgrammingErrorRaised for application programming errors.
NotSupportedErrorRaised if an API method is used that is not supported by the database.

Connection Object

Once a connection has been established, you can use the Connection Object to handle transaction control (commits and rollbacks) and Cursor Object instantiation.

Methods

close ()

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.

commit ()

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.

rollback ()

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.

cursor ()

Allocates a Cursor Object using the connection. An exception will be raised if the rollback call is not successful.

Returns: Cursor Object

Cursor Objects

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.

Attributes

arraysize

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.

description

Describes the columns of the cursor's current result set.

rowcount

This integer specifies the number of rows that the last execute()/executemany() call produced.

Methods

close ()

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.

execute (operation)

Prepares and executes a single database query or command operation.

executemany (operation, parameter_sequence)

Prepares and executes an operation multiple times, binding each parameter from the sequence list.

fetchone ()

Fetches the next row of the query result set.

Returns: Single row. If no more data is available, the call will return None.

fetchall ()

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.

fetchmany ([size][,keep])

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 Object and Constructors

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 ObjectRHDB Type
STRINGchar, bpchar, name, text, varchar
INTEGERint2, int4, serial
LONGint8
FLOATfloat4, float8, numeric
BOOLbool
MONEYmoney
DATETIMEabstime, reltime, tinterval, date, time, timespan, timestamp
ROWIDoid, oid8

The following mappings use the mxDateTime package from eGenix.com:

Python API Type ConstructorsmxDateTime
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))

Conceptual Level Example

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:

  1. Connect to the database by creating a Connection Object

    1. Use the Connection Object to create a Cursor Object

      1. Insert a row using the Cursor Object

      2. Close the Cursor Object

    2. Commit the transaction using the Connection Object

  2. Disconnect from the database using the Connection Object.