Tutorial

This tutorial will demonstrate all the functionality found in Momoko. It’s assumed a working PostgreSQL database is available, and everything is done in the context of a simple tornado web application. Not everything is explained: because Momoko just wraps Psycopg2, the Psycopg2 documentation must be used alongside Momoko’s.

Boilerplate

Here’s the code that’s needed for this tutorial. Each example will replace parts or extend upon this code. The code is kept simple and minimal; its purpose is just to demonstrate Momoko’s functionality. Here it goes:

from tornado import gen
from tornado.ioloop import IOLoop
from tornado.httpserver import HTTPServer
from tornado.options import parse_command_line
from tornado.web import *

import psycopg2
import momoko


class BaseHandler(RequestHandler):
    @property
    def db(self):
        return self.application.db


class TutorialHandler(BaseHandler):
    def get(self):
        self.write('Some text here!')
        self.finish()


if __name__ == '__main__':
    parse_command_line()
    application = Application([
        (r'/', TutorialHandler)
    ], debug=True)

    application.db = momoko.Pool(
        dsn='dbname=your_db user=your_user password=very_secret_password '
            'host=localhost port=5432',
        size=1
    )

    http_server = HTTPServer(application)
    http_server.listen(8888, 'localhost')
    IOLoop.instance().start()

For more information about all the parameters passed to momoko.Pool see momoko.Pool in the API documentation.

Usage

execute(), callproc(), transaction() and mogrify() are methods of momoko.Pool which can be used to query the database. (Actually, mogrify() is only used to escape strings, but it needs a connection). All these methods, except mogrify(), return a cursor or an exception object. All of the described retrieval methods in Psycopg2’s documentation—fetchone, fetchmany, fetchall, etc.—can be used to fetch the results.

All of the example will be using tornado.gen instead of callbacks, because callbacks are fairly simple and don’t require as much explanation. Here’s one example using a callback:

class TutorialHandler(BaseHandler):
    @asynchronous
    def get(self):
        self.db.execute('SELECT 1;', callback=self._done)

    def _done(self, cursor, error):
        self.write('Results: %r' % (cursor.fetchall(),))
        self.finish()

The callback only needs to accept two parameters: the cursor and an exception object. The exception object is either None or an instance of one of Psycopg2’s exceptions. That’s all there’s to know when using callbacks.

Instead of using tornado.gen directly (or using plain callbacks) Momoko provides subclasses of Task, Wait and WaitAll that have some advantages. These are Op, WaitOp and WaitAllOps. These three classes yield only a cursor and raise an exception when something goes wrong. Here’s an example using Op:

class TutorialHandler(BaseHandler):
    @gen.coroutine
    def get(self):
        try:
            cursor = yield momoko.Op(self.db.execute, 'SELECT 1;')
        except (psycopg2.Warning, psycopg2.Error) as error:
            self.write(str(error))
        else:
            self.write('Results: %r' % (cursor.fetchall(),))

        self.finish()

An example with WaitOp:

class TutorialHandler(BaseHandler):
    @gen.coroutine
    def get(self):
        self.db.execute('SELECT 1;', callback=(yield gen.Callback('q1')))
        self.db.execute('SELECT 2;', callback=(yield gen.Callback('q2')))
        self.db.execute('SELECT 3;', callback=(yield gen.Callback('q3')))

        try:
            cursor1 = yield momoko.WaitOp('q1')
            cursor2 = yield momoko.WaitOp('q2')
            cursor3 = yield momoko.WaitOp('q3')
        except (psycopg2.Warning, psycopg2.Error) as error:
            self.write(str(error))
        else:
            self.write('Q1: %r<br>' % (cursor1.fetchall(),))
            self.write('Q2: %r<br>' % (cursor2.fetchall(),))
            self.write('Q3: %r<br>' % (cursor3.fetchall(),))

        self.finish()

WaitAllOps can be used instead of three separate WaitOp calls:

try:
    cursor1, cursor2, cursor3 = yield momoko.WaitAllOps(('q1', 'q2', 'q3'))
except (psycopg2.Warning, psycopg2.Error) as error:
    self.write(str(error))
else:
    self.write('Q1: %r<br>' % (cursor1.fetchall(),))
    self.write('Q2: %r<br>' % (cursor2.fetchall(),))
    self.write('Q3: %r<br>' % (cursor3.fetchall(),))

All the above examples use execute(), but work with callproc(), transaction() and mogrify() too.

Advanced

Manual connection management

You can manually acquire connection from the pool using the getconn() method. This is very useful, for example, for server-side cursors.

It important to return connection back to the pool once you’ve done with it, even if an error occurs in the middle of your work. Use either putconn() method or manage() manager to return the connection.

Here is the server-side cursor example (based on the code in momoko unittests):

@gen.coroutine
def get(self):
    chunk = 1000
    try:
        connection = yield momoko.Op(self.db.getconn)
        with self.db.manage(connection):
            yield momoko.Op(connection.execute, "BEGIN")
            yield momoko.Op(connection.execute, "DECLARE all_ints CURSOR FOR SELECT * FROM unit_test_int_table")
            rows = True
            while rows:
                cursor = yield momoko.Op(connection.execute, "FETCH %s FROM all_ints", (chunk,))
                rows = cursor.fetchall()
                # Do something with results...
            yield momoko.Op(connection.execute, "CLOSE all_ints")
            yield momoko.Op(connection.execute, "COMMIT")
    except Exception as error:
        self.write(str(error))