1 =======================================
2 psycopg 2 extensions to the DBAPI 2.0
3 =======================================
5 This document is a short summary of the extensions built in psycopg 2.0.x over
6 the standard `Python Database API Specification 2.0`__, usually called simply
7 DBAPI-2.0 or even PEP-249. Before reading on this document please make sure
8 you already know how to program in Python using a DBAPI-2.0 compliant driver:
9 basic concepts like opening a connection, executing queries and commiting or
10 rolling back a transaction will not be explained but just used.
12 .. __: http://www.python.org/peps/pep-0249.html
14 Many objects and extension functions are defined in the `psycopg2.extensions`
18 Connection and cursor factories
19 ===============================
21 psycopg 2 exposes two new-style classes that can be sub-classed and expanded to
22 adapt them to the needs of the programmer: `cursor` and `connection`. The
23 `connection` class is usually sub-classed only to provide an easy way to create
24 customized cursors but other uses are possible. `cursor` is much more
25 interesting, because it is the class where query building, execution and result
26 type-casting into Python variables happens.
28 An example of cursor subclass performing logging is::
31 import psycopg2.extensions
34 class LoggingCursor(psycopg2.extensions.cursor):
35 def execute(self, sql, args=None):
36 logger = logging.getLogger('sql_debug')
37 logger.info(self.mogrify(sql, args))
40 psycopg2.extensions.cursor.execute(self, sql, args)
41 except Exception, exc:
42 logger.error("%s: %s" % (exc.__class__.__name__, exc))
45 conn = psycopg2.connect(DSN)
46 curs = conn.cursor(cursor_factory=LoggingCursor)
47 curs.execute("INSERT INTO mytable VALUES (%s, %s, %s);",
58 Setting transaction isolation levels
59 ====================================
61 psycopg2 connection objects hold informations about the PostgreSQL `transaction
62 isolation level`_. The current transaction level can be read from the
63 `.isolation_level` attribute. The default isolation level is ``READ
64 COMMITTED``. A different isolation level con be set through the
65 `.set_isolation_level()` method. The level can be set to one of the following
66 constants, defined in `psycopg2.extensions`:
68 `ISOLATION_LEVEL_AUTOCOMMIT`
69 No transaction is started when command are issued and no
70 `.commit()`/`.rollback()` is required. Some PostgreSQL command such as
71 ``CREATE DATABASE`` can't run into a transaction: to run such command use
72 `.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)`.
74 `ISOLATION_LEVEL_READ_COMMITTED`
75 This is the default value. A new transaction is started at the first
76 `.execute()` command on a cursor and at each new `.execute()` after a
77 `.commit()` or a `.rollback()`. The transaction runs in the PostgreSQL
78 ``READ COMMITTED`` isolation level.
80 `ISOLATION_LEVEL_SERIALIZABLE`
81 Transactions are run at a ``SERIALIZABLE`` isolation level.
84 .. _transaction isolation level:
85 http://www.postgresql.org/docs/8.1/static/transaction-iso.html
88 Adaptation of Python values to SQL types
89 ========================================
91 psycopg2 casts Python variables to SQL literals by type. Standard Python types
92 are already adapted to the proper SQL literal.
94 Example: the Python function::
96 curs.execute("""INSERT INTO atable (anint, adate, astring)
97 VALUES (%s, %s, %s)""",
98 (10, datetime.date(2005, 11, 18), "O'Reilly"))
100 is converted into the SQL command::
102 INSERT INTO atable (anint, adate, astring)
103 VALUES (10, '2005-11-18', 'O''Reilly');
105 Named arguments are supported too with ``%(name)s`` placeholders. Notice that:
107 - The Python string operator ``%`` is not used: the `.execute()` function
108 accepts the values tuple or dictionary as second parameter.
110 - The variables placeholder must always be a ``%s``, even if a different
111 placeholder (such as a ``%d`` for an integer) may look more appropriate.
113 - For positional variables binding, the second argument must always be a
114 tuple, even if it contains a single variable.
116 - Only variable values should be bound via this method: it shouldn't be used
117 to set table or field names. For these elements, ordinary string formatting
118 should be used before running `.execute()`.
124 Any Python class or type can be adapted to an SQL string. Adaptation mechanism
125 is similar to the Object Adaptation proposed in the `PEP-246`_ and is exposed
126 by the `adapt()` function.
128 psycopg2 `.execute()` method adapts its ``vars`` arguments to the `ISQLQuote`
129 protocol. Objects that conform to this protocol expose a ``getquoted()`` method
130 returning the SQL representation of the object as a string.
132 The easiest way to adapt an object to an SQL string is to register an adapter
133 function via the `register_adapter()` function. The adapter function must take
134 the value to be adapted as argument and return a conform object. A convenient
135 object is the `AsIs` wrapper, whose ``getquoted()`` result is simply the
136 ``str()``\ ingification of the wrapped object.
138 Example: mapping of a ``Point`` class into the ``point`` PostgreSQL geometric
141 from psycopg2.extensions import adapt, register_adapter, AsIs
144 def __init__(self, x=0.0, y=0.0):
148 def adapt_point(point):
149 return AsIs("'(%s,%s)'" % (adapt(point.x), adapt(point.y)))
151 register_adapter(Point, adapt_point)
153 curs.execute("INSERT INTO atable (apoint) VALUES (%s)",
154 (Point(1.23, 4.56),))
156 The above function call results in the SQL command::
158 INSERT INTO atable (apoint) VALUES ((1.23, 4.56));
161 .. _PEP-246: http://www.python.org/peps/pep-0246.html
164 Type casting of SQL types into Python values
165 ============================================
167 PostgreSQL objects read from the database can be adapted to Python objects
168 through an user-defined adapting function. An adapter function takes two
169 argments: the object string representation as returned by PostgreSQL and the
170 cursor currently being read, and should return a new Python object. For
171 example, the following function parses a PostgreSQL ``point`` into the
172 previously defined ``Point`` class::
174 def cast_point(value, curs):
175 if value is not None:
176 # Convert from (f1, f2) syntax using a regular expression.
177 m = re.match("\((.*),(.*)\)", value)
179 return Point(float(m.group(1)), float(m.group(2)))
181 To create a mapping from the PostgreSQL type (either standard or user-defined),
182 its ``oid`` must be known. It can be retrieved either by the second column of
183 the cursor description::
185 curs.execute("SELECT NULL::point")
186 point_oid = curs.description[0][1] # usually returns 600
188 or by querying the system catalogs for the type name and namespace (the
189 namespace for system objects is ``pg_catalog``)::
193 FROM pg_type JOIN pg_namespace
194 ON typnamespace = pg_namespace.oid
195 WHERE typname = %(typename)s
196 AND nspname = %(namespace)s""",
197 {'typename': 'point', 'namespace': 'pg_catalog'})
199 point_oid = curs.fetchone()[0]
201 After you know the object ``oid``, you must can and register the new type::
203 POINT = psycopg2.extensions.new_type((point_oid,), "POINT", cast_point)
204 psycopg2.extensions.register_type(POINT)
206 The `new_type()` function binds the object oids (more than one can be
207 specified) to the adapter function. `register_type()` completes the spell.
208 Conversion is automatically performed when a column whose type is a registered
211 curs.execute("SELECT '(10.2,20.3)'::point")
212 point = curs.fetchone()[0]
213 print type(point), point.x, point.y
214 # Prints: "<class '__main__.Point'> 10.2 20.3"
217 Working with times and dates
218 ============================
225 Using COPY TO and COPY FROM
226 ===========================
228 psycopg2 `cursor` object provides an interface to the efficient `PostgreSQL
229 COPY command`__ to move data from files to tables and back.
231 The `.copy_to(file, table)` method writes the content of the table
232 named ``table`` *to* the file-like object ``file``. ``file`` must have a
235 The `.copy_from(file, table)` reads data *from* the file-like object
236 ``file`` appending them to the table named ``table``. ``file`` must have both
237 ``read()`` and ``readline()`` method.
239 Both methods accept two optional arguments: ``sep`` (defaulting to a tab) is
240 the columns separator and ``null`` (defaulting to ``\N``) represents ``NULL``
243 .. __: http://www.postgresql.org/docs/8.1/static/sql-copy.html
246 PostgreSQL status message and executed query
247 ============================================
249 `cursor` objects have two special fields related to the last executed query:
251 - `.query` is the textual representation (str or unicode, depending on what
252 was passed to `.execute()` as first argument) of the query *after* argument
253 binding and mogrification has been applied. To put it another way, `.query`
254 is the *exact* query that was sent to the PostgreSQL backend.
256 - `.statusmessage` is the status message that the backend sent upon query
257 execution. It usually contains the basic type of the query (SELECT,
258 INSERT, UPDATE, ...) and some additional information like the number of
259 rows updated and so on. Refer to the PostgreSQL manual for more