Pylons tip #2: Using SQLite with Pylons

November 16, 2008 at 06:30 PM | categories: Technical, Python, Database | View Comments |

SQLite is an extremely useful little database. It has a nifty bunch of features and is super simple to set up. Using SQLite reduces the cost of developing and maintaining a powerful SQL database even more than traditional free RDBMS' like PostgreSQL and MySQL. Your database is simply an on-disk file - no need to configure user accounts, connection strings or any of that stuff. While this has its limitations, I have found that SQLite is more than sufficient for many web applications. These days I like to write my web applications in Pylons. Python has a full-featured SQLite module which conforms to DB-API 2.0. To use SQLite in a Pylons application, all you need to do is open the SQLite database file. I use a little convenience function to do this:

import sqlite3
def open_db():
    conn = sqlite3.connect(config['sqlite_db_file'])
    return conn
As you can see, the open_db() function references a configuration variable, sqlite_db_file. Instead of having to hard-code the location of the file in your source code, you can simply put it in your Pylons configuration file. to do this, simply add the settings to the app:man section of your INI-file - typically development.ini or test.ini:
use = egg:MyApp
full_stack = true
cache_dir = %(here)s/data
beaker.session.key = mykey
beaker.session.secret = somesecret
sqlite_db_file = /path/to/your/sqlite.db
And there you go!

Niall O'Higgins is an author and software developer. He wrote the O'Reilly book MongoDB and Python. He also develops Strider Open Source Continuous Deployment and offers full-stack consulting services at

blog comments powered by Disqus