This interface specification consists of several items:
Comments and questions about this specification may be directed to the SIG on Tabular Databases in Python.
See the main index for information about modules that use this interface.
This specification document was last updated on: April 9, 1996. It will be known as Version 1.0 of this specification.
Note: The ODBC module implements a newer version of this specification. Some additional error types were added to DBI. A spec update is due.
oracledb
, informixdb
, and pg95db
.
These modules should export several names:
Connection Objects
Connections Objects should respond to the following methods:
Cursor Objects
These objects represent a database cursor, which is used to manage the context
of a fetch operation.
Cursor Objects should respond to the following methods and attributes:
fetchmany()
. This value
is also used
when inserting multiple rows at a time (passing a tuple/list of tuples/lists
as the params value to execute()
). This attribute will
default to a single row.
Note that the arraysize is optional and is merely provided for higher
performance database interactions. Implementations should observe it
with respect to the fetchmany()
method, but are free to
interact with
the database a single row at a time.
(name, type_code, display_size, internal_size,
precision, scale, null_ok)
. This attribute will be
None
for operations that do not
return rows or if the cursor has not had an operation invoked via the
execute()
method yet.
The type_code
is one of the dbi values
specified in the section below.
Note: this is a bit in flux. Generally, the first two items of the 7-tuple will always be present; the others may be database specific.
The parameters may also be specified as a sequence of sequences (e.g. a list of tuples) to insert multiple rows in a single operation.
A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor can optimize its behavior. This is most effective 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 is best to use the setinputsizes() method to specify the parameter types and sizes ahead of time. It is legal for a parameter to not match the predefined information; the implementation should compensate, possibly with a loss of efficiency.
Using SQL terminology, these are the possible result values from the execute() method:
None
,
then the cursor's arraysize determines the number of rows to be fetched.
Note there are performance considerations involved with the size parameter.
For optimal performance, it is usually best to use the arraysize attribute.
If the size parameter is used, then it is best for it to retain the same
value from one fetchmany()
call to the next.
execute()
to predefine
memory areas for
the operation's parameters. sizes is specified as a tuple -- one item
for each input parameter. The item should be a Type object that
corresponds to the input that will be used, or it should be an integer
specifying the maximum length of a string parameter. If the item is
None
, then no predefined memory area will be reserved for
that column (this is useful to avoid predefined areas for large inputs).
This method would be used before the execute()
method
is invoked.
Note that this method is optional and is merely provided for higher performance database interaction. Implementations are free to do nothing and users are free to not use it.
None
will set the default size for all large columns in
the cursor.
This method would be used before the execute()
method
is invoked.
Note that this method is optional and is merely provided for higher performance database interaction. Implementations are free to do nothing and users are free to not use it.
execute()
method are untyped. When the database module
sees a Python string object, it doesn't know if it should be bound
as a simple CHAR column, as a raw binary item, or as a DATE.
To overcome this problem, the dbi module was created. This module specifies some basic database interface types for working with databases. There are two classes: dbiDate and dbiRaw. These are simple container classes that wrap up a value. When passed to the database modules, the module can then detect that the input parameter is intended as a DATE or a RAW. For symmetry, the database modules will return DATE and RAW columns as instances of these classes.
A Cursor Object's description
attribute returns information
about each of the result columns of a query. The type_code
is defined to be one of five types exported by this module:
STRING, RAW, NUMBER,
DATE, or ROWID.
The module exports the following names:
dbiDate
instance that holds
a date value. The value should be specified as an integer number of
seconds since the "epoch" (e.g. time.time()).
dbiRaw
instance that holds
a raw (binary) value. The value should be specified as a Python string.