Selecting & querying

One-off statements (immediate commit)

Run queries right from a database object if you want to run them in a single-statement transaction.

db.q('select name, age from people')
# name  age 
# ----- ---
# Alice 33
# Bob   44

Explicit transactions

Create a transaction() context to commit only when the context manager exits (equivalent to wrapping your code with BEGIN...COMMIT)

with db.t() as t:
    t.q('select name, age from people')

Connection pooling

Connections created via a db object use a connection pool for greater efficiency. They are thread-safe, such that two threads won't use the same connection.

Autocommit mode

Certain statements, like create database... and create index concurrently... cannot be run in a transaction.

These can be run in autocommit mode like so.

db.autocommit('create database example')

Auto-commit statements aren't connection-pooled - a fresh connection is created each time.

Query parameters

Parameterize queries with colon-prefixed names in the SQL statement:

t.q(
    """
    select name, age from people
    where name = :name
    """,
    dict(name='Alice')
)

Pass in the parameter values as a dictionary.

Don't worry - regular colon usage, such as casts, such as '50'::decimal won't get confused for parameters

Parameters from the code context

Instead of explicitly passing in values, you simply use variables from the local code context, as follows:

name = 'Alice'

t.q(
    """
    select name, age from people
    where name = :name
    """,
    context=True
)

The rows object

Queries return a Rows( object, which mostly behaves like a regular list, but with nicer printing and a few other features.

Individual row objects behave partly like a list of values, but also allow attribute-based and key-based access, like so:

# by attribute
row.name

# by key
row['name']

For greater efficiency with large numbers of rows, key information is shared between rows. If you need to modify row keys, it's best to convert the row data to real dictionaries:

rows_as_dicts = rows.as_dicts()