1232 lines
85 KiB
HTML
1232 lines
85 KiB
HTML
|
||
<!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 249 – Python Database API Specification v2.0 | peps.python.org</title>
|
||
<link rel="shortcut icon" href="../_static/py.png">
|
||
<link rel="canonical" href="https://peps.python.org/pep-0249/">
|
||
<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 249 – Python Database API Specification v2.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-0249/">
|
||
<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> » </li>
|
||
<li><a href="../pep-0000/">PEP Index</a> » </li>
|
||
<li>PEP 249</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 249 – Python Database API Specification v2.0</h1>
|
||
<dl class="rfc2822 field-list simple">
|
||
<dt class="field-odd">Author<span class="colon">:</span></dt>
|
||
<dd class="field-odd">Marc-André Lemburg <mal at lemburg.com></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">12-Apr-1999</dd>
|
||
<dt class="field-even">Post-History<span class="colon">:</span></dt>
|
||
<dd class="field-even"><p></p></dd>
|
||
<dt class="field-odd">Replaces<span class="colon">:</span></dt>
|
||
<dd class="field-odd"><a class="reference external" href="../pep-0248/">248</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><ul>
|
||
<li><a class="reference internal" href="#constructors">Constructors</a></li>
|
||
<li><a class="reference internal" href="#globals">Globals</a></li>
|
||
<li><a class="reference internal" href="#exceptions">Exceptions</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#connection-objects">Connection Objects</a><ul>
|
||
<li><a class="reference internal" href="#connection-methods">Connection methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#cursor-objects">Cursor Objects</a><ul>
|
||
<li><a class="reference internal" href="#cursor-attributes">Cursor attributes</a></li>
|
||
<li><a class="reference internal" href="#cursor-methods">Cursor methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#type-objects-and-constructors">Type Objects and Constructors</a></li>
|
||
<li><a class="reference internal" href="#implementation-hints-for-module-authors">Implementation Hints for Module Authors</a></li>
|
||
<li><a class="reference internal" href="#optional-db-api-extensions">Optional DB API Extensions</a></li>
|
||
<li><a class="reference internal" href="#optional-error-handling-extensions">Optional Error Handling Extensions</a></li>
|
||
<li><a class="reference internal" href="#optional-two-phase-commit-extensions">Optional Two-Phase Commit Extensions</a><ul>
|
||
<li><a class="reference internal" href="#tpc-transaction-ids">TPC Transaction IDs</a></li>
|
||
<li><a class="reference internal" href="#tpc-connection-methods">TPC Connection Methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#frequently-asked-questions">Frequently Asked Questions</a></li>
|
||
<li><a class="reference internal" href="#major-changes-from-version-1-0-to-version-2-0">Major Changes from Version 1.0 to Version 2.0</a></li>
|
||
<li><a class="reference internal" href="#open-issues">Open Issues</a></li>
|
||
<li><a class="reference internal" href="#footnotes">Footnotes</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>Comments and questions about this specification may be directed to the
|
||
<a class="reference external" href="mailto:db-sig%40python.org">SIG for Database Interfacing with Python</a>.</p>
|
||
<p>For more information on database interfacing with Python and available
|
||
packages see the <a class="reference external" href="http://www.python.org/topics/database/">Database Topic Guide</a>.</p>
|
||
<p>This document describes the Python Database API Specification 2.0 and
|
||
a set of common optional extensions. The previous version 1.0 version
|
||
is still available as reference, in <a class="pep reference internal" href="../pep-0248/" title="PEP 248 – Python Database API Specification v1.0">PEP 248</a>. Package writers are
|
||
encouraged to use this version of the specification as basis for new
|
||
interfaces.</p>
|
||
</section>
|
||
<section id="module-interface">
|
||
<h2><a class="toc-backref" href="#module-interface" role="doc-backlink">Module Interface</a></h2>
|
||
<section id="constructors">
|
||
<h3><a class="toc-backref" href="#constructors" role="doc-backlink">Constructors</a></h3>
|
||
<p>Access to the database is made available through connection
|
||
objects. The module must provide the following constructor for these:</p>
|
||
<dl id="connect">
|
||
<dt><a class="reference internal" href="#connect">connect</a>( <em>parameters…</em> )</dt><dd>Constructor for creating a connection to the database.<p>Returns a <a class="reference internal" href="#connection">Connection</a> Object. It takes a number of parameters
|
||
which are database dependent. <a class="footnote-reference brackets" href="#id48" id="id1">[1]</a></p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
<section id="globals">
|
||
<h3><a class="toc-backref" href="#globals" role="doc-backlink">Globals</a></h3>
|
||
<p>These module globals must be defined:</p>
|
||
<dl id="apilevel">
|
||
<dt><a class="reference internal" href="#apilevel">apilevel</a></dt><dd>String constant stating the supported DB API level.<p>Currently only the strings “<code class="docutils literal notranslate"><span class="pre">1.0</span></code>” and “<code class="docutils literal notranslate"><span class="pre">2.0</span></code>” are allowed.
|
||
If not given, a DB-API 1.0 level interface should be assumed.</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="threadsafety">
|
||
<dt><a class="reference internal" href="#threadsafety">threadsafety</a></dt><dd>Integer constant stating the level of thread safety the interface
|
||
supports. Possible values are:<table class="docutils align-default">
|
||
<thead>
|
||
<tr class="row-odd"><th class="head">threadsafety</th>
|
||
<th class="head">Meaning</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr class="row-even"><td>0</td>
|
||
<td>Threads may not share the module.</td>
|
||
</tr>
|
||
<tr class="row-odd"><td>1</td>
|
||
<td>Threads may share the module, but not connections.</td>
|
||
</tr>
|
||
<tr class="row-even"><td>2</td>
|
||
<td>Threads may share the module and connections.</td>
|
||
</tr>
|
||
<tr class="row-odd"><td>3</td>
|
||
<td>Threads may share the module, connections and cursors.</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p>Sharing in the above context means that two threads may use a
|
||
resource without wrapping it using a mutex semaphore to implement
|
||
resource locking. Note that you cannot always make external
|
||
resources thread safe by managing access using a mutex: the
|
||
resource may rely on global variables or other external sources
|
||
that are beyond your control.</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="paramstyle">
|
||
<dt><a class="reference internal" href="#paramstyle">paramstyle</a></dt><dd>String constant stating the type of parameter marker formatting
|
||
expected by the interface. Possible values are <a class="footnote-reference brackets" href="#id50" id="id2">[2]</a>:<table class="docutils align-default">
|
||
<thead>
|
||
<tr class="row-odd"><th class="head">paramstyle</th>
|
||
<th class="head">Meaning</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">qmark</span></code></td>
|
||
<td>Question mark style, e.g. <code class="docutils literal notranslate"><span class="pre">...WHERE</span> <span class="pre">name=?</span></code></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">numeric</span></code></td>
|
||
<td>Numeric, positional style, e.g. <code class="docutils literal notranslate"><span class="pre">...WHERE</span> <span class="pre">name=:1</span></code></td>
|
||
</tr>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">named</span></code></td>
|
||
<td>Named style, e.g. <code class="docutils literal notranslate"><span class="pre">...WHERE</span> <span class="pre">name=:name</span></code></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">format</span></code></td>
|
||
<td>ANSI C printf format codes, e.g. <code class="docutils literal notranslate"><span class="pre">...WHERE</span> <span class="pre">name=%s</span></code></td>
|
||
</tr>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">pyformat</span></code></td>
|
||
<td>Python extended format codes, e.g. <code class="docutils literal notranslate"><span class="pre">...WHERE</span> <span class="pre">name=%(name)s</span></code></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
<section id="exceptions">
|
||
<h3><a class="toc-backref" href="#exceptions" role="doc-backlink">Exceptions</a></h3>
|
||
<p>The module should make all error information available through these
|
||
exceptions or subclasses thereof:</p>
|
||
<dl class="simple" id="warning">
|
||
<dt><a class="reference internal" href="#warning">Warning</a></dt><dd>Exception raised for important warnings like data truncations
|
||
while inserting, etc. It must be a subclass of the Python
|
||
<code class="docutils literal notranslate"><span class="pre">Exception</span></code> class <a class="footnote-reference brackets" href="#id58" id="id3">[10]</a> <a class="footnote-reference brackets" href="#id59" id="id4">[11]</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="error">
|
||
<dt><a class="reference internal" href="#error">Error</a></dt><dd>Exception that is the base class of all other error
|
||
exceptions. You can use this to catch all errors with one single
|
||
<code class="docutils literal notranslate"><span class="pre">except</span></code> statement. Warnings are not considered errors and thus
|
||
should not use this class as base. It must be a subclass of the
|
||
Python <code class="docutils literal notranslate"><span class="pre">Exception</span></code> class <a class="footnote-reference brackets" href="#id58" id="id5">[10]</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="interfaceerror">
|
||
<dt><a class="reference internal" href="#interfaceerror">InterfaceError</a></dt><dd>Exception raised for errors that are related to the database
|
||
interface rather than the database itself. It must be a subclass
|
||
of <a class="reference internal" href="#error">Error</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="databaseerror">
|
||
<dt><a class="reference internal" href="#databaseerror">DatabaseError</a></dt><dd>Exception raised for errors that are related to the database. It
|
||
must be a subclass of <a class="reference internal" href="#error">Error</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="dataerror">
|
||
<dt><a class="reference internal" href="#dataerror">DataError</a></dt><dd>Exception raised for errors that are due to problems with the
|
||
processed data like division by zero, numeric value out of range,
|
||
etc. It must be a subclass of <a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="operationalerror">
|
||
<dt><a class="reference internal" href="#operationalerror">OperationalError</a></dt><dd>Exception raised for errors that are related to the database’s
|
||
operation and not necessarily under the control of the programmer,
|
||
e.g. an unexpected disconnect occurs, the data source name is not
|
||
found, a transaction could not be processed, a memory allocation
|
||
error occurred during processing, etc. It must be a subclass of
|
||
<a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="integrityerror">
|
||
<dt><a class="reference internal" href="#integrityerror">IntegrityError</a></dt><dd>Exception raised when the relational integrity of the database is
|
||
affected, e.g. a foreign key check fails. It must be a subclass
|
||
of <a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="internalerror">
|
||
<dt><a class="reference internal" href="#internalerror">InternalError</a></dt><dd>Exception raised when the database encounters an internal error,
|
||
e.g. the cursor is not valid anymore, the transaction is out of
|
||
sync, etc. It must be a subclass of <a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="programmingerror">
|
||
<dt><a class="reference internal" href="#programmingerror">ProgrammingError</a></dt><dd>Exception raised for programming errors, e.g. table not found or
|
||
already exists, syntax error in the SQL statement, wrong number of
|
||
parameters specified, etc. It must be a subclass of
|
||
<a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<dl class="simple" id="notsupportederror">
|
||
<dt><a class="reference internal" href="#notsupportederror">NotSupportedError</a></dt><dd>Exception raised in case a method or database API was used which
|
||
is not supported by the database, e.g. requesting a
|
||
<a class="reference internal" href="#id9">.rollback()</a> on a connection that does not support transaction
|
||
or has transactions turned off. It must be a subclass of
|
||
<a class="reference internal" href="#databaseerror">DatabaseError</a>.</dd>
|
||
</dl>
|
||
<p>This is the exception inheritance layout <a class="footnote-reference brackets" href="#id58" id="id6">[10]</a> <a class="footnote-reference brackets" href="#id59" id="id7">[11]</a>:</p>
|
||
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>Exception
|
||
|__Warning
|
||
|__Error
|
||
|__InterfaceError
|
||
|__DatabaseError
|
||
|__DataError
|
||
|__OperationalError
|
||
|__IntegrityError
|
||
|__InternalError
|
||
|__ProgrammingError
|
||
|__NotSupportedError
|
||
</pre></div>
|
||
</div>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>The values of these exceptions are not defined. They should give the user
|
||
a fairly good idea of what went wrong, though.</p>
|
||
</div>
|
||
</section>
|
||
</section>
|
||
<section id="connection-objects">
|
||
<span id="connection"></span><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>
|
||
<section id="connection-methods">
|
||
<h3><a class="toc-backref" href="#connection-methods" role="doc-backlink">Connection methods</a></h3>
|
||
<dl id="connection-close">
|
||
<dt><a class="reference external" href="#Connection.close">.close()</a></dt><dd>Close the connection now (rather than whenever <code class="docutils literal notranslate"><span class="pre">.__del__()</span></code> is
|
||
called).<p>The connection will be unusable from this point forward; an <a class="reference internal" href="#error">Error</a>
|
||
(or subclass) exception will be raised if any operation is
|
||
attempted with the connection. The same applies to all cursor
|
||
objects trying to use the connection. Note that closing a
|
||
connection without committing the changes first will cause an
|
||
implicit rollback to be performed.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="commit"></span><dl id="id8">
|
||
<dt><a class="reference internal" href="#commit">.commit</a>()</dt><dd>Commit any pending transaction to the database.<p>Note that if the database supports an auto-commit feature, this must be
|
||
initially off. An interface method may be provided to turn it back on.</p>
|
||
<p>Database modules that do not support transactions should implement this
|
||
method with void functionality.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="rollback"></span><dl id="id9">
|
||
<dt><a class="reference internal" href="#rollback">.rollback</a>()</dt><dd>This method is optional since not all databases provide transaction
|
||
support. <a class="footnote-reference brackets" href="#id51" id="id10">[3]</a><p>In case a database does provide transactions this method causes the
|
||
database to roll back to the start of any pending transaction. Closing a
|
||
connection without committing the changes first will cause an implicit
|
||
rollback to be performed.</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="cursor">
|
||
<dt><a class="reference internal" href="#cursor">.cursor</a>()</dt><dd>Return a new <a class="reference internal" href="#id12">Cursor</a> Object using the connection.<p>If the database does not provide a direct cursor concept, the module will
|
||
have to emulate cursors using other means to the extent needed by this
|
||
specification. <a class="footnote-reference brackets" href="#id52" id="id11">[4]</a></p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
</section>
|
||
<section id="cursor-objects">
|
||
<span id="id12"></span><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. Cursors created from the same connection
|
||
are not isolated, <em>i.e.</em>, any changes done to the database by a cursor
|
||
are immediately visible by the other cursors. Cursors created from
|
||
different connections can or can not be isolated, depending on how the
|
||
transaction support is implemented (see also the connection’s
|
||
<a class="reference internal" href="#rollback">.rollback</a>() and <a class="reference internal" href="#commit">.commit</a>() methods).</p>
|
||
<p>Cursor Objects should respond to the following methods and attributes.</p>
|
||
<section id="cursor-attributes">
|
||
<h3><a class="toc-backref" href="#cursor-attributes" role="doc-backlink">Cursor attributes</a></h3>
|
||
<dl id="description">
|
||
<dt><a class="reference internal" href="#description">.description</a></dt><dd>This read-only attribute is a sequence of 7-item sequences.<p>Each of these sequences contains information describing one result
|
||
column:</p>
|
||
<ul class="simple">
|
||
<li><code class="docutils literal notranslate"><span class="pre">name</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">type_code</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">display_size</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">internal_size</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">precision</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">scale</span></code></li>
|
||
<li><code class="docutils literal notranslate"><span class="pre">null_ok</span></code></li>
|
||
</ul>
|
||
<p>The first two items (<code class="docutils literal notranslate"><span class="pre">name</span></code> and <code class="docutils literal notranslate"><span class="pre">type_code</span></code>) are mandatory,
|
||
the other five are optional and are set to <code class="docutils literal notranslate"><span class="pre">None</span></code> if no
|
||
meaningful values can be provided.</p>
|
||
<p>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
|
||
<a class="reference internal" href="#id19">.execute*()</a> method yet.</p>
|
||
<p>The <code class="docutils literal notranslate"><span class="pre">type_code</span></code> can be interpreted by comparing it to the <a class="reference internal" href="#type-objects">Type
|
||
Objects</a> specified in the section below.</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="rowcount">
|
||
<dt><a class="reference internal" href="#rowcount">.rowcount</a></dt><dd>This read-only attribute specifies the number of rows that the last
|
||
<a class="reference internal" href="#id19">.execute*()</a> produced (for DQL statements like <code class="docutils literal notranslate"><span class="pre">SELECT</span></code>) or affected
|
||
(for DML statements like <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> or <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>). <a class="footnote-reference brackets" href="#id57" id="id13">[9]</a><p>The attribute is -1 in case no <a class="reference internal" href="#id19">.execute*()</a> has been performed
|
||
on the cursor or the rowcount of the last operation is cannot be
|
||
determined by the interface. <a class="footnote-reference brackets" href="#id55" id="id14">[7]</a></p>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>Future versions of the DB API specification could redefine the
|
||
latter case to have the object return <code class="docutils literal notranslate"><span class="pre">None</span></code> instead of -1.</p>
|
||
</div>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
<section id="cursor-methods">
|
||
<h3><a class="toc-backref" href="#cursor-methods" role="doc-backlink">Cursor methods</a></h3>
|
||
<span id="callproc"></span><dl id="id15">
|
||
<dt><a class="reference internal" href="#callproc">.callproc</a>( <em>procname</em> [, <em>parameters</em> ] )</dt><dd>(This method is optional since not all databases provide stored
|
||
procedures. <a class="footnote-reference brackets" href="#id51" id="id16">[3]</a>)<p>Call a stored database procedure with the given name. The sequence
|
||
of parameters must contain one entry for each argument that the
|
||
procedure expects. The result of the call is returned as modified
|
||
copy of the input sequence. Input parameters are left untouched,
|
||
output and input/output parameters replaced with possibly new
|
||
values.</p>
|
||
<p>The procedure may also provide a result set as output. This must
|
||
then be made available through the standard <a class="reference internal" href="#id24">.fetch*()</a> methods.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="cursor-close"></span><dl id="id17">
|
||
<dt><a class="reference external" href="#Cursor.close">.close</a>()</dt><dd>Close the cursor now (rather than whenever <code class="docutils literal notranslate"><span class="pre">__del__</span></code> is called).<p>The cursor will be unusable from this point forward; an <a class="reference internal" href="#error">Error</a> (or
|
||
subclass) exception will be raised if any operation is attempted
|
||
with the cursor.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="id20"></span><span id="id19"></span><span id="execute"></span><dl id="id21">
|
||
<dt><a class="reference internal" href="#id20">.execute</a>(<em>operation</em> [, <em>parameters</em>])</dt><dd>Prepare and execute a database operation (query or command).<p>Parameters may be provided as sequence or mapping and will be
|
||
bound to variables in the operation. Variables are specified in a
|
||
database-specific notation (see the module’s <a class="reference internal" href="#paramstyle">paramstyle</a> attribute
|
||
for details). <a class="footnote-reference brackets" href="#id53" id="id22">[5]</a></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 <a class="reference internal" href="#id31">.setinputsizes()</a> 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>The parameters may also be specified as list of tuples to
|
||
e.g. insert multiple rows in a single operation, but this kind of
|
||
usage is deprecated: <a class="reference internal" href="#id23">.executemany()</a> should be used instead.</p>
|
||
<p>Return values are not defined.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="executemany"></span><dl id="id23">
|
||
<dt><a class="reference internal" href="#executemany">.executemany</a>( <em>operation</em>, <em>seq_of_parameters</em> )</dt><dd>Prepare a database operation (query or command) and then execute it
|
||
against all parameter sequences or mappings found in the sequence
|
||
<em>seq_of_parameters</em>.<p>Modules are free to implement this method using multiple calls to
|
||
the <a class="reference internal" href="#id21">.execute()</a> method or by using array operations to have the
|
||
database process the sequence as a whole in one call.</p>
|
||
<p>Use of this method for an operation which produces one or more
|
||
result sets constitutes undefined behavior, and the implementation
|
||
is permitted (but not required) to raise an exception when it
|
||
detects that a result set has been created by an invocation of the
|
||
operation.</p>
|
||
<p>The same comments as for <a class="reference internal" href="#id21">.execute()</a> also apply accordingly to
|
||
this method.</p>
|
||
<p>Return values are not defined.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="fetchone"></span><span id="id24"></span><span id="fetch"></span><dl id="id25">
|
||
<dt><a class="reference internal" href="#fetchone">.fetchone</a>()</dt><dd>Fetch the next row of a query result set, returning a single
|
||
sequence, or <code class="docutils literal notranslate"><span class="pre">None</span></code> when no more data is available. <a class="footnote-reference brackets" href="#id54" id="id26">[6]</a><p>An <a class="reference internal" href="#error">Error</a> (or subclass) exception is raised if the previous call
|
||
to <a class="reference internal" href="#id19">.execute*()</a> did not produce any result set or no call was
|
||
issued yet.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="fetchmany"></span><dl id="id27">
|
||
<dt><a class="reference internal" href="#fetchmany">.fetchmany</a>([<em>size=cursor.arraysize</em>])</dt><dd>Fetch the next set of rows of a query result, returning a sequence
|
||
of sequences (e.g. a list of tuples). An empty sequence is
|
||
returned when no more rows are available.<p>The number of rows to fetch per call is specified by the
|
||
parameter. If it is not given, the cursor’s arraysize determines
|
||
the number of rows to be fetched. The method should try to fetch
|
||
as many rows as indicated by the size parameter. If this is not
|
||
possible due to the specified number of rows not being available,
|
||
fewer rows may be returned.</p>
|
||
<p>An <a class="reference internal" href="#error">Error</a> (or subclass) exception is raised if the previous call
|
||
to <a class="reference internal" href="#id19">.execute*()</a> did not produce any result set or no call was
|
||
issued yet.</p>
|
||
<p>Note there are performance considerations involved with the <em>size</em>
|
||
parameter. For optimal performance, it is usually best to use the
|
||
<a class="reference internal" href="#arraysize">.arraysize</a> attribute. If the size parameter is used, then it
|
||
is best for it to retain the same value from one <a class="reference internal" href="#id27">.fetchmany()</a>
|
||
call to the next.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="fetchall"></span><dl id="id28">
|
||
<dt><a class="reference internal" href="#fetchall">.fetchall</a>()</dt><dd>Fetch all (remaining) rows of a query result, returning them as a
|
||
sequence of sequences (e.g. a list of tuples). Note that the
|
||
cursor’s arraysize attribute can affect the performance of this
|
||
operation.<p>An <a class="reference internal" href="#error">Error</a> (or subclass) exception is raised if the previous call
|
||
to <a class="reference internal" href="#id19">.execute*()</a> did not produce any result set or no call was
|
||
issued yet.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="nextset"></span><dl id="id29">
|
||
<dt><a class="reference internal" href="#nextset">.nextset</a>()</dt><dd>(This method is optional since not all databases support multiple
|
||
result sets. <a class="footnote-reference brackets" href="#id51" id="id30">[3]</a>)<p>This method will make the cursor skip to the next available set,
|
||
discarding any remaining rows from the current set.</p>
|
||
<p>If there are no more sets, the method returns <code class="docutils literal notranslate"><span class="pre">None</span></code>. Otherwise,
|
||
it returns a true value and subsequent calls to the <a class="reference internal" href="#id24">.fetch*()</a>
|
||
methods will return rows from the next result set.</p>
|
||
<p>An <a class="reference internal" href="#error">Error</a> (or subclass) exception is raised if the previous call
|
||
to <a class="reference internal" href="#id19">.execute*()</a> did not produce any result set or no call was
|
||
issued yet.</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="arraysize">
|
||
<dt><a class="reference internal" href="#arraysize">.arraysize</a></dt><dd>This read/write attribute specifies the number of rows to fetch at
|
||
a time with <a class="reference internal" href="#id27">.fetchmany()</a>. It defaults to 1 meaning to fetch a
|
||
single row at a time.<p>Implementations must observe this value with respect to the
|
||
<a class="reference internal" href="#id27">.fetchmany()</a> method, but are free to interact with the database
|
||
a single row at a time. It may also be used in the implementation
|
||
of <a class="reference internal" href="#id23">.executemany()</a>.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="setinputsizes"></span><dl id="id31">
|
||
<dt><a class="reference internal" href="#setinputsizes">.setinputsizes</a>(<em>sizes</em>)</dt><dd>This can be used before a call to <a class="reference internal" href="#id19">.execute*()</a> to predefine
|
||
memory areas for the operation’s parameters.<p><em>sizes</em> is specified as a sequence — 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>
|
||
<p>This method would be used before the <a class="reference internal" href="#id19">.execute*()</a> method is
|
||
invoked.</p>
|
||
<p>Implementations are free to have this method do nothing and users
|
||
are free to not use it.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="setoutputsize"></span><dl id="id32">
|
||
<dt><a class="reference internal" href="#setoutputsize">.setoutputsize</a>(<em>size</em> [, <em>column</em>])</dt><dd>Set a column buffer size for fetches of large columns
|
||
(e.g. <code class="docutils literal notranslate"><span class="pre">LONG</span></code>s, <code class="docutils literal notranslate"><span class="pre">BLOB</span></code>s, etc.). The column is specified as
|
||
an index into the result sequence. Not specifying the column will
|
||
set the default size for all large columns in the cursor.<p>This method would be used before the <a class="reference internal" href="#id19">.execute*()</a> method is
|
||
invoked.</p>
|
||
<p>Implementations are free to have this method do nothing and users
|
||
are free to not use it.</p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
</section>
|
||
<section id="type-objects-and-constructors">
|
||
<span id="type-objects"></span><h2><a class="toc-backref" href="#type-objects-and-constructors" role="doc-backlink">Type Objects and Constructors</a></h2>
|
||
<p>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 <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 <a class="reference internal" href="#id19">.execute*()</a> method are untyped. When the database module sees
|
||
a Python string object, it doesn’t know if it should be bound as a
|
||
simple <code class="docutils literal notranslate"><span class="pre">CHAR</span></code> column, as a raw <code class="docutils literal notranslate"><span class="pre">BINARY</span></code> item, or as a <code class="docutils literal notranslate"><span class="pre">DATE</span></code>.</p>
|
||
<p>To overcome this problem, a module must provide the constructors
|
||
defined below to create objects that can hold special values. When
|
||
passed to the cursor methods, the module can then detect the proper
|
||
type of the input parameter and bind it accordingly.</p>
|
||
<p>A <a class="reference internal" href="#id12">Cursor</a> Object’s description attribute returns information about
|
||
each of the result columns of a query. The <code class="docutils literal notranslate"><span class="pre">type_code</span></code> must compare
|
||
equal to one of Type Objects defined below. Type Objects may be equal
|
||
to more than one type code (e.g. <code class="docutils literal notranslate"><span class="pre">DATETIME</span></code> could be equal to the
|
||
type codes for date, time and timestamp columns; see the
|
||
<a class="reference internal" href="#implementation-hints">Implementation Hints</a> below for details).</p>
|
||
<p>The module exports the following constructors and singletons:</p>
|
||
<dl class="simple" id="date">
|
||
<dt><a class="reference internal" href="#date">Date</a>(<em>year</em>, <em>month</em>, <em>day</em>)</dt><dd>This function constructs an object holding a date value.</dd>
|
||
</dl>
|
||
<dl class="simple" id="time">
|
||
<dt><a class="reference internal" href="#time">Time</a>(<em>hour</em>, <em>minute</em>, <em>second</em>)</dt><dd>This function constructs an object holding a time value.</dd>
|
||
</dl>
|
||
<dl class="simple" id="timestamp">
|
||
<dt><a class="reference internal" href="#timestamp">Timestamp</a>(<em>year</em>, <em>month</em>, <em>day</em>, <em>hour</em>, <em>minute</em>, <em>second</em>)</dt><dd>This function constructs an object holding a time stamp value.</dd>
|
||
</dl>
|
||
<dl class="simple" id="datefromticks">
|
||
<dt><a class="reference internal" href="#datefromticks">DateFromTicks</a>(<em>ticks</em>)</dt><dd>This function constructs an object holding a date value from the
|
||
given ticks value (number of seconds since the epoch; see the
|
||
documentation of <a class="reference external" href="http://docs.python.org/library/time.html">the standard Python time module</a> for details).</dd>
|
||
</dl>
|
||
<dl class="simple" id="timefromticks">
|
||
<dt><a class="reference internal" href="#timefromticks">TimeFromTicks</a>(<em>ticks</em>)</dt><dd>This function constructs an object holding a time value from the
|
||
given ticks value (number of seconds since the epoch; see the
|
||
documentation of the standard Python time module for details).</dd>
|
||
</dl>
|
||
<dl class="simple" id="timestampfromticks">
|
||
<dt><a class="reference internal" href="#timestampfromticks">TimestampFromTicks</a>(<em>ticks</em>)</dt><dd>This function constructs an object holding a time stamp value from
|
||
the given ticks value (number of seconds since the epoch; see the
|
||
documentation of the standard Python time module for details).</dd>
|
||
</dl>
|
||
<dl class="simple" id="binary">
|
||
<dt><a class="reference internal" href="#binary">Binary</a>(<em>string</em>)</dt><dd>This function constructs an object capable of holding a binary
|
||
(long) string value.</dd>
|
||
</dl>
|
||
<dl class="simple" id="string">
|
||
<dt><a class="reference internal" href="#string">STRING</a> type</dt><dd>This type object is used to describe columns in a database that
|
||
are string-based (e.g. <code class="docutils literal notranslate"><span class="pre">CHAR</span></code>).</dd>
|
||
</dl>
|
||
<dl class="simple" id="binary-type">
|
||
<dt><a class="reference internal" href="#binary">BINARY</a> type</dt><dd>This type object is used to describe (long) binary columns in a
|
||
database (e.g. <code class="docutils literal notranslate"><span class="pre">LONG</span></code>, <code class="docutils literal notranslate"><span class="pre">RAW</span></code>, <code class="docutils literal notranslate"><span class="pre">BLOB</span></code>s).</dd>
|
||
</dl>
|
||
<dl class="simple" id="number">
|
||
<dt><a class="reference internal" href="#number">NUMBER</a> type</dt><dd>This type object is used to describe numeric columns in a
|
||
database.</dd>
|
||
</dl>
|
||
<dl class="simple" id="datetime">
|
||
<dt><a class="reference internal" href="#datetime">DATETIME</a> type</dt><dd>This type object is used to describe date/time columns in a
|
||
database.</dd>
|
||
</dl>
|
||
<dl class="simple" id="rowid">
|
||
<dt><a class="reference internal" href="#rowid">ROWID</a> type</dt><dd>This type object is used to describe the “Row ID” column in a
|
||
database.</dd>
|
||
</dl>
|
||
<p>SQL <code class="docutils literal notranslate"><span class="pre">NULL</span></code> values are represented by the Python <code class="docutils literal notranslate"><span class="pre">None</span></code> singleton
|
||
on input and output.</p>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>Usage of Unix ticks for database interfacing can cause troubles
|
||
because of the limited date range they cover.</p>
|
||
</div>
|
||
</section>
|
||
<section id="implementation-hints-for-module-authors">
|
||
<span id="implementation-hints"></span><h2><a class="toc-backref" href="#implementation-hints-for-module-authors" role="doc-backlink">Implementation Hints for Module Authors</a></h2>
|
||
<ul>
|
||
<li>Date/time objects can be implemented as <a class="reference external" href="http://docs.python.org/library/datetime.html">Python datetime module</a> objects (available
|
||
since Python 2.3, with a C API since 2.4) or using the <a class="reference external" href="http://www.egenix.com/products/python/mxBase/mxDateTime/">mxDateTime</a> package
|
||
(available for all Python versions since 1.5.2). They both provide
|
||
all necessary constructors and methods at Python and C level.</li>
|
||
<li>Here is a sample implementation of the Unix ticks based constructors
|
||
for date/time delegating work to the generic constructors:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">time</span>
|
||
|
||
<span class="k">def</span> <span class="nf">DateFromTicks</span><span class="p">(</span><span class="n">ticks</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="n">Date</span><span class="p">(</span><span class="o">*</span><span class="n">time</span><span class="o">.</span><span class="n">localtime</span><span class="p">(</span><span class="n">ticks</span><span class="p">)[:</span><span class="mi">3</span><span class="p">])</span>
|
||
|
||
<span class="k">def</span> <span class="nf">TimeFromTicks</span><span class="p">(</span><span class="n">ticks</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="n">Time</span><span class="p">(</span><span class="o">*</span><span class="n">time</span><span class="o">.</span><span class="n">localtime</span><span class="p">(</span><span class="n">ticks</span><span class="p">)[</span><span class="mi">3</span><span class="p">:</span><span class="mi">6</span><span class="p">])</span>
|
||
|
||
<span class="k">def</span> <span class="nf">TimestampFromTicks</span><span class="p">(</span><span class="n">ticks</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="n">Timestamp</span><span class="p">(</span><span class="o">*</span><span class="n">time</span><span class="o">.</span><span class="n">localtime</span><span class="p">(</span><span class="n">ticks</span><span class="p">)[:</span><span class="mi">6</span><span class="p">])</span>
|
||
</pre></div>
|
||
</div>
|
||
</li>
|
||
<li>The preferred object type for Binary objects are the buffer types
|
||
available in standard Python starting with version 1.5.2. Please
|
||
see the Python documentation for details. For information about the
|
||
C interface have a look at <code class="docutils literal notranslate"><span class="pre">Include/bufferobject.h</span></code> and
|
||
<code class="docutils literal notranslate"><span class="pre">Objects/bufferobject.c</span></code> in the Python source distribution.</li>
|
||
<li>This Python class allows implementing the above type objects even
|
||
though the description type code field yields multiple values for on
|
||
type object:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">DBAPITypeObject</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span><span class="o">*</span><span class="n">values</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">values</span> <span class="o">=</span> <span class="n">values</span>
|
||
<span class="k">def</span> <span class="nf">__cmp__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span><span class="n">other</span><span class="p">):</span>
|
||
<span class="k">if</span> <span class="n">other</span> <span class="ow">in</span> <span class="bp">self</span><span class="o">.</span><span class="n">values</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="mi">0</span>
|
||
<span class="k">if</span> <span class="n">other</span> <span class="o"><</span> <span class="bp">self</span><span class="o">.</span><span class="n">values</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="mi">1</span>
|
||
<span class="k">else</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="o">-</span><span class="mi">1</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>The resulting type object compares equal to all values passed to the
|
||
constructor.</p>
|
||
</li>
|
||
<li>Here is a snippet of Python code that implements the exception
|
||
hierarchy defined above <a class="footnote-reference brackets" href="#id58" id="id33">[10]</a>:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Error</span><span class="p">(</span><span class="ne">Exception</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">Warning</span><span class="p">(</span><span class="ne">Exception</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">InterfaceError</span><span class="p">(</span><span class="n">Error</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">DatabaseError</span><span class="p">(</span><span class="n">Error</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">InternalError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">OperationalError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">ProgrammingError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">IntegrityError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">DataError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span> <span class="nc">NotSupportedError</span><span class="p">(</span><span class="n">DatabaseError</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>In C you can use the <code class="docutils literal notranslate"><span class="pre">PyErr_NewException(fullname,</span> <span class="pre">base,</span> <span class="pre">NULL)</span></code>
|
||
API to create the exception objects.</p>
|
||
</li>
|
||
</ul>
|
||
</section>
|
||
<section id="optional-db-api-extensions">
|
||
<h2><a class="toc-backref" href="#optional-db-api-extensions" role="doc-backlink">Optional DB API Extensions</a></h2>
|
||
<p>During the lifetime of DB API 2.0, module authors have often extended
|
||
their implementations beyond what is required by this DB API
|
||
specification. To enhance compatibility and to provide a clean upgrade
|
||
path to possible future versions of the specification, this section
|
||
defines a set of common extensions to the core DB API 2.0
|
||
specification.</p>
|
||
<p>As with all DB API optional features, the database module authors are
|
||
free to not implement these additional attributes and methods (using
|
||
them will then result in an <code class="docutils literal notranslate"><span class="pre">AttributeError</span></code>) or to raise a
|
||
<a class="reference internal" href="#notsupportederror">NotSupportedError</a> in case the availability can only be checked at
|
||
run-time.</p>
|
||
<p>It has been proposed to make usage of these extensions optionally
|
||
visible to the programmer by issuing Python warnings through the
|
||
Python warning framework. To make this feature useful, the warning
|
||
messages must be standardized in order to be able to mask them. These
|
||
standard messages are referred to below as <em>Warning Message</em>.</p>
|
||
<dl id="rownumber">
|
||
<dt>Cursor<a class="reference internal" href="#rownumber">.rownumber</a></dt><dd>This read-only attribute should provide the current 0-based index
|
||
of the cursor in the result set or <code class="docutils literal notranslate"><span class="pre">None</span></code> if the index cannot be
|
||
determined.<p>The index can be seen as index of the cursor in a sequence (the
|
||
result set). The next fetch operation will fetch the row indexed
|
||
by <a class="reference internal" href="#rownumber">.rownumber</a> in that sequence.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension cursor.rownumber used”</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="connection-error"></span><dl id="connection-programmingerror">
|
||
<dt><a class="reference internal" href="#connection-error">Connection.Error</a>, <a class="reference internal" href="#connection-programmingerror">Connection.ProgrammingError</a>, etc.</dt><dd>All exception classes defined by the DB API standard should be
|
||
exposed on the <a class="reference internal" href="#connection">Connection</a> objects as attributes (in addition to
|
||
being available at module scope).<p>These attributes simplify error handling in multi-connection
|
||
environments.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension connection.<exception> used”</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="id34">
|
||
<dt>Cursor<a class="reference internal" href="#id34">.connection</a></dt><dd>This read-only attribute return a reference to the <a class="reference internal" href="#connection">Connection</a>
|
||
object on which the cursor was created.<p>The attribute simplifies writing polymorph code in
|
||
multi-connection environments.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension cursor.connection used”</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="scroll"></span><dl id="id35">
|
||
<dt>Cursor<a class="reference internal" href="#scroll">.scroll</a>(<em>value</em> [, <em>mode=’relative’</em> ])</dt><dd>Scroll the cursor in the result set to a new position according to
|
||
<em>mode</em>.<p>If mode is <code class="docutils literal notranslate"><span class="pre">relative</span></code> (default), value is taken as offset to the
|
||
current position in the result set, if set to <code class="docutils literal notranslate"><span class="pre">absolute</span></code>, value
|
||
states an absolute target position.</p>
|
||
<p>An <code class="docutils literal notranslate"><span class="pre">IndexError</span></code> should be raised in case a scroll operation
|
||
would leave the result set. In this case, the cursor position is
|
||
left undefined (ideal would be to not move the cursor at all).</p>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>This method should use native scrollable cursors, if available,
|
||
or revert to an emulation for forward-only scrollable
|
||
cursors. The method may raise <a class="reference internal" href="#notsupportederror">NotSupportedError</a> to signal
|
||
that a specific operation is not supported by the database
|
||
(e.g. backward scrolling).</p>
|
||
</div>
|
||
<p><em>Warning Message:</em> “DB-API extension cursor.scroll() used”</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="cursor-messages">
|
||
<dt><a class="reference internal" href="#cursor-messages">Cursor.messages</a></dt><dd>This is a Python list object to which the interface appends tuples
|
||
(exception class, exception value) for all messages which the
|
||
interfaces receives from the underlying database for this cursor.<p>The list is cleared by all standard cursor methods calls (prior to
|
||
executing the call) except for the <a class="reference internal" href="#id24">.fetch*()</a> calls
|
||
automatically to avoid excessive memory usage and can also be
|
||
cleared by executing <code class="docutils literal notranslate"><span class="pre">del</span> <span class="pre">cursor.messages[:]</span></code>.</p>
|
||
<p>All error and warning messages generated by the database are
|
||
placed into this list, so checking the list allows the user to
|
||
verify correct operation of the method calls.</p>
|
||
<p>The aim of this attribute is to eliminate the need for a Warning
|
||
exception which often causes problems (some warnings really only
|
||
have informational character).</p>
|
||
<p><em>Warning Message:</em> “DB-API extension cursor.messages used”</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="connection-messages">
|
||
<dt><a class="reference internal" href="#connection-messages">Connection.messages</a></dt><dd>Same as <a class="reference internal" href="#cursor-messages">Cursor.messages</a> except that the messages in the list are
|
||
connection oriented.<p>The list is cleared automatically by all standard connection
|
||
methods calls (prior to executing the call) to avoid excessive
|
||
memory usage and can also be cleared by executing <code class="docutils literal notranslate"><span class="pre">del</span>
|
||
<span class="pre">connection.messages[:]</span></code>.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension connection.messages used”</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="next"></span><dl id="id36">
|
||
<dt>Cursor<a class="reference internal" href="#next">.next</a>()</dt><dd>Return the next row from the currently executing SQL statement
|
||
using the same semantics as <a class="reference internal" href="#id25">.fetchone()</a>. A <code class="docutils literal notranslate"><span class="pre">StopIteration</span></code>
|
||
exception is raised when the result set is exhausted for Python
|
||
versions 2.2 and later. Previous versions don’t have the
|
||
<code class="docutils literal notranslate"><span class="pre">StopIteration</span></code> exception and so the method should raise an
|
||
<code class="docutils literal notranslate"><span class="pre">IndexError</span></code> instead.<p><em>Warning Message:</em> “DB-API extension cursor.next() used”</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="iter"></span><dl id="id37">
|
||
<dt>Cursor<a class="reference internal" href="#iter">.__iter__</a>()</dt><dd>Return self to make cursors compatible to the iteration protocol
|
||
<a class="footnote-reference brackets" href="#id56" id="id38">[8]</a>.<p><em>Warning Message:</em> “DB-API extension cursor.__iter__() used”</p>
|
||
</dd>
|
||
</dl>
|
||
<dl id="lastrowid">
|
||
<dt>Cursor<a class="reference internal" href="#lastrowid">.lastrowid</a></dt><dd>This read-only attribute provides the rowid of the last modified
|
||
row (most databases return a rowid only when a single <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>
|
||
operation is performed). If the operation does not set a rowid or
|
||
if the database does not support rowids, this attribute should be
|
||
set to <code class="docutils literal notranslate"><span class="pre">None</span></code>.<p>The semantics of <code class="docutils literal notranslate"><span class="pre">.lastrowid</span></code> are undefined in case the last
|
||
executed statement modified more than one row, e.g. when using
|
||
<code class="docutils literal notranslate"><span class="pre">INSERT</span></code> with <code class="docutils literal notranslate"><span class="pre">.executemany()</span></code>.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension cursor.lastrowid used”</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="connection-autocommit"></span><dl id="autocommit">
|
||
<dt>Connection<a class="reference internal" href="#autocommit">.autocommit</a></dt><dd>Attribute to query and set the autocommit mode of the connection.<p>Return <code class="docutils literal notranslate"><span class="pre">True</span></code> if the connection is operating in autocommit
|
||
(non-transactional) mode. Return <code class="docutils literal notranslate"><span class="pre">False</span></code> if the connection is
|
||
operating in manual commit (transactional) mode.</p>
|
||
<p>Setting the attribute to <code class="docutils literal notranslate"><span class="pre">True</span></code> or <code class="docutils literal notranslate"><span class="pre">False</span></code> adjusts the
|
||
connection’s mode accordingly.</p>
|
||
<p>Changing the setting from <code class="docutils literal notranslate"><span class="pre">True</span></code> to <code class="docutils literal notranslate"><span class="pre">False</span></code> (disabling
|
||
autocommit) will have the database leave autocommit mode and start
|
||
a new transaction. Changing from <code class="docutils literal notranslate"><span class="pre">False</span></code> to <code class="docutils literal notranslate"><span class="pre">True</span></code> (enabling
|
||
autocommit) has database dependent semantics with respect to how
|
||
pending transactions are handled. <a class="footnote-reference brackets" href="#id60" id="id39">[12]</a></p>
|
||
<p><em>Deprecation notice</em>: Even though several database modules implement
|
||
both the read and write nature of this attribute, setting the
|
||
autocommit mode by writing to the attribute is deprecated, since
|
||
this may result in I/O and related exceptions, making it difficult
|
||
to implement in an async context. <a class="footnote-reference brackets" href="#id61" id="id40">[13]</a></p>
|
||
<p><em>Warning Message:</em> “DB-API extension connection.autocommit used”</p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
<section id="optional-error-handling-extensions">
|
||
<h2><a class="toc-backref" href="#optional-error-handling-extensions" role="doc-backlink">Optional Error Handling Extensions</a></h2>
|
||
<p>The core DB API specification only introduces a set of exceptions
|
||
which can be raised to report errors to the user. In some cases,
|
||
exceptions may be too disruptive for the flow of a program or even
|
||
render execution impossible.</p>
|
||
<p>For these cases and in order to simplify error handling when dealing
|
||
with databases, database module authors may choose to implement user
|
||
definable error handlers. This section describes a standard way of
|
||
defining these error handlers.</p>
|
||
<span id="connection-errorhandler"></span><dl id="cursor-errorhandler">
|
||
<dt><a class="reference internal" href="#connection-errorhandler">Connection.errorhandler</a>, <a class="reference internal" href="#cursor-errorhandler">Cursor.errorhandler</a></dt><dd>Read/write attribute which references an error handler to call in
|
||
case an error condition is met.<p>The handler must be a Python callable taking the following arguments:</p>
|
||
<pre class="literal-block">errorhandler(<em>connection</em>, <em>cursor</em>, <em>errorclass</em>, <em>errorvalue</em>)</pre>
|
||
<p>where connection is a reference to the connection on which the
|
||
cursor operates, cursor a reference to the cursor (or <code class="docutils literal notranslate"><span class="pre">None</span></code> in
|
||
case the error does not apply to a cursor), <em>errorclass</em> is an
|
||
error class which to instantiate using <em>errorvalue</em> as
|
||
construction argument.</p>
|
||
<p>The standard error handler should add the error information to the
|
||
appropriate <code class="docutils literal notranslate"><span class="pre">.messages</span></code> attribute (<a class="reference internal" href="#connection-messages">Connection.messages</a> or
|
||
<a class="reference internal" href="#cursor-messages">Cursor.messages</a>) and raise the exception defined by the given
|
||
<em>errorclass</em> and <em>errorvalue</em> parameters.</p>
|
||
<p>If no <code class="docutils literal notranslate"><span class="pre">.errorhandler</span></code> is set (the attribute is <code class="docutils literal notranslate"><span class="pre">None</span></code>), the
|
||
standard error handling scheme as outlined above, should be
|
||
applied.</p>
|
||
<p><em>Warning Message:</em> “DB-API extension .errorhandler used”</p>
|
||
</dd>
|
||
</dl>
|
||
<p>Cursors should inherit the <code class="docutils literal notranslate"><span class="pre">.errorhandler</span></code> setting from their
|
||
connection objects at cursor creation time.</p>
|
||
</section>
|
||
<section id="optional-two-phase-commit-extensions">
|
||
<h2><a class="toc-backref" href="#optional-two-phase-commit-extensions" role="doc-backlink">Optional Two-Phase Commit Extensions</a></h2>
|
||
<p>Many databases have support for two-phase commit (TPC) which allows
|
||
managing transactions across multiple database connections and other
|
||
resources.</p>
|
||
<p>If a database backend provides support for two-phase commit and the
|
||
database module author wishes to expose this support, the following
|
||
API should be implemented. <a class="reference internal" href="#notsupportederror">NotSupportedError</a> should be raised, if the
|
||
database backend support for two-phase commit can only be checked at
|
||
run-time.</p>
|
||
<section id="tpc-transaction-ids">
|
||
<h3><a class="toc-backref" href="#tpc-transaction-ids" role="doc-backlink">TPC Transaction IDs</a></h3>
|
||
<p>As many databases follow the XA specification, transaction IDs are
|
||
formed from three components:</p>
|
||
<ul class="simple">
|
||
<li>a format ID</li>
|
||
<li>a global transaction ID</li>
|
||
<li>a branch qualifier</li>
|
||
</ul>
|
||
<p>For a particular global transaction, the first two components should
|
||
be the same for all resources. Each resource in the global
|
||
transaction should be assigned a different branch qualifier.</p>
|
||
<p>The various components must satisfy the following criteria:</p>
|
||
<ul class="simple">
|
||
<li>format ID: a non-negative 32-bit integer.</li>
|
||
<li>global transaction ID and branch qualifier: byte strings no
|
||
longer than 64 characters.</li>
|
||
</ul>
|
||
<p>Transaction IDs are created with the <a class="reference internal" href="#id41">.xid()</a> Connection method:</p>
|
||
<span id="xid"></span><dl id="id41">
|
||
<dt><a class="reference internal" href="#xid">.xid</a>(<em>format_id</em>, <em>global_transaction_id</em>, <em>branch_qualifier</em>)</dt><dd>Returns a transaction ID object suitable for passing to the
|
||
<a class="reference internal" href="#id42">.tpc_*()</a> methods of this connection.<p>If the database connection does not support TPC, a
|
||
<a class="reference internal" href="#notsupportederror">NotSupportedError</a> is raised.</p>
|
||
<p>The type of the object returned by <a class="reference internal" href="#id41">.xid()</a> is not defined, but
|
||
it must provide sequence behaviour, allowing access to the three
|
||
components. A conforming database module could choose to
|
||
represent transaction IDs with tuples rather than a custom object.</p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
<section id="tpc-connection-methods">
|
||
<h3><a class="toc-backref" href="#tpc-connection-methods" role="doc-backlink">TPC Connection Methods</a></h3>
|
||
<span id="tpc-begin"></span><span id="id42"></span><span id="tpc"></span><dl id="id43">
|
||
<dt><a class="reference internal" href="#tpc-begin">.tpc_begin</a>(<em>xid</em>)</dt><dd>Begins a TPC transaction with the given transaction ID <em>xid</em>.<p>This method should be called outside of a transaction (<em>i.e.</em>
|
||
nothing may have executed since the last <a class="reference internal" href="#id8">.commit()</a> or
|
||
<a class="reference internal" href="#id9">.rollback()</a>).</p>
|
||
<p>Furthermore, it is an error to call <a class="reference internal" href="#id8">.commit()</a> or <a class="reference internal" href="#id9">.rollback()</a>
|
||
within the TPC transaction. A <a class="reference internal" href="#programmingerror">ProgrammingError</a> is raised, if the
|
||
application calls <a class="reference internal" href="#id8">.commit()</a> or <a class="reference internal" href="#id9">.rollback()</a> during an active
|
||
TPC transaction.</p>
|
||
<p>If the database connection does not support TPC, a
|
||
<a class="reference internal" href="#notsupportederror">NotSupportedError</a> is raised.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="tpc-prepare"></span><dl id="id44">
|
||
<dt><a class="reference internal" href="#tpc-prepare">.tpc_prepare</a>()</dt><dd>Performs the first phase of a transaction started with
|
||
<a class="reference internal" href="#id43">.tpc_begin()</a>. A <a class="reference internal" href="#programmingerror">ProgrammingError</a> should be raised if this
|
||
method outside of a TPC transaction.<p>After calling <a class="reference internal" href="#id44">.tpc_prepare()</a>, no statements can be executed
|
||
until <a class="reference internal" href="#id45">.tpc_commit()</a> or <a class="reference internal" href="#id46">.tpc_rollback()</a> have been called.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="tpc-commit"></span><dl id="id45">
|
||
<dt><a class="reference internal" href="#tpc-commit">.tpc_commit</a>([ <em>xid</em> ])</dt><dd>When called with no arguments, <a class="reference internal" href="#id45">.tpc_commit()</a> commits a TPC
|
||
transaction previously prepared with <a class="reference internal" href="#id44">.tpc_prepare()</a>.<p>If <a class="reference internal" href="#id45">.tpc_commit()</a> is called prior to <a class="reference internal" href="#id44">.tpc_prepare()</a>, a single
|
||
phase commit is performed. A transaction manager may choose to do
|
||
this if only a single resource is participating in the global
|
||
transaction.</p>
|
||
<p>When called with a transaction ID <em>xid</em>, the database commits the
|
||
given transaction. If an invalid transaction ID is provided, a
|
||
<a class="reference internal" href="#programmingerror">ProgrammingError</a> will be raised. This form should be called
|
||
outside of a transaction, and is intended for use in recovery.</p>
|
||
<p>On return, the TPC transaction is ended.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="tpc-rollback"></span><dl id="id46">
|
||
<dt><a class="reference internal" href="#tpc-rollback">.tpc_rollback</a>([ <em>xid</em> ])</dt><dd>When called with no arguments, <a class="reference internal" href="#id46">.tpc_rollback()</a> rolls back a TPC
|
||
transaction. It may be called before or after <a class="reference internal" href="#id44">.tpc_prepare()</a>.<p>When called with a transaction ID <em>xid</em>, it rolls back the given
|
||
transaction. If an invalid transaction ID is provided, a
|
||
<a class="reference internal" href="#programmingerror">ProgrammingError</a> is raised. This form should be called outside
|
||
of a transaction, and is intended for use in recovery.</p>
|
||
<p>On return, the TPC transaction is ended.</p>
|
||
</dd>
|
||
</dl>
|
||
<span id="tpc-recover"></span><dl id="id47">
|
||
<dt><a class="reference internal" href="#tpc-recover">.tpc_recover</a>()</dt><dd>Returns a list of pending transaction IDs suitable for use with
|
||
<code class="docutils literal notranslate"><span class="pre">.tpc_commit(xid)</span></code> or <code class="docutils literal notranslate"><span class="pre">.tpc_rollback(xid)</span></code>.<p>If the database does not support transaction recovery, it may
|
||
return an empty list or raise <a class="reference internal" href="#notsupportederror">NotSupportedError</a>.</p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
</section>
|
||
<section id="frequently-asked-questions">
|
||
<h2><a class="toc-backref" href="#frequently-asked-questions" role="doc-backlink">Frequently Asked Questions</a></h2>
|
||
<p>The database SIG often sees reoccurring questions about the DB API
|
||
specification. This section covers some of the issues people sometimes
|
||
have with the specification.</p>
|
||
<p><strong>Question:</strong></p>
|
||
<p>How can I construct a dictionary out of the tuples returned by
|
||
<a class="reference internal" href="#id24">.fetch*()</a>:</p>
|
||
<p><strong>Answer:</strong></p>
|
||
<p>There are several existing tools available which provide helpers for
|
||
this task. Most of them use the approach of using the column names
|
||
defined in the cursor attribute <a class="reference internal" href="#description">.description</a> as basis for the keys
|
||
in the row dictionary.</p>
|
||
<p>Note that the reason for not extending the DB API specification to
|
||
also support dictionary return values for the <a class="reference internal" href="#id24">.fetch*()</a> methods is
|
||
that this approach has several drawbacks:</p>
|
||
<ul class="simple">
|
||
<li>Some databases don’t support case-sensitive column names or
|
||
auto-convert them to all lowercase or all uppercase characters.</li>
|
||
<li>Columns in the result set which are generated by the query (e.g.
|
||
using SQL functions) don’t map to table column names and databases
|
||
usually generate names for these columns in a very database specific
|
||
way.</li>
|
||
</ul>
|
||
<p>As a result, accessing the columns through dictionary keys varies
|
||
between databases and makes writing portable code impossible.</p>
|
||
</section>
|
||
<section id="major-changes-from-version-1-0-to-version-2-0">
|
||
<h2><a class="toc-backref" href="#major-changes-from-version-1-0-to-version-2-0" role="doc-backlink">Major Changes from Version 1.0 to Version 2.0</a></h2>
|
||
<p>The Python Database API 2.0 introduces a few major changes compared to
|
||
the 1.0 version. Because some of these changes will cause existing DB
|
||
API 1.0 based scripts to break, the major version number was adjusted
|
||
to reflect this change.</p>
|
||
<p>These are the most important changes from 1.0 to 2.0:</p>
|
||
<ul class="simple">
|
||
<li>The need for a separate dbi module was dropped and the functionality
|
||
merged into the module interface itself.</li>
|
||
<li>New constructors and <a class="reference internal" href="#type-objects">Type Objects</a> were added for date/time
|
||
values, the <code class="docutils literal notranslate"><span class="pre">RAW</span></code> Type Object was renamed to <code class="docutils literal notranslate"><span class="pre">BINARY</span></code>. The
|
||
resulting set should cover all basic data types commonly found in
|
||
modern SQL databases.</li>
|
||
<li>New constants (<a class="reference internal" href="#apilevel">apilevel</a>, <a class="reference internal" href="#threadsafety">threadsafety</a>, <a class="reference internal" href="#paramstyle">paramstyle</a>) and methods
|
||
(<a class="reference internal" href="#id23">.executemany()</a>, <a class="reference internal" href="#id29">.nextset()</a>) were added to provide better
|
||
database bindings.</li>
|
||
<li>The semantics of <a class="reference internal" href="#id15">.callproc()</a> needed to call stored procedures are
|
||
now clearly defined.</li>
|
||
<li>The definition of the <a class="reference internal" href="#id21">.execute()</a> return value changed.
|
||
Previously, the return value was based on the SQL statement type
|
||
(which was hard to implement right) — it is undefined now; use the
|
||
more flexible <a class="reference internal" href="#rowcount">.rowcount</a> attribute instead. Modules are free to
|
||
return the old style return values, but these are no longer mandated
|
||
by the specification and should be considered database interface
|
||
dependent.</li>
|
||
<li>Class based <a class="reference internal" href="#exceptions">exceptions</a> were incorporated into the specification.
|
||
Module implementors are free to extend the exception layout defined
|
||
in this specification by subclassing the defined exception classes.</li>
|
||
</ul>
|
||
<p>Post-publishing additions to the DB API 2.0 specification:</p>
|
||
<ul class="simple">
|
||
<li>Additional optional DB API extensions to the set of core
|
||
functionality were specified.</li>
|
||
</ul>
|
||
</section>
|
||
<section id="open-issues">
|
||
<h2><a class="toc-backref" href="#open-issues" role="doc-backlink">Open Issues</a></h2>
|
||
<p>Although the version 2.0 specification clarifies a lot of questions
|
||
that were left open in the 1.0 version, there are still some remaining
|
||
issues which should be addressed in future versions:</p>
|
||
<ul class="simple">
|
||
<li>Define a useful return value for <a class="reference internal" href="#id29">.nextset()</a> for the case where a
|
||
new result set is available.</li>
|
||
<li>Integrate the <a class="reference external" href="http://docs.python.org/library/decimal.html">decimal module</a> <code class="docutils literal notranslate"><span class="pre">Decimal</span></code> object
|
||
for use as loss-less monetary and decimal interchange format.</li>
|
||
</ul>
|
||
</section>
|
||
<section id="footnotes">
|
||
<h2><a class="toc-backref" href="#footnotes" role="doc-backlink">Footnotes</a></h2>
|
||
<aside class="footnote-list brackets">
|
||
<aside class="footnote brackets" id="id48" role="doc-footnote">
|
||
<dt class="label" id="id48">[<a href="#id1">1</a>]</dt>
|
||
<dd>As a guideline the connection constructor parameters should be
|
||
implemented as keyword parameters for more intuitive use and
|
||
follow this order of parameters:<table class="docutils align-default">
|
||
<thead>
|
||
<tr class="row-odd"><th class="head">Parameter</th>
|
||
<th class="head">Meaning</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">dsn</span></code></td>
|
||
<td>Data source name as string</td>
|
||
</tr>
|
||
<tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">user</span></code></td>
|
||
<td>User name as string (optional)</td>
|
||
</tr>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">password</span></code></td>
|
||
<td>Password as string (optional)</td>
|
||
</tr>
|
||
<tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">host</span></code></td>
|
||
<td>Hostname (optional)</td>
|
||
</tr>
|
||
<tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">database</span></code></td>
|
||
<td>Database name (optional)</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p>E.g. a connect could look like this:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connect</span><span class="p">(</span><span class="n">dsn</span><span class="o">=</span><span class="s1">'myhost:MYDB'</span><span class="p">,</span> <span class="n">user</span><span class="o">=</span><span class="s1">'guido'</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s1">'234$'</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>Also see <a class="footnote-reference brackets" href="#id61" id="id49">[13]</a> regarding planned future additions to this list.</p>
|
||
</aside>
|
||
<aside class="footnote brackets" id="id50" role="doc-footnote">
|
||
<dt class="label" id="id50">[<a href="#id2">2</a>]</dt>
|
||
<dd>Module implementors should prefer <code class="docutils literal notranslate"><span class="pre">numeric</span></code>, <code class="docutils literal notranslate"><span class="pre">named</span></code> or
|
||
<code class="docutils literal notranslate"><span class="pre">pyformat</span></code> over the other formats because these offer more
|
||
clarity and flexibility.</aside>
|
||
<aside class="footnote brackets" id="id51" role="doc-footnote">
|
||
<dt class="label" id="id51">[3]<em> (<a href='#id10'>1</a>, <a href='#id16'>2</a>, <a href='#id30'>3</a>) </em></dt>
|
||
<dd>If the database does not support the functionality required by
|
||
the method, the interface should throw an exception in case the
|
||
method is used.<p>The preferred approach is to not implement the method and thus have
|
||
Python generate an <code class="docutils literal notranslate"><span class="pre">AttributeError</span></code> in case the method is
|
||
requested. This allows the programmer to check for database
|
||
capabilities using the standard <code class="docutils literal notranslate"><span class="pre">hasattr()</span></code> function.</p>
|
||
<p>For some dynamically configured interfaces it may not be
|
||
appropriate to require dynamically making the method
|
||
available. These interfaces should then raise a
|
||
<code class="docutils literal notranslate"><span class="pre">NotSupportedError</span></code> to indicate the non-ability to perform the
|
||
roll back when the method is invoked.</p>
|
||
</aside>
|
||
<aside class="footnote brackets" id="id52" role="doc-footnote">
|
||
<dt class="label" id="id52">[<a href="#id11">4</a>]</dt>
|
||
<dd>A database interface may choose to support named cursors by
|
||
allowing a string argument to the method. This feature is not part
|
||
of the specification, since it complicates semantics of the
|
||
<a class="reference internal" href="#id24">.fetch*()</a> methods.</aside>
|
||
<aside class="footnote brackets" id="id53" role="doc-footnote">
|
||
<dt class="label" id="id53">[<a href="#id22">5</a>]</dt>
|
||
<dd>The module will use the <code class="docutils literal notranslate"><span class="pre">__getitem__</span></code> method of the
|
||
parameters object to map either positions (integers) or names
|
||
(strings) to parameter values. This allows for both sequences and
|
||
mappings to be used as input.<p>The term <em>bound</em> refers to the process of binding an input value
|
||
to a database execution buffer. In practical terms, this means
|
||
that the input value is directly used as a value in the operation.
|
||
The client should not be required to “escape” the value so that it
|
||
can be used — the value should be equal to the actual database
|
||
value.</p>
|
||
</aside>
|
||
<aside class="footnote brackets" id="id54" role="doc-footnote">
|
||
<dt class="label" id="id54">[<a href="#id26">6</a>]</dt>
|
||
<dd>Note that the interface may implement row fetching using arrays
|
||
and other optimizations. It is not guaranteed that a call to this
|
||
method will only move the associated cursor forward by one row.</aside>
|
||
<aside class="footnote brackets" id="id55" role="doc-footnote">
|
||
<dt class="label" id="id55">[<a href="#id14">7</a>]</dt>
|
||
<dd>The <code class="docutils literal notranslate"><span class="pre">rowcount</span></code> attribute may be coded in a way that updates
|
||
its value dynamically. This can be useful for databases that
|
||
return usable <code class="docutils literal notranslate"><span class="pre">rowcount</span></code> values only after the first call to a
|
||
<a class="reference internal" href="#id24">.fetch*()</a> method.</aside>
|
||
<aside class="footnote brackets" id="id56" role="doc-footnote">
|
||
<dt class="label" id="id56">[<a href="#id38">8</a>]</dt>
|
||
<dd>Implementation Note: Python C extensions will have to implement
|
||
the <code class="docutils literal notranslate"><span class="pre">tp_iter</span></code> slot on the cursor object instead of the
|
||
<code class="docutils literal notranslate"><span class="pre">.__iter__()</span></code> method.</aside>
|
||
<aside class="footnote brackets" id="id57" role="doc-footnote">
|
||
<dt class="label" id="id57">[<a href="#id13">9</a>]</dt>
|
||
<dd>The term <em>number of affected rows</em> generally refers to the
|
||
number of rows deleted, updated or inserted by the last statement
|
||
run on the database cursor. Most databases will return the total
|
||
number of rows that were found by the corresponding <code class="docutils literal notranslate"><span class="pre">WHERE</span></code>
|
||
clause of the statement. Some databases use a different
|
||
interpretation for <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>s and only return the number of rows
|
||
that were changed by the <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, even though the <code class="docutils literal notranslate"><span class="pre">WHERE</span></code>
|
||
clause of the statement may have found more matching rows.
|
||
Database module authors should try to implement the more common
|
||
interpretation of returning the total number of rows found by the
|
||
<code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause, or clearly document a different interpretation
|
||
of the <code class="docutils literal notranslate"><span class="pre">.rowcount</span></code> attribute.</aside>
|
||
<aside class="footnote brackets" id="id58" role="doc-footnote">
|
||
<dt class="label" id="id58">[10]<em> (<a href='#id3'>1</a>, <a href='#id5'>2</a>, <a href='#id6'>3</a>, <a href='#id33'>4</a>) </em></dt>
|
||
<dd>In Python 2 and earlier versions of this PEP, <code class="docutils literal notranslate"><span class="pre">StandardError</span></code>
|
||
was used as the base class for all DB-API exceptions. Since
|
||
<code class="docutils literal notranslate"><span class="pre">StandardError</span></code> was removed in Python 3, database modules
|
||
targeting Python 3 should use <code class="docutils literal notranslate"><span class="pre">Exception</span></code> as base class instead.
|
||
The PEP was updated to use <code class="docutils literal notranslate"><span class="pre">Exception</span></code> throughout the text, to
|
||
avoid confusion. The change should not affect existing modules or
|
||
uses of those modules, since all DB-API error exception classes are
|
||
still rooted at the <code class="docutils literal notranslate"><span class="pre">Error</span></code> or <code class="docutils literal notranslate"><span class="pre">Warning</span></code> classes.</aside>
|
||
<aside class="footnote brackets" id="id59" role="doc-footnote">
|
||
<dt class="label" id="id59">[11]<em> (<a href='#id4'>1</a>, <a href='#id7'>2</a>) </em></dt>
|
||
<dd>In a future revision of the DB-API, the base class for
|
||
<code class="docutils literal notranslate"><span class="pre">Warning</span></code> will likely change to the builtin <code class="docutils literal notranslate"><span class="pre">Warning</span></code> class. At
|
||
the time of writing of the DB-API 2.0 in 1999, the warning framework
|
||
in Python did not yet exist.</aside>
|
||
<aside class="footnote brackets" id="id60" role="doc-footnote">
|
||
<dt class="label" id="id60">[<a href="#id39">12</a>]</dt>
|
||
<dd>Many database modules implementing the autocommit attribute will
|
||
automatically commit any pending transaction and then enter
|
||
autocommit mode. It is generally recommended to explicitly
|
||
<a class="reference internal" href="#id8">.commit()</a> or <a class="reference internal" href="#id9">.rollback()</a> transactions prior to changing the
|
||
autocommit setting, since this is portable across database modules.</aside>
|
||
<aside class="footnote brackets" id="id61" role="doc-footnote">
|
||
<dt class="label" id="id61">[13]<em> (<a href='#id40'>1</a>, <a href='#id49'>2</a>) </em></dt>
|
||
<dd>In a future revision of the DB-API, we are going to introduce a
|
||
new method <code class="docutils literal notranslate"><span class="pre">.setautocommit(value)</span></code>, which will allow setting the
|
||
autocommit mode, and make <code class="docutils literal notranslate"><span class="pre">.autocommit</span></code> a read-only attribute.
|
||
Additionally, we are considering to add a new standard keyword
|
||
parameter <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> to the Connection constructor. Modules
|
||
authors are encouraged to add these changes in preparation for this
|
||
change.</aside>
|
||
</aside>
|
||
</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 2.0 from the original HTML format into the PEP
|
||
format in 2001.</p>
|
||
<p>Many thanks to James Henstridge for leading the discussion which led to
|
||
the standardization of the two-phase commit API extensions in 2008.</p>
|
||
<p>Many thanks to Daniele Varrazzo for converting the specification from
|
||
text PEP format to ReST PEP format, which allows linking to various
|
||
parts in 2012.</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-0249.rst">https://github.com/python/peps/blob/main/peps/pep-0249.rst</a></p>
|
||
<p>Last modified: <a class="reference external" href="https://github.com/python/peps/commits/main/peps/pep-0249.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><ul>
|
||
<li><a class="reference internal" href="#constructors">Constructors</a></li>
|
||
<li><a class="reference internal" href="#globals">Globals</a></li>
|
||
<li><a class="reference internal" href="#exceptions">Exceptions</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#connection-objects">Connection Objects</a><ul>
|
||
<li><a class="reference internal" href="#connection-methods">Connection methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#cursor-objects">Cursor Objects</a><ul>
|
||
<li><a class="reference internal" href="#cursor-attributes">Cursor attributes</a></li>
|
||
<li><a class="reference internal" href="#cursor-methods">Cursor methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#type-objects-and-constructors">Type Objects and Constructors</a></li>
|
||
<li><a class="reference internal" href="#implementation-hints-for-module-authors">Implementation Hints for Module Authors</a></li>
|
||
<li><a class="reference internal" href="#optional-db-api-extensions">Optional DB API Extensions</a></li>
|
||
<li><a class="reference internal" href="#optional-error-handling-extensions">Optional Error Handling Extensions</a></li>
|
||
<li><a class="reference internal" href="#optional-two-phase-commit-extensions">Optional Two-Phase Commit Extensions</a><ul>
|
||
<li><a class="reference internal" href="#tpc-transaction-ids">TPC Transaction IDs</a></li>
|
||
<li><a class="reference internal" href="#tpc-connection-methods">TPC Connection Methods</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#frequently-asked-questions">Frequently Asked Questions</a></li>
|
||
<li><a class="reference internal" href="#major-changes-from-version-1-0-to-version-2-0">Major Changes from Version 1.0 to Version 2.0</a></li>
|
||
<li><a class="reference internal" href="#open-issues">Open Issues</a></li>
|
||
<li><a class="reference internal" href="#footnotes">Footnotes</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-0249.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> |