Core API Reference¶
Connection¶
The library provides a way to connect to PostgreSQL database.
Example:
@asyncio.coroutine
def go():
conn = yield from aiopg.connect(database='aiopg',
user='aiopg',
password='secret',
host='127.0.0.1')
cur = yield from conn.cursor()
yield from cur.execute("SELECT * FROM tbl")
ret = yield from cur.fetchall()
-
aiopg.
connect
(dsn=None, *, loop=None, timeout=60.0, enable_json=True, enable_hstore=True, echo=False, **kwargs)¶ A coroutine that connects to PostgreSQL.
The function accepts all parameters that
psycopg2.connect()
does plus optional keyword-only loop and timeout parameters.Parameters: - loop – asyncio event loop instance or
None
for default one. - timeout (float) –
default timeout (in seconds) for connection operations.
60 secs by default.
- enable_json (bool) –
enable json column types for connection.
True
by default. - enable_hstore (bool) –
try to enable hstore column types for connection.
True
by default.For using HSTORE columns extension should be installed in database first:
CREATE EXTENSION HSTORE
- echo (bool) – log executed SQL statement (
False
by default).
Returns: Connection
instance.- loop – asyncio event loop instance or
-
class
aiopg.
Connection
¶ A connection to a PostgreSQL database instance. It encapsulates a database session.
Its insterface is very close to
psycopg2.connection
(http://initd.org/psycopg/docs/connection.html) except all methods are coroutines.Use
connect()
for creating connection.The most important method is
-
cursor
(name=None, cursor_factory=None, scrollable=None, withhold=False, *, timeout=None)¶ A coroutine that creates a new cursor object using the connection.
The only cursor_factory can be specified, all other parameters are not supported by psycopg2 in asynchronous mode yet.
The cursor_factory argument can be used to create non-standard cursors. The argument must be a subclass of psycopg2.extensions.cursor. See subclassing-cursor for details. A default factory for the connection can also be specified using the
Connection.cursor_factory
attribute.timeout is a timeout for returned cursor instance if parameter is not None.
name, scrollable and withhold parameters are not supported by psycopg2 in asynchronous mode.
Returns: Cursor
instance.
-
close
()¶ Immediatelly close the connection.
Close the connection now (rather than whenever del is executed). The connection will be unusable from this point forward; an psycopg2.InterfaceError will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. Note that closing a connection without committing the changes first will cause any pending change to be discarded as if a
ROLLBACK
was performed.Changed in version 0.5:
close()
is regular function now. For sake of backward compatibility the method returnsasyncio.Future
instance with result already set toNone
(you still can useyield from conn.close()
construction.
-
closed
¶ The readonly property that returns
True
if connections is closed.
-
echo
¶ Return echo mode status. Log all executed queries to logger named
aiopg
ifTrue
-
raw
¶ The readonly property that underlying
psycopg2.connection
instance.
-
cancel
(timeout=None)¶ A coroutine that cancels current database operation.
The method interrupts the processing of the current operation. If no query is being executed, it does nothing. You can call this function from a different thread than the one currently executing a database operation, for instance if you want to cancel a long running query if a button is pushed in the UI. Interrupting query execution will cause the cancelled method to raise a
psycopg2.extensions.QueryCanceledError
. Note that the termination of the query is not guaranteed to succeed: see the documentation forPQcancel()
.Parameters: timeout (float) – timeout for cancelling.
-
dsn
¶ The readonly property that returns dsn string used by the connection.
-
autocommit
¶ Autocommit mode status for connection (always
True
).Note
psycopg2 doesn’t allow to change autocommit mode in asynchronous mode.
-
encoding
¶ Client encoding for SQL operations.
Note
psycopg2 doesn’t allow to change encoding in asynchronous mode.
-
isolation_level
¶ Get the transaction isolation level for the current session.
Note
The only value allowed in asynchronous mode value is
psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
(READ COMMITTED
).
-
notices
¶ A list containing all the database messages sent to the client during the session:
>>> cur.execute("CREATE TABLE foo (id serial PRIMARY KEY);") >>> pprint(conn.notices) ['NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"\n', 'NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"\n']
To avoid a leak in case excessive notices are generated, only the last 50 messages are kept.
You can configure what messages to receive using PostgreSQL logging configuration parameters such as
log_statement
,client_min_messages
,log_min_duration_statement
etc.
-
cursor_factory
¶ The default cursor factory used by Connection.cursor() if the parameter is not specified.
-
get_backend_pid
()¶ Returns the process ID (PID) of the backend server process handling this connection.
Note that the PID belongs to a process executing on the database server host, not the local host!
See also
libpq docs for PQbackendPID() for details.
-
get_parameter_status
(parameter)¶ Look up a current parameter setting of the server.
Potential values for
parameter
are:server_version
,server_encoding
,client_encoding
,is_superuser
,session_authorization
,DateStyle
,TimeZone
,integer_datetimes
, andstandard_conforming_strings
.If server did not report requested parameter, return
None
.See also
libpq docs for PQparameterStatus() for details.
-
get_transaction_status
()¶ Return the current session transaction status as an integer. Symbolic constants for the values are defined in the module psycopg2.extensions: see transaction-status-constants for the available values.
See also
libpq docs for PQtransactionStatus() for details.
-
protocol_version
¶ A read-only integer representing frontend/backend protocol being used. Currently Psycopg supports only protocol 3, which allows connection to PostgreSQL server from version 7.4. Psycopg versions previous than 2.3 support both protocols 2 and 3.
See also
libpq docs for PQprotocolVersion() for details.
-
server_version
¶ A read-only integer representing the backend version.
The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as
80105
.See also
libpq docs for PQserverVersion() for details.
-
status
¶ A read-only integer representing the status of the connection. Symbolic constants for the values are defined in the module psycopg2.extensions: see connection-status-constants for the available values.
The status is undefined for closed connectons.
-
timeout
¶ A read-only float representing default timeout for connection’s operations.
The
Connection
class also has several methods not described here. Those methods are not supported in asynchronous mode (psycopg2.ProgrammingError
is raised).-
Cursor¶
-
class
aiopg.
Cursor
¶ A cursor for connection.
Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the
Connection.cursor()
coroutine: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.Cursors that are created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. Cursors created from different connections can or can not be isolated, depending on the connections’ isolation level.
Its insterface is very close to
psycopg2.cursor
(http://initd.org/psycopg/docs/cursor.html) except all methods are coroutines.Use
Connection.cursor()
for getting cursor for connection.-
echo
¶ Return echo mode status. Log all executed queries to logger named
aiopg
ifTrue
-
description
¶ This read-only attribute is a sequence of 7-item sequences.
Each of these sequences is a
collections.namedtuple()
containing information describing one result column:- name: the name of the column returned.
- type_code: the PostgreSQL OID of the column. You can use the
pg_type
system table to get more informations about the type. This is the value used by Psycopg to decide what Python type use to represent the value. See also type-casting-from-sql-to-python. - display_size: the actual length of the column in bytes.
Obtaining this value is computationally intensive, so it is
always
None
unless thePSYCOPG_DISPLAY_SIZE
parameter is set at compile time. See also PQgetlength. - internal_size: the size in bytes of the column associated to this column on the server. Set to a negative value for variable-size types See also PQfsize.
- precision: total number of significant digits in columns of
type
NUMERIC
.None
for other types. - scale: count of decimal digits in the fractional part in
columns of type
NUMERIC
.None
for other types. - null_ok: always
None
as not easy to retrieve from the libpq.
This attribute will be
None
for operations that do not return rows or if the cursor has not had an operation invoked via theexecute()
method yet.
-
close
()¶ Close the cursor now (rather than whenever
del
is executed). The cursor will be unusable from this point forward; anpsycopg2.InterfaceError
will be raised if any operation is attempted with the cursor.
-
closed
¶ Read-only boolean attribute: specifies if the cursor is closed (
True
) or not (False
).
-
raw
¶ The readonly property that underlying
psycopg2.cursor
instance.
-
connection
¶ Read-only attribute returning a reference to the
Connection
object on which the cursor was created.
-
timeout
¶ A read-only float representing default timeout for cursor’s operations.
-
execute
(operation, parameters=None, *, timeout=None)¶ Prepare and execute a database operation (query or command).
Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified either with positional (
%s
) or named (%(name)s
) placeholders. See query-parameters.Parameters: timeout (float) – overrides cursor’s timeout if not None
.Returns: None
. If a query was executed, the returned values can be retrieved usingfetch*()
methods.
-
callproc
(procname, parameters=None, *, timeout=None)¶ Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.
The procedure may also provide a result set as output. This must then be made available through the standard
fetch*()
methods.Parameters: timeout (float) – overrides cursor’s timeout if not None
.
-
mogrify
(operation, parameters=None)¶ Returns a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the
Cursor.execute()
method or similar.The returned string is always a bytes string:
>>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar')) "INSERT INTO test (num, data) VALUES (42, E'bar')"
-
setinputsizes
(sizes)¶ This method is exposed in compliance with the DBAPI. It currently does nothing but it is safe to call it.
Results retrieval methods
The following methods are used to read data from the database after an
Cursor.execute()
call.Note
Cursor
objects are iterable, so, instead of calling explicitlyCursor.fetchone()
in a loop, the object itself can be used:>>> cur.execute("SELECT * FROM test;") >>> for record in cur: ... print(record) ... (1, 100, "abc'def") (2, None, 'dada') (3, 42, 'bar')
-
fetchone
()¶ Fetch the next row of a query result set, returning a single tuple, or
None
when no more data is available:>>> cur.execute("SELECT * FROM test WHERE id = %s", (3,)) >>> cur.fetchone() (3, 42, 'bar')
A
psycopg2.ProgrammingError
is raised if the previous call toexecute()
did not produce any result set or no call was issued yet.
-
fetchmany
(size=cursor.arraysize)¶ Fetch the next set of rows of a query result, returning a list of tuples. An empty list is returned when no more rows are available.
The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s
Cursor.arraysize
determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned:>>> cur.execute("SELECT * FROM test;") >>> cur.fetchmany(2) [(1, 100, "abc'def"), (2, None, 'dada')] >>> cur.fetchmany(2) [(3, 42, 'bar')] >>> cur.fetchmany(2) []
A
psycopg2.ProgrammingError
is raised if the previous call toexecute()
did not produce any result set or no call was issued yet.Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the
Cursor.arraysize
attribute. If the size parameter is used, then it is best for it to retain the same value from onefetchmany()
call to the next.
-
fetchall
()¶ Fetch all (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch:
>>> cur.execute("SELECT * FROM test;") >>> cur.fetchall() [(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]
A
psycopg2.ProgrammingError
is raised if the previous call toexecute()
did not produce any result set or no call was issued yet.-
scroll
(value, mode='relative')¶ Scroll the cursor in the result set to a new position according to mode.
If mode is
relative
(default), value is taken as offset to the current position in the result set, if set toabsolute
, value states an absolute target position.If the scroll operation would leave the result set, a
psycopg2.ProgrammingError
is raised and the cursor position is not changed.Note
According to the DBAPI, the exception raised for a cursor out of bound should have been
IndexError
. The best option is probably to catch both exceptions in your code:try: cur.scroll(1000 * 1000) except (ProgrammingError, IndexError), exc: deal_with_it(exc)
-
-
arraysize
¶ This read/write attribute specifies the number of rows to fetch at a time with
Cursor.fetchmany()
. It defaults to 1 meaning to fetch a single row at a time.
-
rowcount
¶ This read-only attribute specifies the number of rows that the last
execute()
produced (for DQL statements likeSELECT
) or affected (for DML statements likeUPDATE
orINSERT
).The attribute is -1 in case no
execute()
has been performed on the cursor or the row count of the last operation if it can’t be determined by the interface.Note
The DBAPI interface reserves to redefine the latter case to have the object return
None
instead of -1 in future versions of the specification.
-
rownumber
¶ This read-only attribute provides the current 0-based index of the cursor in the result set or
None
if the index cannot be determined.The index can be seen as index of the cursor in a sequence (the result set). The next fetch operation will fetch the row indexed by
rownumber
in that sequence.
-
lastrowid
¶ This read-only attribute provides the OID of the last row inserted by the cursor. If the table wasn’t created with OID support or the last operation is not a single record insert, the attribute is set to
None
.Note
PostgreSQL currently advices to not create OIDs on the tables and the default for
CREATE TABLE
is to not support them. TheINSERT ... RETURNING
syntax available from PostgreSQL 8.3 allows more flexibility.
-
query
¶ Read-only attribute containing the body of the last query sent to the backend (including bound arguments) as bytes string.
None
if no query has been executed yet:>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar')) >>> cur.query "INSERT INTO test (num, data) VALUES (42, E'bar')"
-
statusmessage
¶ Read-only attribute containing the message returned by the last command:
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar')) >>> cur.statusmessage 'INSERT 0 1'
-
tzinfo_factory
¶ The time zone factory used to handle data types such as
TIMESTAMP WITH TIME ZONE
. It should be a datetime.tzinfo object. A few implementations are available in thepsycopg2.tz
module.
-
Pool¶
The library provides connection pool as well as plain
Connection
objects.
The basic usage is:
import asyncio
import aiopg
dsn = 'dbname=jetty user=nick password=1234 host=localhost port=5432'
@asyncio.coroutine
def test_select():
pool = yield from aiopg.create_pool(dsn)
with (yield from pool.cursor()) as cur:
yield from cur.execute('SELECT 1')
ret = yield from cur.fetchone()
assert ret == (1,), ret
-
aiopg.
create_pool
(dsn=None, *, minsize=10, maxsize=10, enable_json=True, enable_hstore=True, loop=None, timeout=60.0, **kwargs)¶ A coroutine that creates a pool of connections to PostgreSQL database.
The function accepts all parameters that
psycopg2.connect()
does plus optional keyword-only parameters loop, minsize, maxsize.- loop is an optional event loop instance,
asyncio.get_event_loop()
is used if loop is not specified.
minsize and maxsize are minimum and maximum sizes of the pool.
- timeout is a default timeout (in seconds) for connection
- operations. 60 secs if not specified.
enable_json — enable json column types for connections created by the pool.
True
by default.enable_hstore — try to enable hstore column types for connections created by the pool.
True
by default.For using HSTORE columns extension should be installed in database first:
CREATE EXTENSION HSTORE
echo — executed log SQL queryes (
False
by default).Returns
Pool
instance.
-
class
aiopg.
Pool
¶ A connection pool.
After creation pool has minsize free connections and can grow up to maxsize ones.
If minsize is
0
the pool doesn’t creates any connection on startup.If maxsize is
0
than size of pool is unlimited (but it recycles used connections of course).The most important way to use it is getting connection in with statement:
with (yield from pool) as conn: cur = yield from conn.cursor()
and shortcut for getting cursor directly:
with (yield from pool.cursor()) as cur: yield from cur.execute('SELECT 1')
See also
Pool.acquire()
andPool.release()
for acquring connection without with statement.-
echo
¶ Return echo mode status. Log all executed queries to logger named
aiopg
ifTrue
-
minsize
¶ A minimal size of the pool (read-only),
10
by default.
-
maxsize
¶ A maximal size of the pool (read-only),
10
by default.
-
size
¶ A current size of the pool (readonly). Includes used and free connections.
-
freesize
¶ A count of free connections in the pool (readonly).
-
timeout
¶ A read-only float representing default timeout for operations for connections from pool.
-
clear
()¶ A coroutine that closes all free connections in the pool. At next connection acquiring at least
minsize
of them will be recreated.
-
close
()¶ Close pool.
Mark all pool connections to be closed on getting back to pool. Closed pool doesn’t allow to acquire new connections.
If you want to wait for actual closing of acquired connection please call
wait_closed()
afterclose()
.Warning
The method is not a coroutine.
-
terminate
()¶ Terminate pool.
Close pool with instantly closing all acquired connections also.
wait_closed()
should be called afterterminate()
for waiting for actual finishing.Warning
The method is not a coroutine.
-
wait_closed
()¶ A coroutine that waits for releasing and closing all acquired connections.
Should be called after
close()
for waiting for actual pool closing.
-
acquire
()¶ A coroutine that acquires a connection from free pool. Creates new connection if needed and
size
of pool is less thanmaxsize
.Returns a
Connection
instance.
-
release
(conn)¶ Reverts connection conn to free pool for future recycling.
Warning
The method is not a coroutine.
-
cursor
(name=None, cursor_factory=None, scrollable=None, withhold=False, *, timeout=None)¶ A coroutine that
acquires
a connection and returns context manager.The only cursor_factory can be specified, all other parameters are not supported by psycopg2 in asynchronous mode yet.
The cursor_factory argument can be used to create non-standard cursors. The argument must be a subclass of psycopg2.extensions.cursor. See subclassing-cursor for details. A default factory for the connection can also be specified using the
Connection.cursor_factory
attribute.timeout is a timeout for returned cursor instance if parameter is not None.
name, scrollable and withhold parameters are not supported by psycopg2 in asynchronous mode.
The usage is:
with (yield from pool.cursor()) as cur: yield from cur.execute('SELECT 1')
After exiting from with block cursor cur will be closed.
-
Exceptions¶
Any call to library function, method or property can raise an exception.
aiopg
doesn’t define any exception class itself, it reuses
DBAPI Exceptions from psycopg2
Transactions¶
While aiopg
works only in autocommit mode it is still
possible to use SQL transactions.
Just execute BEGIN and COMMIT statements manually.
Connection.commit()
and Connection.rollback()
methods are
disabled and always raises psycopg2.ProgrammingError
exception.
Extension type translations¶
JSON¶
aiopg
has support for JSON
data type enabled by default.
For pushing data to server please wrap json dict into
psycopg2.extras.Json
:
from psycopg2.extras import Json
data = {'a': 1, 'b': 'str'}
yield from cur.execute("INSERT INTO tbl (val) VALUES (%s)", [Json(data)])
On receiving data from json column psycopg2 autoconvers result
into python dict
object:
yield from cur.execute("SELECT val FROM tbl")
item = yield from cur.fetchone()
assert item == {'b': 'str', 'a': 1}