pyscopg2 and connection pooling, v1

10 Sep 2008

This blog entry is certainly to be "version 1", with updates to follow

Again, why is finding information on something so basic, so hard to find? Anyways, the short answer is this:

# we need the 'with' statement, python 2.5 or greater
from __future__ import with_statement
from contextlib import contextmanager

import psycopg2
import psycopg2.pool

# create pool with min number of connections of 1, max of 10
a = psycopg2.pool.SimpleConnectionPool(1,10,database='YOURDB', otherstuff...)

@contextmanager
def getcursor():
    con = a.getconn()
    try:
        yield con.cursor()
    finally:
        a.putconn(con)

That's it. To use it do something like this:

with getcursor() as cur
    cur.execute("select count(*) from foo")
    # do something with result

# all done, other code goes here

About psycopg2's pools

Crude infomation on the pooling code can be found with

 pydoc psycopg2.pool

From this you can see three pool types:

  • SimpleConnectionPool
  • PersistantConnectionPool
  • ThreadedConnectionPool

I'm haven't studied which one is appropriate for different server types and configurations (fcgi, mod_wsgi, mod_python)

The "constructor" for all them are the same. The first and second arguments are the minimum and maximum number of connections. The remaining arguments are the same ones you use in psycopg2.connect. To get a connection, call getconn, then when you are done, you must return it with putconn. That is kinda clunky. Using with and contextmanger makes it easy. How that works is another article.

Performance

You know already, but

imax = 1000
def withpool():
    for i in xrange(imax):
        with getcursor() as cur:
            cur.execute("select 1")

def withoutpool():
    for i in xrange(imax):
        con = psycopg2.connect(database='geocode')
        cur = con.cursor()
        cur.execute("select 1")
        con.close()

has the results of

         18003 function calls in 0.679 CPU seconds


versus


3003 function calls in 18.232 CPU seconds


Comment 2008-09-21 by None

Thanks for this tip !


Comment 2011-10-02 by None

Still the best example on the Internet and its 2011 now...


Comment 2011-10-02 by None

Still the best example around and its now 2011...


Comment 2012-02-26 by None

This is cool. I tweaked this so that I don't have to worry about committing my changes or closing the connections as well:

import psycopg2
from contextlib import contextmanager

@contextmanager
def connect(dsn):
connection = psycopg2.connect(dsn)
cursor = connection.cursor()
try:
yield cursor
connection.commit()
except:
connection.rollback()
finally:
connection.close()