SQLAlchemy 0.7 Documentation

Release: 0.7.10 | Release Date: February 7, 2013 | Download PDF

PostgreSQL

Support for the PostgreSQL database.

For information on connecting using specific drivers, see the documentation section regarding that driver.

Sequences/SERIAL

PostgreSQL supports sequences, and SQLAlchemy uses these as the default means of creating new primary key values for integer-based primary key columns. When creating tables, SQLAlchemy will issue the SERIAL datatype for integer-based primary key columns, which generates a sequence and server side default corresponding to the column.

To specify a specific named sequence to be used for primary key generation, use the Sequence() construct:

Table('sometable', metadata,
        Column('id', Integer, Sequence('some_id_seq'), primary_key=True)
    )

When SQLAlchemy issues a single INSERT statement, to fulfill the contract of having the “last insert identifier” available, a RETURNING clause is added to the INSERT statement which specifies the primary key columns should be returned after the statement completes. The RETURNING functionality only takes place if Postgresql 8.2 or later is in use. As a fallback approach, the sequence, whether specified explicitly or implicitly via SERIAL, is executed independently beforehand, the returned value to be used in the subsequent insert. Note that when an insert() construct is executed using “executemany” semantics, the “last inserted identifier” functionality does not apply; no RETURNING clause is emitted nor is the sequence pre-executed in this case.

To force the usage of RETURNING by default off, specify the flag implicit_returning=False to create_engine().

Transaction Isolation Level

create_engine() accepts an isolation_level parameter which results in the command SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> being invoked for every new connection. Valid values for this parameter are READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE:

engine = create_engine(
                "postgresql+pg8000://scott:tiger@localhost/test",
                isolation_level="READ UNCOMMITTED"
            )

When using the psycopg2 dialect, a psycopg2-specific method of setting transaction isolation level is used, but the API of isolation_level remains the same - see Transaction Isolation Level.

Remote / Cross-Schema Table Introspection

Tables can be introspected from any accessible schema, including inter-schema foreign key relationships. However, care must be taken when specifying the “schema” argument for a given Table, when the given schema is also present in PostgreSQL’s search_path variable for the current connection.

If a FOREIGN KEY constraint reports that the remote table’s schema is within the current search_path, the “schema” attribute of the resulting Table will be set to None, unless the actual schema of the remote table matches that of the referencing table, and the “schema” argument was explicitly stated on the referencing table.

The best practice here is to not use the schema argument on Table for any schemas that are present in search_path. search_path defaults to “public”, but care should be taken to inspect the actual value using:

SHOW search_path;

Changed in version 0.7.3: Prior to this version, cross-schema foreign keys when the schemas were also in the search_path could make an incorrect assumption if the schemas were explicitly stated on each Table.

Background on PG’s search_path is at: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH

INSERT/UPDATE...RETURNING

The dialect supports PG 8.2’s INSERT..RETURNING, UPDATE..RETURNING and DELETE..RETURNING syntaxes. INSERT..RETURNING is used by default for single-row INSERT statements in order to fetch newly generated primary key identifiers. To specify an explicit RETURNING clause, use the _UpdateBase.returning() method on a per-statement basis:

# INSERT..RETURNING
result = table.insert().returning(table.c.col1, table.c.col2).\
    values(name='foo')
print result.fetchall()

# UPDATE..RETURNING
result = table.update().returning(table.c.col1, table.c.col2).\
    where(table.c.name=='foo').values(name='bar')
print result.fetchall()

# DELETE..RETURNING
result = table.delete().returning(table.c.col1, table.c.col2).\
    where(table.c.name=='foo')
print result.fetchall()

Postgresql-Specific Index Options

Several extensions to the Index construct are available, specific to the PostgreSQL dialect.

Partial Indexes

Partial indexes add criterion to the index definition so that the index is applied to a subset of rows. These can be specified on Index using the postgresql_where keyword argument:

Index('my_index', my_table.c.id, postgresql_where=tbl.c.value > 10)

Operator Classes

PostgreSQL allows the specification of an operator class for each column of an index (see http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html). The Index construct allows these to be specified via the postgresql_ops keyword argument:

