Using Queries

Queries provides both a session based API and a stripped-down simple API for interacting with PostgreSQL. If you’re writing applications that will only have one or two queries, the simple API may be useful. Instead of creating a session object when using the simple API methods (queries.query() and queries.callproc()), this is done for you. Simply pass in your query and the URIs of the PostgreSQL server to connect to:

queries.query("SELECT now()", "postgresql://postgres@localhost:5432/postgres")

Queries built-in connection pooling will re-use connections when possible, lowering the overhead of connecting and reconnecting. This is also true when you’re using Queries sessions in different parts of your application in the same Python interpreter.

Connection URIs

When specifying a URI, if you omit the username and database name to connect with, Queries will use the current OS username for both. You can also omit the URI when connecting to connect to localhost on port 5432 as the current OS user, connecting to a database named for the current user. For example, if your username is fred and you omit the URI when issuing queries.query() the URI that is constructed would be postgresql://fred@localhost:5432/fred.

If you’d rather use individual values for the connection, the queries.uri() method provides a quick and easy way to create a URI to pass into the various methods.

queries.uri(host='localhost', port=5432, dbname='postgres', user='postgres', password=None)[source]

Return a PostgreSQL connection URI for the specified values.

Parameters:
  • host (str) – Host to connect to
  • port (int) – Port to connect on
  • dbname (str) – The database name
  • user (str) – User to connect as
  • password (str) – The password to use, None for no password
Return str:

The PostgreSQL connection URI

Examples

The following examples demonstrate various aspects of the Queries API. For more detailed examples and documentation, visit the simple, Session API, Query Results, and TornadoSession Asynchronous API pages.

Using queries.uri to generate a URI from individual arguments

>>> queries.uri("server-name", 5432, "dbname", "user", "pass")
'postgresql://user:pass@server-name:5432/dbname'

Using the queries.Session class

To execute queries or call stored procedures, you start by creating an instance of the queries.Session class. It can act as a context manager, meaning you can use it with the with keyword and it will take care of cleaning up after itself. For more information on the with keyword and context managers, see PEP 343.

In addition to both the queries.Session.query() and queries.Session.callproc() methods that are similar to the simple API methods, the queries.Session class provides access to the psycopg2 connection and cursor objects.

Using queries.Session.query

The following example shows how a queries.Session object can be used as a context manager to query the database table:

>>> import pprint
>>> import queries
>>>
>>> with queries.Session() as s:
...     for row in s.query('SELECT * FROM names'):
...         pprint.pprint(row)
...
{'id': 1, 'name': u'Jacob'}
{'id': 2, 'name': u'Mason'}
{'id': 3, 'name': u'Ethan'}

Using queries.Session.callproc

This example uses queries.Session.callproc() to execute a stored procedure and then pretty-prints the single row results as a dictionary:

>>> import pprint
>>> import queries
>>> with queries.Session() as session:
...   results = session.callproc('chr', [65])
...   pprint.pprint(results.as_dict())
...
{'chr': u'A'}