PostgreSQL Session API¶
The Session classes wrap the Queries Session and TornadoSession classes providing environment variable based configuration.
Environment variables should be set using the PGSQL[_DBNAME] format where the value is a PostgreSQL URI.
For PostgreSQL URI format, see:
http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING
As example, given the environment variable:
PGSQL_FOO = 'postgresql://bar:baz@foohost:6000/foo'
and code for creating a Session instance for the database name foo:
session = sprockets.postgresql.Session('foo')
A queries.Session object will be created that connects to Postgres running on foohost, port 6000 using the username bar and the password baz, connecting to the foo database.
Session Classes¶
- class sprockets.clients.postgresql.Session(dbname, cursor_factory=<class 'psycopg2.extras.RealDictCursor'>, pool_idle_ttl=60, pool_max_size=1, db_url=None)¶
Extends queries.Session using configuration data that is stored in environment variables.
Utilizes connection pooling to ensure that multiple concurrent asynchronous queries do not block each other. Heavily trafficked services will require a higher max_pool_size to allow for greater connection concurrency.
Parameters: - dbname (str) – PostgreSQL database name
- queries.cursor – The cursor type to use
- pool_idle_ttl (int) – How long idle pools keep connections open
- pool_max_size (int) – The maximum size of the pool to use
- db_url (str) – Optional database connection URL. Use this when you need to connect to a database that is only known at runtime.
- backend_pid¶
Return the backend process ID of the PostgreSQL server that this session is connected to.
Return type: int
- callproc(name, args=None)¶
Call a stored procedure on the server, returning the results in a queries.Results instance.
Parameters: Return type: queries.Results
Raises: queries.DataError
Raises: queries.DatabaseError
Raises: queries.IntegrityError
Raises: queries.InternalError
Raises: queries.InterfaceError
Raises: queries.NotSupportedError
Raises: queries.OperationalError
Raises: queries.ProgrammingError
- close()¶
Explicitly close the connection and remove it from the connection pool if pooling is enabled. If the connection is already closed
Raises: psycopg2.InterfaceError
- connection¶
The current open connection to PostgreSQL.
Return type: psycopg2.extensions.connection
- cursor¶
The current, active cursor for the open connection.
Return type: psycopg2.extensions.cursor
- encoding¶
The current client encoding value.
Return type: str
- notices¶
A list of up to the last 50 server notices sent to the client.
Return type: list
- pid¶
Return the pool ID used for connection pooling
Return type: str
- query(sql, parameters=None)¶
A generator to issue a query on the server, mogrifying the parameters against the sql statement. Results are returned as a queries.Results object which can act as an iterator and has multiple ways to access the result data.
Parameters: Return type: queries.Results
Raises: queries.DataError
Raises: queries.DatabaseError
Raises: queries.IntegrityError
Raises: queries.InternalError
Raises: queries.InterfaceError
Raises: queries.NotSupportedError
Raises: queries.OperationalError
Raises: queries.ProgrammingError
- class sprockets.clients.postgresql.TornadoSession(dbname, cursor_factory=<class 'psycopg2.extras.RealDictCursor'>, pool_idle_ttl=60, pool_max_size=25, io_loop=None, db_url=None)¶
Extends queries.TornadoSession using configuration data that is stored in environment variables.
Utilizes connection pooling to ensure that multiple concurrent asynchronous queries do not block each other. Heavily trafficked services will require a higher max_pool_size to allow for greater connection concurrency.
query and callproc must call Results.free
Parameters: - dbname (str) – PostgreSQL database name
- queries.cursor – The cursor type to use
- pool_idle_ttl (int) – How long idle pools keep connections open
- pool_max_size (int) – The maximum size of the pool to use
- ioloop (tornado.ioloop.IOLoop) – Pass in the instance of the tornado IOLoop you would like to use. Defaults to the global instance.
- db_url (str) – Optional database connection URL. Use this when you need to connect to a database that is only known at runtime.
- backend_pid¶
Return the backend process ID of the PostgreSQL server that this session is connected to.
Return type: int
- callproc(name, args=None)¶
Call a stored procedure asynchronously on the server, passing in the arguments to be passed to the stored procedure, yielding the results as a Results object.
You must free the results that are returned by this method to unlock the connection used to perform the query. Failure to do so will cause your Tornado application to run out of connections.
Parameters: Return type: Results
Raises: queries.DataError
Raises: queries.DatabaseError
Raises: queries.IntegrityError
Raises: queries.InternalError
Raises: queries.InterfaceError
Raises: queries.NotSupportedError
Raises: queries.OperationalError
Raises: queries.ProgrammingError
- close()¶
Explicitly close the connection and remove it from the connection pool if pooling is enabled. If the connection is already closed
Raises: psycopg2.InterfaceError
- connection¶
Do not use this directly with Tornado applications
Returns:
- encoding¶
The current client encoding value.
Return type: str
- notices¶
A list of up to the last 50 server notices sent to the client.
Return type: list
- pid¶
Return the pool ID used for connection pooling
Return type: str
- query(sql, parameters=None)¶
Issue a query asynchronously on the server, mogrifying the parameters against the sql statement and yielding the results as a Results object.
You must free the results that are returned by this method to unlock the connection used to perform the query. Failure to do so will cause your Tornado application to run out of connections.
Parameters: Return type: Results
Raises: queries.DataError
Raises: queries.DatabaseError
Raises: queries.IntegrityError
Raises: queries.InternalError
Raises: queries.InterfaceError
Raises: queries.NotSupportedError
Raises: queries.OperationalError
Raises: queries.ProgrammingError
- set_encoding(value='UTF8')¶
Set the client encoding for the session if the value specified is different than the current client encoding.
Parameters: value (str) – The encoding value to use
- validate()¶
Validate the session can connect or has open connections to PostgreSQL
Return type: bool