| Name |
Space |
Section |
Page |
Version |
Status |
Reviewed |
Author(s) |
| Using the DB-API in Pylons |
Pylons CookBook |
Home |
Using the DB-API in Pylons |
1.0 |
Draft |
False |
 James Gardner |
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):
1
2
3
4
5
6
7
8
9 | 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:
1
2
3
4
5 | install_requires = [
... other options...
"DBUtils==0.9.2",
"psycopg2==2.0",
]
|
Then ensure they are installed by re-running:
 | 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):
1
2
3
4
5
6
7
8 | app_conf = config['app_conf']
self.pool = PooledDB(
psycopg2,
5,
database=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:
1
2
3
4
5
6 | [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:
1
2
3
4
5
6
7
8 | 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.
