1
<?xml version="1.0" encoding="utf-8" ?>
2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
5
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
6
<meta name="generator" content="Docutils 0.3.9: http://docutils.sourceforge.net/" />
7
<title>psycopg 2 extensions to the DBAPI 2.0</title>
8
<link rel="stylesheet" href="default.css" type="text/css" />
11
<div class="document" id="psycopg-2-extensions-to-the-dbapi-2-0">
12
<h1 class="title">psycopg 2 extensions to the DBAPI 2.0</h1>
13
<p>This document is a short summary of the extensions built in psycopg 2.0.x over
14
the standard <a class="reference" href="http://www.python.org/peps/pep-0249.html">Python Database API Specification 2.0</a>, usually called simply
15
DBAPI-2.0 or even PEP-249. Before reading on this document please make sure
16
you already know how to program in Python using a DBAPI-2.0 compliant driver:
17
basic concepts like opening a connection, executing queries and commiting or
18
rolling back a transaction will not be explained but just used.</p>
19
<p>Many objects and extension functions are defined in the <a class="reference" href="api/public/psycopg2.extensions-module.html"><tt class="docutils literal"><span class="pre">psycopg2.extensions</span></tt></a>
21
<div class="section" id="connection-and-cursor-factories">
22
<h1><a name="connection-and-cursor-factories">Connection and cursor factories</a></h1>
23
<p>psycopg 2 exposes two new-style classes that can be sub-classed and expanded to
24
adapt them to the needs of the programmer: <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html"><tt class="docutils literal"><span class="pre">cursor</span></tt></a> and <a class="reference" href="api/private/psycopg2._psycopg.connection-class.html"><tt class="docutils literal"><span class="pre">connection</span></tt></a>. The
25
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html"><tt class="docutils literal"><span class="pre">connection</span></tt></a> class is usually sub-classed only to provide a . <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html"><tt class="docutils literal"><span class="pre">cursor</span></tt></a> is much
26
more interesting, because it is the class where query building, execution and
27
result type-casting into Python variables happens.</p>
28
<div class="section" id="row-factories">
29
<h2><a name="row-factories">Row factories</a></h2>
31
<div class="section" id="tzinfo-factories">
32
<h2><a name="tzinfo-factories">tzinfo factories</a></h2>
35
<div class="section" id="setting-transaction-isolation-levels">
36
<h1><a name="setting-transaction-isolation-levels">Setting transaction isolation levels</a></h1>
37
<p>psycopg2 connection objects hold informations about the PostgreSQL <a class="reference" href="http://www.postgresql.org/docs/8.1/static/transaction-iso.html">transaction
38
isolation level</a>. The current transaction level can be read from the
39
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#isolation_level"><tt class="docutils literal"><span class="pre">.isolation_level</span></tt></a> attribute. The default isolation level is <tt class="docutils literal"><span class="pre">READ</span>
40
<span class="pre">COMMITTED</span></tt>. A different isolation level con be set through the
41
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#set_isolation_level"><tt class="docutils literal"><span class="pre">.set_isolation_level()</span></tt></a> method. The level can be set to one of the following
42
constants, defined in <a class="reference" href="api/public/psycopg2.extensions-module.html"><tt class="docutils literal"><span class="pre">psycopg2.extensions</span></tt></a>:</p>
44
<dt><a class="reference" href="api/public/psycopg2.extensions-module.html#ISOLATION_LEVEL_AUTOCOMMIT"><tt class="docutils literal"><span class="pre">ISOLATION_LEVEL_AUTOCOMMIT</span></tt></a></dt>
45
<dd>No transaction is started when command are issued and no
46
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#commit"><tt class="docutils literal"><span class="pre">.commit()</span></tt></a>/<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#rollback"><tt class="docutils literal"><span class="pre">.rollback()</span></tt></a> is required. Some PostgreSQL command such as
47
<tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">DATABASE</span></tt> can't run into a transaction: to run such command use
48
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#set_isolation_level"><tt class="docutils literal"><span class="pre">.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)</span></tt></a>.</dd>
49
<dt><a href="#id2" name="id3"><span class="problematic" id="id3">`ISOLATION_LEVEL_READ_COMMITTED`</span></a></dt>
50
<dd><div class="first system-message" id="id2">
51
<p class="system-message-title">System Message: <a name="id2">ERROR/3</a> (<tt class="docutils">../doc/extensions.rst</tt>, line 54); <em><a href="#id3">backlink</a></em></p>
52
Can't find 'ISOLATION_LEVEL_READ_COMMITTED' in any provided module.</div>
53
<p class="last">This is the default value. A new transaction is started at the first
54
<a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a> command on a cursor and at each new <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a> after a
55
<a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#commit"><tt class="docutils literal"><span class="pre">.commit()</span></tt></a> or a <a class="reference" href="api/private/psycopg2._psycopg.connection-class.html#rollback"><tt class="docutils literal"><span class="pre">.rollback()</span></tt></a>. The transaction runs in the PostgreSQL
56
<tt class="docutils literal"><span class="pre">READ</span> <span class="pre">COMMITTED</span></tt> isolation level.</p>
58
<dt><a class="reference" href="api/public/psycopg2.extensions-module.html#ISOLATION_LEVEL_SERIALIZABLE"><tt class="docutils literal"><span class="pre">ISOLATION_LEVEL_SERIALIZABLE</span></tt></a></dt>
59
<dd>Transactions are run at a <tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt> isolation level.</dd>
62
<div class="section" id="adaptation-of-python-values-to-sql-types">
63
<h1><a name="adaptation-of-python-values-to-sql-types">Adaptation of Python values to SQL types</a></h1>
64
<p>psycopg2 casts Python variables to SQL literals by type. Standard Python types
65
are already adapted to the proper SQL literal.</p>
66
<p>Example: the Python function:</p>
67
<pre class="literal-block">
68
curs.execute("""INSERT INTO atable (anint, adate, astring)
69
VALUES (%s, %s, %s)""",
70
(10, datetime.date(2005, 11, 18), "O'Reilly"))
72
<p>is converted into the SQL command:</p>
73
<pre class="literal-block">
74
INSERT INTO atable (anint, adate, astring)
75
VALUES (10, '2005-11-18', 'O''Reilly');
77
<p>Named arguments are supported too with <tt class="docutils literal"><span class="pre">%(name)s</span></tt> placeholders. Notice that:</p>
80
<li>The Python string operator <tt class="docutils literal"><span class="pre">%</span></tt> is not used: the <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a> function
81
accepts the values tuple or dictionary as second parameter.</li>
82
<li>The variables placeholder must always be a <tt class="docutils literal"><span class="pre">%s</span></tt>, even if a different
83
placeholder (such as a <tt class="docutils literal"><span class="pre">%d</span></tt> for an integer) may look more appropriate.</li>
84
<li>For positional variables binding, the second argument must always be a
85
tuple, even if it contains a single variable.</li>
86
<li>Only variable values should be bound via this method: it shouldn't be used
87
to set table or field names. For these elements, ordinary string formatting
88
should be used before running <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a>.</li>
91
<div class="section" id="adapting-new-types">
92
<h2><a name="adapting-new-types">Adapting new types</a></h2>
93
<p>Any Python class or type can be adapted to an SQL string. Adaptation mechanism
94
is similar to the Object Adaptation proposed in the <a class="reference" href="http://www.python.org/peps/pep-0246.html">PEP-246</a> and is exposed
95
by the <a class="reference" href="api/private/psycopg2._psycopg-module.html#adapt"><tt class="docutils literal"><span class="pre">adapt()</span></tt></a> function.</p>
96
<p>psycopg2 <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a> method adapts its <tt class="docutils literal"><span class="pre">vars</span></tt> arguments to the <a class="reference" href="api/private/psycopg2._psycopg.ISQLQuote-class.html"><tt class="docutils literal"><span class="pre">ISQLQuote</span></tt></a>
97
protocol. Objects that conform to this protocol expose a <tt class="docutils literal"><span class="pre">getquoted()</span></tt> method
98
returning the SQL representation of the object as a string.</p>
99
<p>The easiest way to adapt an object to an SQL string is to register an adapter
100
function via the <a class="reference" href="api/public/psycopg2.extensions-module.html#register_adapter"><tt class="docutils literal"><span class="pre">register_adapter()</span></tt></a> function. The adapter function must take
101
the value to be adapted as argument and return a conform object. A convenient
102
object is the <a class="reference" href="api/private/psycopg2._psycopg-module.html#AsIs"><tt class="docutils literal"><span class="pre">AsIs</span></tt></a> wrapper, whose <tt class="docutils literal"><span class="pre">getquoted()</span></tt> result is simply the
103
<tt class="docutils literal"><span class="pre">str()</span></tt>ingification of the wrapped object.</p>
104
<p>Example: mapping of a <tt class="docutils literal"><span class="pre">Point</span></tt> class into the <tt class="docutils literal"><span class="pre">point</span></tt> PostgreSQL geometric
106
<pre class="literal-block">
107
from psycopg2.extensions import adapt, register_adapter, AsIs
110
def __init__(self, x=0.0, y=0.0):
114
def adapt_point(point):
115
return AsIs("'(%s,%s)'" % (adapt(point.x), adapt(point.y)))
117
register_adapter(Point, adapt_point)
119
curs.execute("INSERT INTO atable (apoint) VALUES (%s)",
120
(Point(1.23, 4.56),))
122
<p>The above function call results in the SQL command:</p>
123
<pre class="literal-block">
124
INSERT INTO atable (apoint) VALUES ((1.23, 4.56));
128
<div class="section" id="type-casting-of-sql-types-into-python-values">
129
<h1><a name="type-casting-of-sql-types-into-python-values">Type casting of SQL types into Python values</a></h1>
130
<p>PostgreSQL objects read from the database can be adapted to Python objects
131
through an user-defined adapting function. An adapter function takes two
132
argments: the object string representation as returned by PostgreSQL and the
133
cursor currently being read, and should return a new Python object. For
134
example, the following function parses a PostgreSQL <tt class="docutils literal"><span class="pre">point</span></tt> into the
135
previously defined <tt class="docutils literal"><span class="pre">Point</span></tt> class:</p>
136
<pre class="literal-block">
137
def cast_point(value, curs):
138
if value is not None:
139
# Convert from (f1, f2) syntax using a regular expression.
140
m = re.match("\((.*),(.*)\)", value)
142
return Point(float(m.group(1)), float(m.group(2)))
144
<p>To create a mapping from the PostgreSQL type (either standard or user-defined),
145
its <tt class="docutils literal"><span class="pre">oid</span></tt> must be known. It can be retrieved either by the second column of
146
the cursor description:</p>
147
<pre class="literal-block">
148
curs.execute("SELECT NULL::point")
149
point_oid = curs.description[0][1] # usually returns 600
151
<p>or by querying the system catalogs for the type name and namespace (the
152
namespace for system objects is <tt class="docutils literal"><span class="pre">pg_catalog</span></tt>):</p>
153
<pre class="literal-block">
154
curs.execute("""
156
FROM pg_type JOIN pg_namespace
157
ON typnamespace = pg_namespace.oid
158
WHERE typname = %(typename)s
159
AND nspname = %(namespace)s""",
160
{'typename': 'point', 'namespace': 'pg_catalog'})
162
point_oid = curs.fetchone()[0]
164
<p>After you know the object <tt class="docutils literal"><span class="pre">oid</span></tt>, you must can and register the new type:</p>
165
<pre class="literal-block">
166
POINT = psycopg2.extensions.new_type((point_oid,), "POINT", cast_point)
167
psycopg2.extensions.register_type(POINT)
169
<p>The <a class="reference" href="api/private/psycopg2._psycopg-module.html#new_type"><tt class="docutils literal"><span class="pre">new_type()</span></tt></a> function binds the object oids (more than one can be
170
specified) to the adapter function. <a class="reference" href="api/private/psycopg2._psycopg-module.html#register_type"><tt class="docutils literal"><span class="pre">register_type()</span></tt></a> completes the spell.
171
Conversion is automatically performed when a column whose type is a registered
172
<tt class="docutils literal"><span class="pre">oid</span></tt> is read:</p>
173
<pre class="literal-block">
174
curs.execute("SELECT '(10.2,20.3)'::point")
175
point = curs.fetchone()[0]
176
print type(point), point.x, point.y
177
# Prints: "<class '__main__.Point'> 10.2 20.3"
180
<div class="section" id="working-with-times-and-dates">
181
<h1><a name="working-with-times-and-dates">Working with times and dates</a></h1>
183
<div class="section" id="receiving-notifys">
184
<h1><a name="receiving-notifys">Receiving NOTIFYs</a></h1>
186
<div class="section" id="using-copy-to-and-copy-from">
187
<h1><a name="using-copy-to-and-copy-from">Using COPY TO and COPY FROM</a></h1>
188
<p>psycopg2 <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html"><tt class="docutils literal"><span class="pre">cursor</span></tt></a> object provides an interface to the efficient <a class="reference" href="http://www.postgresql.org/docs/8.1/static/sql-copy.html">PostgreSQL
189
COPY command</a> to move data from files to tables and back.</p>
190
<p>The <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#copy_to"><tt class="docutils literal"><span class="pre">.copy_to(file,</span> <span class="pre">table)</span></tt></a> method writes the content of the table
191
named <tt class="docutils literal"><span class="pre">table</span></tt> <em>to</em> the file-like object <tt class="docutils literal"><span class="pre">file</span></tt>. <tt class="docutils literal"><span class="pre">file</span></tt> must have a
192
<tt class="docutils literal"><span class="pre">write()</span></tt> method.</p>
193
<p>The <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#copy_from"><tt class="docutils literal"><span class="pre">.copy_from(file,</span> <span class="pre">table)</span></tt></a> reads data <em>from</em> the file-like object
194
<tt class="docutils literal"><span class="pre">file</span></tt> appending them to the table named <tt class="docutils literal"><span class="pre">table</span></tt>. <tt class="docutils literal"><span class="pre">file</span></tt> must have both
195
<tt class="docutils literal"><span class="pre">read()</span></tt> and <tt class="docutils literal"><span class="pre">readline()</span></tt> method.</p>
196
<p>Both methods accept two optional arguments: <tt class="docutils literal"><span class="pre">sep</span></tt> (defaulting to a tab) is
197
the columns separator and <tt class="docutils literal"><span class="pre">null</span></tt> (defaulting to <tt class="docutils literal"><span class="pre">\N</span></tt>) represents <tt class="docutils literal"><span class="pre">NULL</span></tt>
198
values in the file.</p>
200
<div class="section" id="postgresql-status-message-and-executed-query">
201
<h1><a name="postgresql-status-message-and-executed-query">PostgreSQL status message and executed query</a></h1>
202
<p><a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html"><tt class="docutils literal"><span class="pre">cursor</span></tt></a> objects have two special fields related to the last executed query:</p>
205
<li><a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#query"><tt class="docutils literal"><span class="pre">.query</span></tt></a> is the textual representation (str or unicode, depending on what
206
was passed to <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#execute"><tt class="docutils literal"><span class="pre">.execute()</span></tt></a> as first argument) of the query <em>after</em> argument
207
binding and mogrification has been applied. To put it another way, <a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#query"><tt class="docutils literal"><span class="pre">.query</span></tt></a>
208
is the <em>exact</em> query that was sent to the PostgreSQL backend.</li>
209
<li><a class="reference" href="api/private/psycopg2._psycopg.cursor-class.html#statusmessage"><tt class="docutils literal"><span class="pre">.statusmessage</span></tt></a> is the status message that the backend sent upon query
210
execution. It usually contains the basic type of the query (SELECT,
211
INSERT, UPDATE, ...) and some additional information like the number of
212
rows updated and so on. Refer to the PostgreSQL manual for more
added
removed
doc/extensions.html