Index('my_index', my_table.c.id, my_table.c.data,
                        postgresql_ops={
                            'data': 'text_pattern_ops',
                            'id': 'int4_ops'
                        })

New in version 0.7.2: postgresql_ops keyword argument to Index construct.

Note that the keys in the postgresql_ops dictionary are the “key” name of the Column, i.e. the name used to access it from the .c collection of Table, which can be configured to be different than the actual name of the column as expressed in the database.

Index Types

PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well as the ability for users to create their own (see http://www.postgresql.org/docs/8.3/static/indexes-types.html). These can be specified on Index using the postgresql_using keyword argument:

Index('my_index', my_table.c.data, postgresql_using='gin')

The value passed to the keyword argument will be simply passed through to the underlying CREATE INDEX command, so it must be a valid index type for your version of PostgreSQL.

PostgreSQL Data Types

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with Postgresql are importable from the top level dialect, whether they originate from sqlalchemy.types or from the local dialect:

from sqlalchemy.dialects.postgresql import \
    ARRAY, BIGINT, BIT, BOOLEAN, BYTEA, CHAR, CIDR, DATE, \
    DOUBLE_PRECISION, ENUM, FLOAT, INET, INTEGER, INTERVAL, \
    MACADDR, NUMERIC, REAL, SMALLINT, TEXT, TIME, TIMESTAMP, \
    UUID, VARCHAR

Types which are specific to PostgreSQL, or have PostgreSQL-specific construction arguments, are as follows:

class sqlalchemy.dialects.postgresql.ARRAY(item_type, mutable=False, as_tuple=False)

Bases: sqlalchemy.types.MutableType, sqlalchemy.types.Concatenable, sqlalchemy.types.TypeEngine

Postgresql ARRAY type.

Represents values as Python lists.

The ARRAY type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000.

__init__(item_type, mutable=False, as_tuple=False)

Construct an ARRAY.

E.g.:

Column('myarray', ARRAY(Integer))

Arguments are:

Parameters:
  • item_type – The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like INTEGER[][], are constructed as ARRAY(Integer), not as ARRAY(ARRAY(Integer)) or such. The type mapping figures out on the fly
  • mutable=False

    Specify whether lists passed to this class should be considered mutable - this enables “mutable types” mode in the ORM. Be sure to read the notes for MutableType regarding ORM performance implications.

    Changed in version 0.7.0: Default changed from True.

    Changed in version 0.7: This functionality is now superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking.

  • as_tuple=False

    Specify whether return results should be converted to tuples from lists. DBAPIs such as psycopg2 return lists by default. When tuples are returned, the results are hashable. This flag can only be set to True when mutable is set to False.

    New in version 0.6.5.

class sqlalchemy.dialects.postgresql.BIT(length=None, varying=False)

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.BYTEA(length=None)

Bases: sqlalchemy.types.LargeBinary

__init__(length=None)

Construct a LargeBinary type.

Parameters:length – optional, a length for the column for use in DDL statements, for those BLOB types that accept a length (i.e. MySQL). It does not produce a small BINARY/VARBINARY type - use the BINARY/VARBINARY types specifically for those. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued.
class sqlalchemy.dialects.postgresql.CIDR(*args, **kwargs)

Bases: sqlalchemy.types.TypeEngine

__init__(*args, **kwargs)

Support implementations that were passing arguments

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION(precision=None, asdecimal=False, **kwargs)

Bases: sqlalchemy.types.Float

__init__(precision=None, asdecimal=False, **kwargs)

Construct a Float.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • asdecimal – the same flag as that of Numeric, but defaults to False. Note that setting this flag to True results in floating point conversion.
  • **kwargs – deprecated. Additional arguments here are ignored by the default Float type. For database specific floats that support additional arguments, see that dialect’s documentation for details, such as sqlalchemy.dialects.mysql.FLOAT.
class sqlalchemy.dialects.postgresql.ENUM(*enums, **kw)

Bases: sqlalchemy.types.Enum

Postgresql ENUM type.

This is a subclass of types.Enum which includes support for PG’s CREATE TYPE.

ENUM is used automatically when using the types.Enum type on PG assuming the native_enum is left as True. However, the ENUM class can also be instantiated directly in order to access some additional Postgresql-specific options, namely finer control over whether or not CREATE TYPE should be emitted.

Note that both types.Enum as well as ENUM feature create/drop methods; the base types.Enum type ultimately delegates to the create() and drop() methods present here.

__init__(*enums, **kw)

Construct an ENUM.

Arguments are the same as that of types.Enum, but also including the following parameters.

Parameters:create_type

Defaults to True. Indicates that CREATE TYPE should be emitted, after optionally checking for the presence of the type, when the parent table is being created; and additionally that DROP TYPE is called when the table is dropped. When False, no check will be performed and no CREATE TYPE or DROP TYPE is emitted, unless create() or drop() are called directly. Setting to False is helpful when invoking a creation scheme to a SQL file without access to the actual database - the create() and drop() methods can be used to emit SQL to a target bind.

New in version 0.7.4.

create(bind=None, checkfirst=True)

Emit CREATE TYPE for this ENUM.

If the underlying dialect does not support Postgresql CREATE TYPE, no action is taken.

Parameters:
  • bind – a connectable Engine, Connection, or similar object to emit SQL.
  • checkfirst – if True, a query against the PG catalog will be first performed to see if the type does not exist already before creating.
drop(bind=None, checkfirst=True)

Emit DROP TYPE for this ENUM.

If the underlying dialect does not support Postgresql DROP TYPE, no action is taken.

Parameters:
  • bind – a connectable Engine, Connection, or similar object to emit SQL.
  • checkfirst – if True, a query against the PG catalog will be first performed to see if the type actually exists before dropping.
class sqlalchemy.dialects.postgresql.INET(*args, **kwargs)

Bases: sqlalchemy.types.TypeEngine

__init__(*args, **kwargs)

Support implementations that were passing arguments

class sqlalchemy.dialects.postgresql.INTERVAL(precision=None)

Bases: sqlalchemy.types.TypeEngine

Postgresql INTERVAL type.

The INTERVAL type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000 or zxjdbc.

class sqlalchemy.dialects.postgresql.MACADDR(*args, **kwargs)

Bases: sqlalchemy.types.TypeEngine

__init__(*args, **kwargs)

Support implementations that were passing arguments

class sqlalchemy.dialects.postgresql.REAL(precision=None, asdecimal=False, **kwargs)

Bases: sqlalchemy.types.Float

The SQL REAL type.

__init__(precision=None, asdecimal=False, **kwargs)

Construct a Float.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • asdecimal – the same flag as that of Numeric, but defaults to False. Note that setting this flag to True results in floating point conversion.
  • **kwargs – deprecated. Additional arguments here are ignored by the default Float type. For database specific floats that support additional arguments, see that dialect’s documentation for details, such as sqlalchemy.dialects.mysql.FLOAT.
class sqlalchemy.dialects.postgresql.UUID(as_uuid=False)

Bases: sqlalchemy.types.TypeEngine

Postgresql UUID type.

Represents the UUID column type, interpreting data either as natively returned by the DBAPI or as Python uuid objects.

The UUID type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000.

__init__(as_uuid=False)

Construct a UUID type.

Parameters:as_uuid=False – if True, values will be interpreted as Python uuid objects, converting to/from string via the DBAPI.

psycopg2 Notes

Support for the PostgreSQL database via the psycopg2 driver.

Driver

The psycopg2 driver is available at http://pypi.python.org/pypi/psycopg2/ . The dialect has several behaviors which are specifically tailored towards compatibility with this module.

Note that psycopg1 is not supported.

Connecting

URLs are of the form postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...].

