python-peps/pep-0248/index.html

361 lines
24 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="color-scheme" content="light dark">
<title>PEP 248 Python Database API Specification v1.0 | peps.python.org</title>
<link rel="shortcut icon" href="../_static/py.png">
<link rel="canonical" href="https://peps.python.org/pep-0248/">
<link rel="stylesheet" href="../_static/style.css" type="text/css">
<link rel="stylesheet" href="../_static/mq.css" type="text/css">
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" media="(prefers-color-scheme: light)" id="pyg-light">
<link rel="stylesheet" href="../_static/pygments_dark.css" type="text/css" media="(prefers-color-scheme: dark)" id="pyg-dark">
<link rel="alternate" type="application/rss+xml" title="Latest PEPs" href="https://peps.python.org/peps.rss">
<meta property="og:title" content='PEP 248 Python Database API Specification v1.0 | peps.python.org'>
<meta property="og:description" content="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 datab...">
<meta property="og:type" content="website">
<meta property="og:url" content="https://peps.python.org/pep-0248/">
<meta property="og:site_name" content="Python Enhancement Proposals (PEPs)">
<meta property="og:image" content="https://peps.python.org/_static/og-image.png">
<meta property="og:image:alt" content="Python PEPs">
<meta property="og:image:width" content="200">
<meta property="og:image:height" content="200">
<meta name="description" content="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 datab...">
<meta name="theme-color" content="#3776ab">
</head>
<body>
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
<symbol id="svg-sun-half" viewBox="0 0 24 24" pointer-events="all">
<title>Following system colour scheme</title>
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="none"
stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">
<circle cx="12" cy="12" r="9"></circle>
<path d="M12 3v18m0-12l4.65-4.65M12 14.3l7.37-7.37M12 19.6l8.85-8.85"></path>
</svg>
</symbol>
<symbol id="svg-moon" viewBox="0 0 24 24" pointer-events="all">
<title>Selected dark colour scheme</title>
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="none"
stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">
<path stroke="none" d="M0 0h24v24H0z" fill="none"></path>
<path d="M12 3c.132 0 .263 0 .393 0a7.5 7.5 0 0 0 7.92 12.446a9 9 0 1 1 -8.313 -12.454z"></path>
</svg>
</symbol>
<symbol id="svg-sun" viewBox="0 0 24 24" pointer-events="all">
<title>Selected light colour scheme</title>
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="none"
stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">
<circle cx="12" cy="12" r="5"></circle>
<line x1="12" y1="1" x2="12" y2="3"></line>
<line x1="12" y1="21" x2="12" y2="23"></line>
<line x1="4.22" y1="4.22" x2="5.64" y2="5.64"></line>
<line x1="18.36" y1="18.36" x2="19.78" y2="19.78"></line>
<line x1="1" y1="12" x2="3" y2="12"></line>
<line x1="21" y1="12" x2="23" y2="12"></line>
<line x1="4.22" y1="19.78" x2="5.64" y2="18.36"></line>
<line x1="18.36" y1="5.64" x2="19.78" y2="4.22"></line>
</svg>
</symbol>
</svg>
<script>
document.documentElement.dataset.colour_scheme = localStorage.getItem("colour_scheme") || "auto"
</script>
<section id="pep-page-section">
<header>
<h1>Python Enhancement Proposals</h1>
<ul class="breadcrumbs">
<li><a href="https://www.python.org/" title="The Python Programming Language">Python</a> &raquo; </li>
<li><a href="../pep-0000/">PEP Index</a> &raquo; </li>
<li>PEP 248</li>
</ul>
<button id="colour-scheme-cycler" onClick="setColourScheme(nextColourScheme())">
<svg aria-hidden="true" class="colour-scheme-icon-when-auto"><use href="#svg-sun-half"></use></svg>
<svg aria-hidden="true" class="colour-scheme-icon-when-dark"><use href="#svg-moon"></use></svg>
<svg aria-hidden="true" class="colour-scheme-icon-when-light"><use href="#svg-sun"></use></svg>
<span class="visually-hidden">Toggle light / dark / auto colour theme</span>
</button>
</header>
<article>
<section id="pep-content">
<h1 class="page-title">PEP 248 Python Database API Specification v1.0</h1>
<dl class="rfc2822 field-list simple">
<dt class="field-odd">Author<span class="colon">:</span></dt>
<dd class="field-odd">Greg Stein &lt;gstein&#32;&#97;t&#32;lyra.org&gt;, Marc-André Lemburg &lt;mal&#32;&#97;t&#32;lemburg.com&gt;</dd>
<dt class="field-even">Discussions-To<span class="colon">:</span></dt>
<dd class="field-even"><a class="reference external" href="https://mail.python.org/mailman/listinfo/db-sig">Db-SIG list</a></dd>
<dt class="field-odd">Status<span class="colon">:</span></dt>
<dd class="field-odd"><abbr title="Accepted and implementation complete, or no longer active">Final</abbr></dd>
<dt class="field-even">Type<span class="colon">:</span></dt>
<dd class="field-even"><abbr title="Non-normative PEP containing background, guidelines or other information relevant to the Python ecosystem">Informational</abbr></dd>
<dt class="field-odd">Created<span class="colon">:</span></dt>
<dd class="field-odd">08-May-1996</dd>
<dt class="field-even">Post-History<span class="colon">:</span></dt>
<dd class="field-even"><p></p></dd>
<dt class="field-odd">Superseded-By<span class="colon">:</span></dt>
<dd class="field-odd"><a class="reference external" href="../pep-0249/">249</a></dd>
</dl>
<hr class="docutils" />
<section id="contents">
<details><summary>Table of Contents</summary><ul class="simple">
<li><a class="reference internal" href="#introduction">Introduction</a></li>
<li><a class="reference internal" href="#module-interface">Module Interface</a></li>
<li><a class="reference internal" href="#connection-objects">Connection Objects</a></li>
<li><a class="reference internal" href="#cursor-objects">Cursor Objects</a></li>
<li><a class="reference internal" href="#dbi-helper-objects">DBI Helper Objects</a></li>
<li><a class="reference internal" href="#acknowledgements">Acknowledgements</a></li>
<li><a class="reference internal" href="#copyright">Copyright</a></li>
</ul>
</details></section>
<section id="introduction">
<h2><a class="toc-backref" href="#introduction" role="doc-backlink">Introduction</a></h2>
<p>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.</p>
<p>This interface specification consists of several items:</p>
<ul class="simple">
<li>Module Interface</li>
<li>Connection Objects</li>
<li>Cursor Objects</li>
<li>DBI Helper Objects</li>
</ul>
<p>Comments and questions about this specification may be directed to
the SIG on Tabular Databases in Python
(<a class="reference external" href="http://www.python.org/sigs/db-sig">http://www.python.org/sigs/db-sig</a>).</p>
<p>This specification document was last updated on: April 9, 1996.
It will be known as Version 1.0 of this specification.</p>
</section>
<section id="module-interface">
<h2><a class="toc-backref" href="#module-interface" role="doc-backlink">Module Interface</a></h2>
<p>The database interface modules should typically be named with
something terminated by <code class="docutils literal notranslate"><span class="pre">db</span></code>. Existing examples are: <code class="docutils literal notranslate"><span class="pre">oracledb</span></code>,
<code class="docutils literal notranslate"><span class="pre">informixdb</span></code>, and <code class="docutils literal notranslate"><span class="pre">pg95db</span></code>. These modules should export several
names:</p>
<dl class="simple">
<dt><code class="docutils literal notranslate"><span class="pre">modulename(connection_string)</span></code></dt><dd>Constructor for creating a connection to the database.
Returns a Connection Object.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">error</span></code></dt><dd>Exception raised for errors from the database module.</dd>
</dl>
</section>
<section id="connection-objects">
<h2><a class="toc-backref" href="#connection-objects" role="doc-backlink">Connection Objects</a></h2>
<p>Connection Objects should respond to the following methods:</p>
<dl class="simple">
<dt><code class="docutils literal notranslate"><span class="pre">close()</span></code></dt><dd>Close the connection now (rather than whenever <code class="docutils literal notranslate"><span class="pre">__del__</span></code> is
called). The connection will be unusable from this point
forward; an exception will be raised if any operation is
attempted with the connection.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">commit()</span></code></dt><dd>Commit any pending transaction to the database.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">rollback()</span></code></dt><dd>Roll the database back to the start of any pending
transaction.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">cursor()</span></code></dt><dd>Return a new Cursor Object. An exception may be thrown if
the database does not support a cursor concept.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">callproc([params])</span></code></dt><dd>(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.</dd>
<dt>(all Cursor Object attributes and methods)</dt><dd>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.</dd>
</dl>
</section>
<section id="cursor-objects">
<h2><a class="toc-backref" href="#cursor-objects" role="doc-backlink">Cursor Objects</a></h2>
<p>These objects represent a database cursor, which is used to manage
the context of a fetch operation.</p>
<p>Cursor Objects should respond to the following methods and
attributes:</p>
<dl>
<dt><code class="docutils literal notranslate"><span class="pre">arraysize</span></code></dt><dd>This read/write attribute specifies the number of rows to
fetch at a time with <code class="docutils literal notranslate"><span class="pre">fetchmany()</span></code>. This value is also used
when inserting multiple rows at a time (passing a
tuple/list of tuples/lists as the params value to
<code class="docutils literal notranslate"><span class="pre">execute()</span></code>). This attribute will default to a single row.<p>Note that the arraysize is optional and is merely provided
for higher performance database interactions.
Implementations should observe it with respect to the
<code class="docutils literal notranslate"><span class="pre">fetchmany()</span></code> method, but are free to interact with the
database a single row at a time.</p>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">description</span></code></dt><dd>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 <code class="docutils literal notranslate"><span class="pre">None</span></code>
for operations that do not return rows or if the cursor
has not had an operation invoked via the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method
yet.<p>The type_code is one of the dbi values specified in
the section below.</p>
<p>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.</p>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">close()</span></code></dt><dd>Close the cursor now (rather than whenever <code class="docutils literal notranslate"><span class="pre">__del__</span></code> is
called). The cursor will be unusable from this point
forward; an exception will be raised if any operation is
attempted with the cursor.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">execute(operation</span> <span class="pre">[,params])</span></code></dt><dd>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).<p>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.</p>
<p>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).</p>
<p>For maximum efficiency when reusing an operation, it is
best to use the <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> 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.</p>
<p>Using SQL terminology, these are the possible result
values from the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method:</p>
<ul class="simple">
<li>If the statement is DDL (e.g. <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code>), then 1 is
returned.</li>
<li>If the statement is DML (e.g. <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> or <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>), then the
number of rows affected is returned (0 or a positive
integer).</li>
<li>If the statement is DQL (e.g. <code class="docutils literal notranslate"><span class="pre">SELECT</span></code>), <code class="docutils literal notranslate"><span class="pre">None</span></code> is returned,
indicating that the statement is not really complete until
you use one of the fetch methods.</li>
</ul>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">fetchone()</span></code></dt><dd>Fetch the next row of a query result, returning a single
tuple.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">fetchmany([size])</span></code></dt><dd>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 <code class="docutils literal notranslate"><span class="pre">None</span></code>, then the
cursors arraysize determines the number of rows to be
fetched.<p>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 <code class="docutils literal notranslate"><span class="pre">fetchmany()</span></code> call to the next.</p>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">fetchall()</span></code></dt><dd>Fetch all rows of a query result, returning as a list of
tuples. Note that the cursors arraysize attribute can
affect the performance of this operation.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">setinputsizes(sizes)</span></code></dt><dd>(Note: this method is not well-defined yet.) This can be
used before a call to <code class="docutils literal notranslate"><span class="pre">execute()</span></code> to predefine memory
areas for the operations 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
<code class="docutils literal notranslate"><span class="pre">None</span></code>, then no predefined memory area will be reserved
for that column (this is useful to avoid predefined areas
for large inputs).<p>This method would be used before the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method is
invoked.</p>
<p>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.</p>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">setoutputsize(size</span> <span class="pre">[,col])</span></code></dt><dd>(Note: this method is not well-defined yet.)<p>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 <code class="docutils literal notranslate"><span class="pre">None</span></code> will set the default
size for all large columns in the cursor.</p>
<p>This method would be used before the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method is
invoked.</p>
<p>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.</p>
</dd>
</dl>
</section>
<section id="dbi-helper-objects">
<h2><a class="toc-backref" href="#dbi-helper-objects" role="doc-backlink">DBI Helper Objects</a></h2>
<p>Many databases need to have the input in a particular format for
binding to an operations input parameters. For example, if an
input is destined for a <code class="docutils literal notranslate"><span class="pre">DATE</span></code> 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 <code class="docutils literal notranslate"><span class="pre">RAW</span></code>
columns). This presents problems for Python since the parameters
to the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method are untyped. When the database module
sees a Python string object, it doesnt know if it should be bound
as a simple CHAR column, as a raw binary item, or as a <code class="docutils literal notranslate"><span class="pre">DATE</span></code>.</p>
<p>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 <code class="docutils literal notranslate"><span class="pre">DATE</span></code> or a <code class="docutils literal notranslate"><span class="pre">RAW</span></code>. For symmetry,
the database modules will return <code class="docutils literal notranslate"><span class="pre">DATE</span></code> and <code class="docutils literal notranslate"><span class="pre">RAW</span></code> columns as instances
of these classes.</p>
<p>A Cursor Objects 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: <code class="docutils literal notranslate"><span class="pre">STRING</span></code>,
<code class="docutils literal notranslate"><span class="pre">RAW</span></code>, <code class="docutils literal notranslate"><span class="pre">NUMBER</span></code>, <code class="docutils literal notranslate"><span class="pre">DATE</span></code>, or <code class="docutils literal notranslate"><span class="pre">ROWID</span></code>.</p>
<p>The module exports the following names:</p>
<dl class="simple">
<dt><code class="docutils literal notranslate"><span class="pre">dbiDate(value)</span></code></dt><dd>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. <code class="docutils literal notranslate"><span class="pre">time.time()</span></code>).</dd>
<dt><code class="docutils literal notranslate"><span class="pre">dbiRaw(value)</span></code></dt><dd>This function constructs a dbiRaw instance that holds a
raw (binary) value. The value should be specified as a
Python string.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">STRING</span></code></dt><dd>This object is used to describe columns in a database that
are string-based (e.g. CHAR).</dd>
<dt><code class="docutils literal notranslate"><span class="pre">RAW</span></code></dt><dd>This object is used to describe (large) binary columns in
a database (e.g. LONG RAW, blobs).</dd>
<dt><code class="docutils literal notranslate"><span class="pre">NUMBER</span></code></dt><dd>This object is used to describe numeric columns in a
database.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">DATE</span></code></dt><dd>This object is used to describe date columns in a
database.</dd>
<dt><code class="docutils literal notranslate"><span class="pre">ROWID</span></code></dt><dd>This object is used to describe the “Row ID” column in a
database.</dd>
</dl>
</section>
<section id="acknowledgements">
<h2><a class="toc-backref" href="#acknowledgements" role="doc-backlink">Acknowledgements</a></h2>
<p>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.</p>
<p>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.</p>
</section>
<section id="copyright">
<h2><a class="toc-backref" href="#copyright" role="doc-backlink">Copyright</a></h2>
<p>This document has been placed in the Public Domain.</p>
</section>
</section>
<hr class="docutils" />
<p>Source: <a class="reference external" href="https://github.com/python/peps/blob/main/peps/pep-0248.rst">https://github.com/python/peps/blob/main/peps/pep-0248.rst</a></p>
<p>Last modified: <a class="reference external" href="https://github.com/python/peps/commits/main/peps/pep-0248.rst">2023-09-09 17:39:29 GMT</a></p>
</article>
<nav id="pep-sidebar">
<h2>Contents</h2>
<ul>
<li><a class="reference internal" href="#introduction">Introduction</a></li>
<li><a class="reference internal" href="#module-interface">Module Interface</a></li>
<li><a class="reference internal" href="#connection-objects">Connection Objects</a></li>
<li><a class="reference internal" href="#cursor-objects">Cursor Objects</a></li>
<li><a class="reference internal" href="#dbi-helper-objects">DBI Helper Objects</a></li>
<li><a class="reference internal" href="#acknowledgements">Acknowledgements</a></li>
<li><a class="reference internal" href="#copyright">Copyright</a></li>
</ul>
<br>
<a id="source" href="https://github.com/python/peps/blob/main/peps/pep-0248.rst">Page Source (GitHub)</a>
</nav>
</section>
<script src="../_static/colour_scheme.js"></script>
<script src="../_static/wrap_tables.js"></script>
<script src="../_static/sticky_banner.js"></script>
</body>
</html>