Niall's Weblog

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:

1
2
3
4

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:

1
2
3
4
5
6
7

[app:main]
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!