Introduction

This API has been defined to encourage similarity between thePython modules that are used to access databases. By doing this,we hope to achieve a consistency leading to more easily understoodmodules, code that is generally more portable across databases,and a broader reach of database connectivity from Python.

This interface specification consists of several items:

• Module Interface
• Connection Objects
• Cursor Objects
• DBI Helper Objects

Comments and questions about this specification may be directed tothe SIG on Tabular Databases in Python(http://www.python.org/sigs/db-sig).

This specification document was last updated on: April 9, 1996.It will be known as Version 1.0 of this specification.

Module Interface

The database interface modules should typically be named withsomething terminated bydb. Existing examples are:oracledb,informixdb, andpg95db. These modules should export severalnames:

modulename(connection_string)

Constructor for creating a connection to the database.Returns a Connection Object.

error

Exception raised for errors from the database module.

Connection Objects

Connection Objects should respond to the following methods:

close()

Close the connection now (rather than whenever__del__ iscalled). The connection will be unusable from this pointforward; an exception will be raised if any operation isattempted with the connection.

commit()

Commit any pending transaction to the database.

rollback()

Roll the database back to the start of any pendingtransaction.

cursor()

Return a new Cursor Object. An exception may be thrown ifthe database does not support a cursor concept.

callproc([params])

(Note: this method is not well-defined yet.) Call astored database procedure with the given (optional)parameters. Returns the result of the stored procedure.

(all Cursor Object attributes and methods)

For databases that do not have cursors and for simpleapplications that do not require the complexity of acursor, a Connection Object should respond to each of theattributes and methods of the Cursor Object. Databasesthat have cursor can implement this by using an implicit,internal cursor.

Cursor Objects

These objects represent a database cursor, which is used to managethe context of a fetch operation.

Cursor Objects should respond to the following methods andattributes:

arraysize

This read/write attribute specifies the number of rows tofetch at a time withfetchmany(). This value is also usedwhen inserting multiple rows at a time (passing atuple/list of tuples/lists as the params value toexecute()). This attribute will default to a single row.

Note that the arraysize is optional and is merely providedfor higher performance database interactions.Implementations should observe it with respect to thefetchmany() method, but are free to interact with thedatabase a single row at a time.

description

This read-only attribute is a tuple of 7-tuples. Each7-tuple contains information describing each resultcolumn: (name, type_code, display_size, internal_size,precision, scale, null_ok). This attribute will beNonefor operations that do not return rows or if the cursorhas not had an operation invoked via theexecute() methodyet.

The ‘type_code’ is one of the ‘dbi’ values specified inthe section below.

Note: this is a bit in flux. Generally, the first twoitems of the 7-tuple will always be present; the othersmay be database specific.

close()

Close the cursor now (rather than whenever__del__ iscalled). The cursor will be unusable from this pointforward; an exception will be raised if any operation isattempted with the cursor.

execute(operation[,params])

Execute (prepare) a database operation (query or command).Parameters may be provided (as a sequence(e.g. tuple/list)) and will be bound to variables in theoperation. Variables are specified in a database-specificnotation that is based on the index in the parameter tuple(position-based rather than name-based).

The parameters may also be specified as a sequence ofsequences (e.g. a list of tuples) to insert multiple rowsin a single operation.

A reference to the operation will be retained by thecursor. If the same operation object is passed in again,then the cursor can optimize its behavior. This is mosteffective for algorithms where the same operation is used,but different parameters are bound to it (many times).

For maximum efficiency when reusing an operation, it isbest to use thesetinputsizes() method to specify theparameter types and sizes ahead of time. It is legal fora parameter to not match the predefined information; theimplementation should compensate, possibly with a loss ofefficiency.

Using SQL terminology, these are the possible resultvalues from theexecute() method:

• If the statement is DDL (e.g.CREATETABLE), then 1 isreturned.
• If the statement is DML (e.g.UPDATE orINSERT), then thenumber of rows affected is returned (0 or a positiveinteger).
• If the statement is DQL (e.g.SELECT),None is returned,indicating that the statement is not really complete untilyou use one of the ‘fetch’ methods.

fetchone()

Fetch the next row of a query result, returning a singletuple.

fetchmany([size])

Fetch the next set of rows of a query result, returning asa list of tuples. An empty list is returned when no morerows are available. The number of rows to fetch isspecified by the parameter. If it isNone, then thecursor’s arraysize determines the number of rows to befetched.

Note there are performance considerations involved withthe size parameter. For optimal performance, it isusually best to use the arraysize attribute. If the sizeparameter is used, then it is best for it to retain thesame value from onefetchmany() call to the next.

fetchall()

Fetch all rows of a query result, returning as a list oftuples. Note that the cursor’s arraysize attribute canaffect the performance of this operation.

setinputsizes(sizes)

(Note: this method is not well-defined yet.) This can beused before a call toexecute() to predefine memoryareas for the operation’s parameters. sizes is specifiedas a tuple – one item for each input parameter. The itemshould be a Type object that corresponds to the input thatwill be used, or it should be an integer specifying themaximum length of a string parameter. If the item isNone, then no predefined memory area will be reservedfor that column (this is useful to avoid predefined areasfor large inputs).

This method would be used before theexecute() method isinvoked.

Note that this method is optional and is merely providedfor higher performance database interaction.Implementations are free to do nothing and users are freeto not use it.

setoutputsize(size[,col])

(Note: this method is not well-defined yet.)

Set a column buffer size for fetches of large columns(e.g. LONG). The column is specified as an index into theresult tuple. Using a column ofNone will set the defaultsize for all large columns in the cursor.

This method would be used before theexecute() method isinvoked.

Note that this method is optional and is merely providedfor higher performance database interaction.Implementations are free to do nothing and users are freeto not use it.

DBI Helper Objects

Many databases need to have the input in a particular format forbinding to an operation’s input parameters. For example, if aninput is destined for aDATE column, then it must be bound to thedatabase in a particular string format. Similar problems existfor “Row ID” columns or large binary items (e.g. blobs orRAWcolumns). This presents problems for Python since the parametersto theexecute() method are untyped. When the database modulesees a Python string object, it doesn’t know if it should be boundas a simple CHAR column, as a raw binary item, or as aDATE.

To overcome this problem, the ‘dbi’ module was created. Thismodule specifies some basic database interface types for workingwith databases. There are two classes: ‘dbiDate’ and ‘dbiRaw’.These are simple container classes that wrap up a value. Whenpassed to the database modules, the module can then detect thatthe input parameter is intended as aDATE or aRAW. For symmetry,the database modules will returnDATE andRAW columns as instancesof these classes.

A Cursor Object’s ‘description’ attribute returns informationabout each of the result columns of a query. The ‘type_code’ isdefined to be one of five types exported by this module:STRING,RAW,NUMBER,DATE, orROWID.

The module exports the following names:

dbiDate(value)

This function constructs a ‘dbiDate’ instance that holds adate value. The value should be specified as an integernumber of seconds since the “epoch” (e.g.time.time()).

dbiRaw(value)

This function constructs a ‘dbiRaw’ instance that holds araw (binary) value. The value should be specified as aPython string.

STRING

This object is used to describe columns in a database thatare string-based (e.g. CHAR).

RAW

This object is used to describe (large) binary columns ina database (e.g. LONG RAW, blobs).

NUMBER

This object is used to describe numeric columns in adatabase.

DATE

This object is used to describe date columns in adatabase.

ROWID

This object is used to describe the “Row ID” column in adatabase.

Acknowledgements

Many thanks go to Andrew Kuchling who converted the PythonDatabase API Specification 1.0 from the original HTML format intothe PEP format in 2001.

Greg Stein is the original author of the Python Database APISpecification 1.0. Marc-André later continued maintenance of the API asan editor.

Copyright

This document has been placed in the Public Domain.