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
transaction() context to commit only when the context manager exits (equivalent to wrapping your code with
with db.t() as t: t.q('select name, age from people')
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.
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.
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()