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.