psycopg2-specific keyword arguments which are accepted by create_engine() are:

  • server_side_cursors - Enable the usage of “server side cursors” for SQL statements which support this feature. What this essentially means from a psycopg2 point of view is that the cursor is created using a name, e.g. connection.cursor('some name'), which has the effect that result rows are not immediately pre-fetched and buffered after statement execution, but are instead left on the server and only retrieved as needed. SQLAlchemy’s ResultProxy uses special row-buffering behavior when this feature is enabled, such that groups of 100 rows at a time are fetched over the wire to reduce conversational overhead. Note that the stream_results=True execution option is a more targeted way of enabling this mode on a per-execution basis.
  • use_native_unicode - Enable the usage of Psycopg2 “native unicode” mode per connection. True by default.

Unix Domain Connections

psycopg2 supports connecting via Unix domain connections. When the host portion of the URL is omitted, SQLAlchemy passes None to psycopg2, which specifies Unix-domain communication rather than TCP/IP communication:

create_engine("postgresql+psycopg2://user:password@/dbname")

By default, the socket file used is to connect to a Unix-domain socket in /tmp, or whatever socket directory was specified when PostgreSQL was built. This value can be overridden by passing a pathname to psycopg2, using host as an additional keyword argument:

create_engine("postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql")

See also:

