Postgres Connector

A PostgresConnector instance contains a cursor for a Postgres connection and methods to execute queries.

class sprockets_postgres.PostgresConnector(cursor, on_error=None, on_duration=None, timeout=None)[source]

Wraps a aiopg.Cursor instance for creating explicit transactions, calling stored procedures, and executing queries.

Unless the transaction() asynchronous context-manager is used, each call to callproc() and execute() is an explicit transaction.

Note

PostgresConnector instances are created by ApplicationMixin.postgres_connector and should not be created directly.

Parameters
  • cursor (aiopg.Cursor) – The cursor to use in the connector

  • on_error (typing.Optional[typing.Callable]) – The callback to invoke when an exception is caught

  • on_duration (typing.Optional[typing.Callable]) – The callback to invoke when a query is complete and all of the data has been returned.

  • timeout (Timeout) – A timeout value in seconds for executing queries. If unspecified, defaults to the configured query timeout of 120 seconds.

async callproc(name, parameters=None, metric_name='', *, timeout=None)[source]

Execute a stored procedure / function

Parameters
  • name (str) – The stored procedure / function name to call

  • parameters (QueryParameters) – Query parameters to pass when calling

  • metric_name (str) – The metric name for duration recording and logging

  • timeout (Timeout) – Timeout value to override the default or the value specified when creating the PostgresConnector.

Raises
Return type

QueryResult

async execute(sql, parameters=None, metric_name='', *, timeout=None)[source]

Execute a query, specifying a name for the query, the SQL statement, and optional positional arguments to pass in with the query.

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.

Parameters
  • sql (str) – The SQL statement to execute

  • parameters (QueryParameters) – Query parameters to pass as part of the execution

  • metric_name (str) – The metric name for duration recording and logging

  • timeout (Timeout) – Timeout value to override the default or the value specified when creating the PostgresConnector.

Raises
Return type

QueryResult

transaction()[source]

asynchronous context-manager function that implements full BEGIN, COMMIT, and ROLLBACK semantics. If there is a psycopg2.Error raised during the transaction, the entire transaction will be rolled back.

If no exception is raised, the transaction will be committed when exiting the context manager.

Note

This method is provided for edge case usage. As a generalization sprockets_postgres.RequestHandlerMixin.postgres_transaction() should be used instead.

Usage Example

class RequestHandler(sprockets_postgres.RequestHandlerMixin,
                     web.RequestHandler):

    async def post(self):
        async with self.postgres_transaction() as transaction:
            result1 = await transaction.execute(QUERY_ONE)
            result2 = await transaction.execute(QUERY_TWO)
            result3 = await transaction.execute(QUERY_THREE)
Raises
  • asyncio.TimeoutError – when there is a query or network timeout when starting the transaction

  • psycopg2.Error – when there is an exception raised by Postgres when starting the transaction

Return type

typing.AbstractAsyncContextManager[sprockets_postgres.PostgresConnector]