Module usage

Basic usage of the module is not very different from using Twisted’s adbapi:

from txpostgres import txpostgres

from twisted.internet import reactor
from twisted.python import log, util

# connect to the database
conn = txpostgres.Connection()
d = conn.connect('dbname=postgres')

# run the query and print the result
d.addCallback(lambda _: conn.runQuery('select tablename from pg_tables'))
d.addCallback(lambda result: util.println('All tables:', result))

# close the connection, log any errors and stop the reactor
d.addCallback(lambda _: conn.close())
d.addErrback(log.err)
d.addBoth(lambda _: reactor.stop())

# start the reactor to kick off connection estabilishing
reactor.run()

If you want you can use the Cursor class directly, with a interface closer to Psycopg. Note that using this method you have to make sure never to execute a query before the previous one finishes, as that would violate the PostgreSQL asynchronous protocol.

from txpostgres import txpostgres

from twisted.internet import reactor
from twisted.python import log, util

# define the libpq connection string and the query to use
connstr = 'dbname=postgres'
query = 'select tablename from pg_tables order by tablename'

# connect to the database
conn = txpostgres.Connection()
d = conn.connect('dbname=postgres')

def useCursor(cur):
    # execute a query
    d = cur.execute(query)
    # fetch the first row from the result
    d.addCallback(lambda _: cur.fetchone())
    # output it
    d.addCallback(lambda result: util.println('First table name:', result[0]))
    # and close the cursor
    return d.addCallback(lambda _: cur.close())

# create a cursor and use it
d.addCallback(lambda _: conn.cursor())
d.addCallback(useCursor)

# log any errors and stop the reactor
d.addErrback(log.err)
d.addBoth(lambda _: reactor.stop())

# start the reactor to kick off connection estabilishing
reactor.run()

Using transactions

Every query executed by txpostgres is committed immediately. If you need to execute a series of queries in a transaction, use the runInteraction() method:

from txpostgres import txpostgres

from twisted.internet import reactor
from twisted.python import log

# connect to the database
conn = txpostgres.Connection()
d = conn.connect('dbname=postgres')

# define a callable that will execute inside a transaction
def interaction(cur):
    # the parameter is a txpostgres Cursor
    d = cur.execute('create table test(x integer)')
    d.addCallback(lambda _: cur.execute('insert into test values (%s)', (1, )))
    return d

# run the interaction, making sure that if the insert fails, the table won't be
# left behind created but empty
d.addCallback(lambda _: conn.runInteraction(interaction))

# close the connection, log any errors and stop the reactor
d.addCallback(lambda _: conn.close())
d.addErrback(log.err)
d.addBoth(lambda _: reactor.stop())

# start the reactor to kick off connection estabilishing
reactor.run()

Customising the connection and cursor factories

You might want to customise the way txpostgres creates connections and cursors to take advantage of Psycopg features like dictionary cursors. To do that, define a subclass of Connection and override connectionFactory or cursorFactory class attributes to use your custom code. Here’s an example of how to use dict cursors:

import psycopg2
import psycopg2.extras
from txpostgres import txpostgres

from twisted.internet import reactor
from twisted.python import log, util


def dict_connect(*args, **kwargs):
    kwargs['connection_factory'] = psycopg2.extras.DictConnection
    return psycopg2.connect(*args, **kwargs)


class DictConnection(txpostgres.Connection):
    connectionFactory = staticmethod(dict_connect)


# connect using the custom connection class
conn = DictConnection()
d = conn.connect('dbname=postgres')

# run a query and print the result
d.addCallback(lambda _: conn.runQuery('select * from pg_tablespace'))
# access the column by its name
d.addCallback(lambda result: util.println('All tablespace names:',
                                          [row['spcname'] for row in result]))

# close the connection, log any errors and stop the reactor
d.addCallback(lambda _: conn.close())
d.addErrback(log.err)
d.addBoth(lambda _: reactor.stop())

# start the reactor to kick off connection estabilishing
reactor.run()

Listening for database notifications

Being an asynchronous driver, txpostgres supports the PostgreSQL NOTIFY feature for sending asynchronous notifications to connections. Here is an example script that connects to the database and listens for notifications on the list channel. Every time a notification is received, it interprets the payload as part of the name of a table and outputs a list of tables with names containing that payload.

from txpostgres import txpostgres

from twisted.internet import reactor
from twisted.python import util


def outputResults(results, payload):
    print "Tables with `%s' in their name:" % payload
    for result in results:
        print result[0]


def observer(notify):
    if not notify.payload:
        print "No payload"
        return

    query = ("select tablename from pg_tables "
             "where tablename like '%%' || %s || '%%'")
    d = conn.runQuery(query, (notify.payload, ))
    d.addCallback(outputResults, notify.payload)


# connect to the database
conn = txpostgres.Connection()
d = conn.connect('dbname=postgres')

# add a NOTIFY observer
conn.addNotifyObserver(observer)
# start listening for NOTIFY events on the 'list' channel
d.addCallback(lambda _: conn.runOperation("listen list"))
d.addCallback(lambda _: util.println("Listening on the `list' channel"))

# process events until killed
reactor.run()

To try it execute the example code and then open another session using psql and try sending some NOTIFY events:

$ psql postgres
psql (9.1.2)
Type "help" for help.

postgres=> notify list, 'user';
NOTIFY
postgres=> notify list, 'auth';
NOTIFY

You should see the example program outputting lists of table names containing the payload:

$ python notify_example.py
Listening on the `list' channel
Tables with `user' in their name:
pg_user_mapping
Tables with `auth' in their name:
pg_authid
pg_auth_members