Python and SQLite

Often, I find myself writing a script or application where I want to do some quick data extraction and analysis using a database, but where it’d be overkill to set up a MySQL schema to store the data. So, enter my recently-beloved Python and the sqlite3 module.

SQLite has the advantage of storing data in a single flat file – which is fine if you’re creating something to be used locally, and by a single user. Plus, if you’re using Python 2.5 or above, pysqlite2 comes pre-bundled, so all you need to do is:

from sqlite3 import *

Then it’s just a case of creating your tables, popping in some data, and performing some queries. Simples. Firstly, create a connection object and a cursor to go with it:

connection = connect('data.db')
cursor = connection.cursor()

Then it’s just a case of creating some tables:

cursor.execute("CREATE TABLE person\
    (id int primary key, firstname text, lastname text)")

Putting some data in them:

cursor.execute("INSERT INTO person VALUES (null, 'Joe', 'Bloggs')")
connection.commit()

…and then querying them:

cursor.execute('SELECT * FROM person')
for row in cursor:
    print row

Anyway — that’s my first go at writing a quick and dirty tutorial, if you find it helpful or you think I’ve missed anything out then leave me a comment.