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. Note that the psycopg2 dialect does not use this technique and uses psycopg2-specific APIs (see that dialect for details).

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()

Indexes¶

PostgreSQL supports partial indexes. To create them pass a postgresql_where option to the Index constructor:

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

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=True, 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.

Note: be sure to read the notes for MutableType regarding ORM performance implications. The ARRAY type’s mutability can be disabled using the “mutable” flag.

__init__(item_type, mutable=True, 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=True – Specify whether lists passed to this class should be considered mutable. If so, generic copy operations (typically used by the ORM) will shallow-copy values. 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 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

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.

class sqlalchemy.dialects.postgresql.ENUM(*enums, **kw)¶

Bases: sqlalchemy.types.Enum

__init__(*enums, **kw)¶

Construct an enum.

Keyword arguments which don’t apply to a specific backend are ignored by that backend.

Parameters:

*enums – string or unicode enumeration labels. If unicode labels are present, the convert_unicode flag is auto-enabled. convert_unicode – Enable unicode-aware bind parameter and result-set processing for this Enum’s data. This is set automatically based on the presence of unicode label strings. metadata – Associate this type directly with a MetaData object. For types that exist on the target database as an independent schema construct (Postgresql), this type will be created and dropped within create_all() and drop_all() operations. If the type is not associated with any MetaData object, it will associate itself with each Table in which it is used, and will be created when any of those individual tables are created, after a check is performed for it’s existence. The type is only dropped when drop_all() is called for that Table object’s metadata, however. name – The name of this type. This is required for Postgresql and any future supported database which requires an explicitly named type, or an explicitly named constraint in order to generate the type and/or a table that uses it. native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR + check constraint for all backends. schema – Schemaname of this type. For types that exist on the target database as an independent schema construct (Postgresql), this parameter specifies the named schema in which the type is present. quote – Force quoting to be on or off on the type’s name. If left as the default of None, the usual schema-level “case sensitive”/”reserved name” rules are used to determine if this type’s name should be quoted.

class sqlalchemy.dialects.postgresql.INET(*args, **kwargs)¶

Bases: sqlalchemy.types.TypeEngine

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

class sqlalchemy.dialects.postgresql.REAL(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.

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.

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

py-postgresql Notes¶

Support for the PostgreSQL database via py-postgresql.

pg8000 Notes¶

Support for the PostgreSQL database via the pg8000 driver.

zxjdbc Notes¶

Support for the PostgreSQL database via the zxjdbc JDBC connector.