PostgreSQL Database Access

Nucleon includes a wrapper for the psycopg2 PostgreSQL driver that shares a pool of database connections between greenlets. The number of open database connections is capped for performance.

Making database queries

A PostgreSQL connection pool can be retreived from each app’s Application object.

When a greenlet wishes to make a database request, it “borrows” a connection from the pool. A context manager interface ensures that the connection is returned to the pool when the greenlet no longer needs it.

High-level API

A higher level API is available for pre-defining queries that can be executed later. This is intended to save boilerplate and allow queries to be defined in one place - by convention, a separate

The style of the API is largely declarative; queries can be declared in SQL syntax but can be used as Python callables. For example, declaring and using a query might work as follows:

>>> db = Database('database')
>>> get_customer ="SELECT id, name FROM customers WHERE id=%s")
>>> get_customer(52).unique
{'id': 52, 'name': 'Leonard Winter'}

The entry point to this high-level API is the Database class, which wraps a PostgreSQL connection corresponding to a setting defined in the application settings file.

When performing a query, the return value is an object that allows transformation of the results into simple Pythonic forms.

A results instance is also iterable; iterating it is equivalent to iterating .rows, except that it does not build a list of all results first.

Transaction Functions

Sometimes we want to do more processing in Python than is possible using the above approach - we may need to take results into Python code, operate on them, and perform database actions as a result.

We can do this using a “transaction function” - effectively a block of code in which database queries are either all committed or all rolled back. This is written as a decorated function whose first position argument is a callable that can be used to perform queries within the transaction context:

db = Database('database')

def add_customer(q, name)
    return q('INSERT INTO customers(name) VALUES(%s)', name)

Additionally, it is possible to specify that such a transaction be automatically retried a finite number of times - this is useful if there can be integrity problems but the chances of such are relatively low.

For example, we can write a transaction to insert a record with the next highest id value as follows (assume id has a uniqueness constraint):

def insert_value(q):
    lastid = q('SELECT max(id) FROM test').value
    return q(
        'insert into test(id, name) values(%s, %s)',
        lastid + 1, 'a%s' % lastid

This would be retried up to three times (4 attempts total) if there was an integrity error (ie. another client inserts the same id between the SELECT and the INSERT.