Using SQLite in Python

Python has had support for SQLite built-in since version 2.5.

This is a very convenient pairing as SQLite is an excellent lightweight SQL implementation that I find very useful for a variety of tasks e.g. data mining.  Or any task involving manipulating complex data sets where I’d otherwise end up resorting to using a full blown SQL server like MySQL.

Here is a simple example of using SQLite in Python using it’s built-in sqlite3 module:

import sqlite3

# craete a connection
con = sqlite3.connect('test.db')

# create a cursor
cur = con.cursor()

# create a test table
cur.execute( "CREATE TABLE testTable (myKey INT, myValue INT)" )

# insert some data
for i in range(0,10):
 cur.execute( "INSERT INTO testTable VALUES ( %d, %d )"%(i,i*i) )

# select the data
for row in cur.execute( "SELECT * FROM testTable" ):
 print row

# destroy (drop) our test table
cur.execute( "DROP TABLE testTable" )

# close the connection
con.close()

As you can see Python makes handling SQLite (a C language library) much easier, less error prone, and the resulting code much more compact than SQLite’s native C.