PQconnectdbParams

Per-Statement/Connection Execution Options

The following DBAPI-specific options are respected when used with Connection.execution_options(), Executable.execution_options(), Query.execution_options(), in addition to those not specific to DBAPIs:

  • isolation_level - Set the transaction isolation level for the lifespan of a Connection (can only be set on a connection, not a statement or query). This includes the options SERIALIZABLE, READ COMMITTED, READ UNCOMMITTED and REPEATABLE READ.
  • stream_results - Enable or disable usage of server side cursors. If None or not set, the server_side_cursors option of the Engine is used.

Unicode

By default, the psycopg2 driver uses the psycopg2.extensions.UNICODE extension, such that the DBAPI receives and returns all strings as Python Unicode objects directly - SQLAlchemy passes these values through without change. Psycopg2 here will encode/decode string values based on the current “client encoding” setting; by default this is the value in the postgresql.conf file, which often defaults to SQL_ASCII. Typically, this can be changed to utf-8, as a more useful default:

#client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

A second way to affect the client encoding is to set it within Psycopg2 locally. SQLAlchemy will call psycopg2’s set_client_encoding() method (see: http://initd.org/psycopg/docs/connection.html#connection.set_client_encoding) on all new connections based on the value passed to create_engine() using the client_encoding parameter:

engine = create_engine("postgresql://user:pass@host/dbname", client_encoding='utf8')

This overrides the encoding specified in the Postgresql client configuration.

New in version 0.7.3: The psycopg2-specific client_encoding parameter to create_engine().

SQLAlchemy can also be instructed to skip the usage of the psycopg2 UNICODE extension and to instead utilize it’s own unicode encode/decode services, which are normally reserved only for those DBAPIs that don’t fully support unicode directly. Passing use_native_unicode=False to create_engine() will disable usage of psycopg2.extensions.UNICODE. SQLAlchemy will instead encode data itself into Python bytestrings on the way in and coerce from bytes on the way back, using the value of the create_engine() encoding parameter, which defaults to utf-8. SQLAlchemy’s own unicode encode/decode functionality is steadily becoming obsolete as more DBAPIs support unicode fully along with the approach of Python 3; in modern usage psycopg2 should be relied upon to handle unicode.

Transactions

The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.

Transaction Isolation Level

The isolation_level parameter of create_engine() here makes use psycopg2’s set_isolation_level() connection method, rather than issuing a SET SESSION CHARACTERISTICS command. This because psycopg2 resets the isolation level on each new transaction, and needs to know at the API level what level should be used.

NOTICE logging

The psycopg2 dialect will log Postgresql NOTICE messages via the sqlalchemy.dialects.postgresql logger:

import logging
logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)

py-postgresql Notes

Support for the PostgreSQL database via py-postgresql.

Connecting

URLs are of the form postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...].

pg8000 Notes

Support for the PostgreSQL database via the pg8000 driver.

Connecting

URLs are of the form postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...].

Unicode

pg8000 requires that the postgresql client encoding be configured in the postgresql.conf file in order to use encodings other than ascii. Set this value to the same value as the “encoding” parameter on create_engine(), usually “utf-8”.

Interval

Passing data from/to the Interval type is not supported as of yet.

zxjdbc Notes

Support for the PostgreSQL database via the zxjdbc JDBC connector.

JDBC Driver

The official Postgresql JDBC driver is at http://jdbc.postgresql.org/.