--- /dev/null
+=======================================
+ psycopg 2 extensions to the DBAPI 2.0
+=======================================
+
+This document is a short summary of the extensions built in psycopg 2.0.x over
+the standard `Python Database API Specification 2.0`__, usually called simply
+DBAPI-2.0 or even PEP-249. Before reading on this document please make sure
+you already know how to program in Python using a DBAPI-2.0 compliant driver:
+basic concepts like opening a connection, executing queries and commiting or
+rolling back a transaction will not be explained but just used.
+
+.. __: http://www.python.org/peps/pep-0249.html
+
+Many objects and extension functions are defined in the `psycopg2.extensions`
+module.
+
+
+Connection and cursor factories
+===============================
+
+psycopg 2 exposes two new-style classes that can be sub-classed and expanded to
+adapt them to the needs of the programmer: `cursor` and `connection`. The
+`connection` class is usually sub-classed only to provide an easy way to create
+customized cursors but other uses are possible. `cursor` is much more
+interesting, because it is the class where query building, execution and result
+type-casting into Python variables happens.
+
+An example of cursor subclass performing logging is::
+
+ import psycopg2
+ import psycopg2.extensions
+ import logging
+
+ class LoggingCursor(psycopg2.extensions.cursor):
+ def execute(self, sql, args=None):
+ logger = logging.getLogger('sql_debug')
+ logger.info(self.mogrify(sql, args))
+
+ try:
+ psycopg2.extensions.cursor.execute(self, sql, args)
+ except Exception, exc:
+ logger.error("%s: %s" % (exc.__class__.__name__, exc))
+ raise
+
+ conn = psycopg2.connect(DSN)
+ curs = conn.cursor(cursor_factory=LoggingCursor)
+ curs.execute("INSERT INTO mytable VALUES (%s, %s, %s);",
+ (10, 20, 30))
+
+
+Row factories
+-------------
+
+tzinfo factories
+----------------
+
+
+Setting transaction isolation levels
+====================================
+
+psycopg2 connection objects hold informations about the PostgreSQL `transaction
+isolation level`_. The current transaction level can be read from the
+`.isolation_level` attribute. The default isolation level is ``READ
+COMMITTED``. A different isolation level con be set through the
+`.set_isolation_level()` method. The level can be set to one of the following
+constants, defined in `psycopg2.extensions`:
+
+`ISOLATION_LEVEL_AUTOCOMMIT`
+ No transaction is started when command are issued and no
+ `.commit()`/`.rollback()` is required. Some PostgreSQL command such as
+ ``CREATE DATABASE`` can't run into a transaction: to run such command use
+ `.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)`.
+
+`ISOLATION_LEVEL_READ_COMMITTED`
+ This is the default value. A new transaction is started at the first
+ `.execute()` command on a cursor and at each new `.execute()` after a
+ `.commit()` or a `.rollback()`. The transaction runs in the PostgreSQL
+ ``READ COMMITTED`` isolation level.
+
+`ISOLATION_LEVEL_SERIALIZABLE`
+ Transactions are run at a ``SERIALIZABLE`` isolation level.
+
+
+.. _transaction isolation level:
+ http://www.postgresql.org/docs/8.1/static/transaction-iso.html
+
+
+Adaptation of Python values to SQL types
+========================================
+
+psycopg2 casts Python variables to SQL literals by type. Standard Python types
+are already adapted to the proper SQL literal.
+
+Example: the Python function::
+
+ curs.execute("""INSERT INTO atable (anint, adate, astring)
+ VALUES (%s, %s, %s)""",
+ (10, datetime.date(2005, 11, 18), "O'Reilly"))
+
+is converted into the SQL command::
+
+ INSERT INTO atable (anint, adate, astring)
+ VALUES (10, '2005-11-18', 'O''Reilly');
+
+Named arguments are supported too with ``%(name)s`` placeholders. Notice that:
+
+ - The Python string operator ``%`` is not used: the `.execute()` function
+ accepts the values tuple or dictionary as second parameter.
+
+ - The variables placeholder must always be a ``%s``, even if a different
+ placeholder (such as a ``%d`` for an integer) may look more appropriate.
+
+ - For positional variables binding, the second argument must always be a
+ tuple, even if it contains a single variable.
+
+ - Only variable values should be bound via this method: it shouldn't be used
+ to set table or field names. For these elements, ordinary string formatting
+ should be used before running `.execute()`.
+
+
+Adapting new types
+------------------
+
+Any Python class or type can be adapted to an SQL string. Adaptation mechanism
+is similar to the Object Adaptation proposed in the `PEP-246`_ and is exposed
+by the `adapt()` function.
+
+psycopg2 `.execute()` method adapts its ``vars`` arguments to the `ISQLQuote`
+protocol. Objects that conform to this protocol expose a ``getquoted()`` method
+returning the SQL representation of the object as a string.
+
+The easiest way to adapt an object to an SQL string is to register an adapter
+function via the `register_adapter()` function. The adapter function must take
+the value to be adapted as argument and return a conform object. A convenient
+object is the `AsIs` wrapper, whose ``getquoted()`` result is simply the
+``str()``\ ingification of the wrapped object.
+
+Example: mapping of a ``Point`` class into the ``point`` PostgreSQL geometric
+type::
+
+ from psycopg2.extensions import adapt, register_adapter, AsIs
+
+ class Point(object):
+ def __init__(self, x=0.0, y=0.0):
+ self.x = x
+ self.y = y
+
+ def adapt_point(point):
+ return AsIs("'(%s,%s)'" % (adapt(point.x), adapt(point.y)))
+
+ register_adapter(Point, adapt_point)
+
+ curs.execute("INSERT INTO atable (apoint) VALUES (%s)",
+ (Point(1.23, 4.56),))
+
+The above function call results in the SQL command::
+
+ INSERT INTO atable (apoint) VALUES ((1.23, 4.56));
+
+
+.. _PEP-246: http://www.python.org/peps/pep-0246.html
+
+
+Type casting of SQL types into Python values
+============================================
+
+PostgreSQL objects read from the database can be adapted to Python objects
+through an user-defined adapting function. An adapter function takes two
+argments: the object string representation as returned by PostgreSQL and the
+cursor currently being read, and should return a new Python object. For
+example, the following function parses a PostgreSQL ``point`` into the
+previously defined ``Point`` class::
+
+ def cast_point(value, curs):
+ if value is not None:
+ # Convert from (f1, f2) syntax using a regular expression.
+ m = re.match("\((.*),(.*)\)", value)
+ if m:
+ return Point(float(m.group(1)), float(m.group(2)))
+
+To create a mapping from the PostgreSQL type (either standard or user-defined),
+its ``oid`` must be known. It can be retrieved either by the second column of
+the cursor description::
+
+ curs.execute("SELECT NULL::point")
+ point_oid = curs.description[0][1] # usually returns 600
+
+or by querying the system catalogs for the type name and namespace (the
+namespace for system objects is ``pg_catalog``)::
+
+ curs.execute("""
+ SELECT pg_type.oid
+ FROM pg_type JOIN pg_namespace
+ ON typnamespace = pg_namespace.oid
+ WHERE typname = %(typename)s
+ AND nspname = %(namespace)s""",
+ {'typename': 'point', 'namespace': 'pg_catalog'})
+
+ point_oid = curs.fetchone()[0]
+
+After you know the object ``oid``, you must can and register the new type::
+
+ POINT = psycopg2.extensions.new_type((point_oid,), "POINT", cast_point)
+ psycopg2.extensions.register_type(POINT)
+
+The `new_type()` function binds the object oids (more than one can be
+specified) to the adapter function. `register_type()` completes the spell.
+Conversion is automatically performed when a column whose type is a registered
+``oid`` is read::
+
+ curs.execute("SELECT '(10.2,20.3)'::point")
+ point = curs.fetchone()[0]
+ print type(point), point.x, point.y
+ # Prints: "<class '__main__.Point'> 10.2 20.3"
+
+
+Working with times and dates
+============================
+
+
+Receiving NOTIFYs
+=================
+
+
+Using COPY TO and COPY FROM
+===========================
+
+psycopg2 `cursor` object provides an interface to the efficient `PostgreSQL
+COPY command`__ to move data from files to tables and back.
+
+The `.copy_to(file, table)` method writes the content of the table
+named ``table`` *to* the file-like object ``file``. ``file`` must have a
+``write()`` method.
+
+The `.copy_from(file, table)` reads data *from* the file-like object
+``file`` appending them to the table named ``table``. ``file`` must have both
+``read()`` and ``readline()`` method.
+
+Both methods accept two optional arguments: ``sep`` (defaulting to a tab) is
+the columns separator and ``null`` (defaulting to ``\N``) represents ``NULL``
+values in the file.
+
+.. __: http://www.postgresql.org/docs/8.1/static/sql-copy.html
+
+
+PostgreSQL status message and executed query
+============================================
+
+`cursor` objects have two special fields related to the last executed query:
+
+ - `.query` is the textual representation (str or unicode, depending on what
+ was passed to `.execute()` as first argument) of the query *after* argument
+ binding and mogrification has been applied. To put it another way, `.query`
+ is the *exact* query that was sent to the PostgreSQL backend.
+
+ - `.statusmessage` is the status message that the backend sent upon query
+ execution. It usually contains the basic type of the query (SELECT,
+ INSERT, UPDATE, ...) and some additional information like the number of
+ rows updated and so on. Refer to the PostgreSQL manual for more
+ information.
git://git.onelab.eu / plcapi.git / blobdiff