Using the DB-API in Pylons

Unknown macro: {metadata-list}

Name	Using the DB-API in Pylons
Space	Pylons CookBook
Section	Home
Page	Using the DB-API in Pylons
Version	1.0
Status	Draft
Reviewed	False
Author(s)	James Gardner

In most cases you'll want to use SQLAlchemy instead, which does all this and a lot more. See Using SQLAlchemy with Pylons.

The DB-API is defined in PEP 249 at http://www.python.org/dev/peps/pep-0249/

Background

The DB-API is Python's API for connecting to a variety of databases. The basic DB-API usage might look something like this (for PostgreSQL):

import psycopg2
connection = psycopg2.connect(...options...)
cursor = connection.cursor()
cursor.execute(...sql...)
rows = cursor.fetchall()
for row in rows:
    print row
cursor.close()
connection.close()

This code makes a connection to the database, executes some SQL, fetches and prints the results then closes the connection. There are two potential problems with using this code in a Pylons controller action:

1. Pylons applications are designed to be loaded into memory and then executed in a multi threaded way so you need to ensure your database module is threadsafe.

2. If you were to use the above code in a controller action a new database connection would be created on each request which would slow things down.

The best solution is likely to be to use a DBUtils connection pool because this can be made to work with non-thread safe database modules but also provides a pool so that connections can be shared which reduces the amount of opening and closing of connections and therefore improves performance. Connections created in this way will also be automatically recreated if they are closed or the database server is restarted. Alternatively, you can use something like SQLAlchemy which handles this for you.

Creating A Persistent Pool

In your project's lib/app_globals.py file add these imports at the top:

1 2	import psycopg2 from DBUtils.PooledDB import PooledDB

You will need add DBUtils and psycopgy2 to your project's setup.py file:

install_requires = [
    ... other options... 
    "DBUtils==0.9.2",
    "psycopg2==2.0",
]

Then ensure they are installed by re-running:

python setup.py develop

You might need to install gcc, python-dev and libpq-dev packages in order to compile psycopg2

In the _init_() method of the Globals class add this (using whichever connection options are appropriate for your DB-API module and setup):

app_conf = config['app_conf']
self.pool = PooledDB(
    psycopg2,
    5, 
    db=app_conf['pool.database'], 
    user=app_conf['pool.user'], 
    host=app_conf['pool.host']
)

This code will setup a pool of 5 database connections.

You will also need to specify the config options used in your development.ini file:

[app:main]
use = egg:RMSForms
pool.database = database
pool.user = username
pool.host = localhost
# ... other options ...

Using The Pool

Now the pool should be available anywhere in your application as g.pool. You can use it in a controller action like this:

def index(self):
    conn = g.pool.connection()
    cur = conn.cursor()
    cur.execute("SELECT count(uid) from project;")
    response = Response(cur.fetchall())
    cur.close()
    conn.close()
    return response

The psycopg2 module does not auto commit your changes so you need to specify conn.commit() to commit them.

Warning: In a threaded environment such as Pylons, never do the following:

1	pool.connection().cursor().execute(...)

This would release the connection too early for reuse which may be fatal if the connections are not thread-safe. Make sure that the connection object stays alive as long as you are using it by following the example above.

That's it! You should now be able to produce fairly scalable database driven applications in Pylons.

PylonsHQ.

Using the DB-API in Pylons

Background

Creating A Persistent Pool

Using The Pool

Labels

Comments (1)

Nima Mohammadi says: