python-peps/peps/pep-0248.rst

PEP: 248
Title: Python Database API Specification v1.0
Author: Greg Stein <gstein@lyra.org>, Marc-André Lemburg <mal@lemburg.com>
Discussions-To: db-sig@python.org
Status: Final
Type: Informational
Content-Type: text/x-rst
Created: 08-May-1996
Post-History:
Superseded-By: 249


Introduction
============

This API has been defined to encourage similarity between the
Python modules that are used to access databases.  By doing this,
we hope to achieve a consistency leading to more easily understood
modules, 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 to
the 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 with
something terminated by ``db``.  Existing examples are: ``oracledb``,
``informixdb``, and ``pg95db``.  These modules should export several
names:

``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__`` is
   called).  The connection will be unusable from this point
   forward; an exception will be raised if any operation is
   attempted with the connection.

``commit()``
   Commit any pending transaction to the database.

``rollback()``
   Roll the database back to the start of any pending
   transaction.

``cursor()``
   Return a new Cursor Object.  An exception may be thrown if
   the database does not support a cursor concept.

``callproc([params])``
   (Note: this method is not well-defined yet.)  Call a
   stored 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 simple
   applications that do not require the complexity of a
   cursor, a Connection Object should respond to each of the
   attributes and methods of the Cursor Object.  Databases
   that have cursor can implement this by using an implicit,
   internal cursor.


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:

``arraysize``
   This read/write attribute specifies the number of rows to
   fetch at a time with ``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.

``description``
   This read-only attribute is a tuple of 7-tuples.  Each
   7-tuple contains information describing each result
   column: (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.

``close()``
   Close the cursor now (rather than whenever ``__del__`` is
   called).  The cursor will be unusable from this point
   forward; an exception will be raised if any operation is
   attempted 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 the
   operation.  Variables are specified in a database-specific
   notation 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 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:

   - If the statement is DDL (e.g. ``CREATE TABLE``), then 1 is
     returned.

   - If the statement is DML (e.g. ``UPDATE`` or ``INSERT``), then the
     number of rows affected is returned (0 or a positive
     integer).

   - If the statement is DQL (e.g. ``SELECT``), ``None`` is returned,
     indicating that the statement is not really complete until
     you use one of the  'fetch' methods.

``fetchone()``
   Fetch the next row of a query result, returning a single
   tuple.

``fetchmany([size])``
   Fetch the next set of rows of a query result, returning as
   a list of tuples. An empty list is returned when no more
   rows are available. The number of rows to fetch is
   specified by the parameter.  If it is ``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.

``fetchall()``
   Fetch all rows of a query result, returning as a list of
   tuples.  Note that the cursor's arraysize attribute can
   affect the performance of this operation.

``setinputsizes(sizes)``
   (Note: this method is not well-defined yet.)  This can be
   used before a call to ``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.

``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 the
   result tuple.  Using a column of ``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.


DBI Helper Objects
==================

Many databases need to have the input in a particular format for
binding to an operation's input parameters.  For example, if an
input is destined for a ``DATE`` column, then it must be bound to the
database in a particular string format.  Similar problems exist
for "Row ID" columns or large binary items (e.g. blobs or ``RAW``
columns).  This presents problems for Python since the parameters
to the ``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(value)``
   This function constructs a '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(value)``
   This function constructs a 'dbiRaw' instance that holds a
   raw (binary) value.  The value should be specified as a
   Python string.

``STRING``
   This object is used to describe columns in a database that
   are string-based (e.g. CHAR).

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

``NUMBER``
   This object is used to describe numeric columns in a
   database.

``DATE``
   This object is used to describe date columns in a
   database.

``ROWID``
   This object is used to describe the "Row ID" column in a
   database.


Acknowledgements
================

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

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

Copyright
=========

This document has been placed in the Public Domain.