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'}