Support for the PostgreSQL database.
For information on connecting using specific drivers, see the documentation section regarding that driver.
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().
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).
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 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)
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:
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.
Construct an ARRAY.
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.|
Construct a Float.
Construct an enum.
Keyword arguments which don’t apply to a specific backend are ignored by that backend.
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.
Construct a Float.
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.
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.|
Support for the PostgreSQL database via the psycopg2 driver.
The psycopg2 driver is supported, 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.
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. Note that this setting requires that the PG client encoding be set to one which can accomodate the kind of character data being passed - typically utf-8. If the Postgresql database is configured for SQL_ASCII encoding, which is often the default for PG installations, it may be necessary for non-ascii strings to be encoded into a specific encoding before being passed to the DBAPI. If changing the database’s client encoding setting is not an option, specify use_native_unicode=False as a keyword argument to create_engine(), and take note of the encoding setting as well, which also defaults to utf-8. Note that disabling “native unicode” mode has a slight performance penalty, as SQLAlchemy now must translate unicode strings to/from an encoding such as utf-8, a task that is handled more efficiently within the Psycopg2 driver natively.
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:
The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
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.
The psycopg2 dialect will log Postgresql NOTICE messages via the sqlalchemy.dialects.postgresql logger:
import logging logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)
The following per-statement execution options are respected:
Support for the PostgreSQL database via py-postgresql.
URLs are of the form postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...].
Support for the PostgreSQL database via the pg8000 driver.
URLs are of the form postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...].
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”.
Passing data from/to the Interval type is not supported as of yet.