Using SQLite in Python

by Phillip Watts

July 16, 2009

Introduction

SQLite is the most ubiquitous of all database engines. Python is used for tens of thousands of applications of all kinds. A joining (pun intended) of these two excellent tools, should be of interest to many application developers.

From the SQLite website:

"The basic rule of thumb for when it is appropriate to use SQLite is this: Use SQLite in situations where simplicity of administration, implementation, and maintenance are more important than the countless complex features that enterprise database engines provide. As it turns out, situations where simplicity is the better choice are more common than many people realize."

I could not agree more. I have used MySQL and PostgreSQL. They are excellent products,but I have found that even in an application as complex as client/server POS, SQLite is more than adequate, and in fact better.

There are far more installations of SQLite than all other SQL servers combined. This is due to, simplicity, small footprint, dedicated open source development community, and the simplest and most open of licenses. SQLite is embedded in Adobe Photoshop, Firefox, Google Gears, Skype clients, and countless appliances. If you have a car, a cellphone and a television receiver, you are probably running SQLite (and maybe Python).

If you have Python 2.5+, then you have SQLite. Otherwise: Download SQLite3

This is not a tutorial on SQL, or a sales pitch for relational database management. This article will get you started in using SQLite in the Python context.

Let us create an empty database. And by the way, I am not an advocate of import *, it just makes writing a tutorial easier.

#!/usr/bin/env python
from sqlite3 import *

conn = connect('sample.db')
curs = conn.cursor()

This creates an empty SQLite database in the current directory, or connects to one previously created. The cursor - curs-, is simply the object instance which directs your query-like methods to the particular database.

Now let us create a table called "item":

# Create Item table
curs.execute('''create table item
  (id integer primary key, itemno text unique,
        scancode text, descr text, price real)''')

This statement causes a table to be created with a primary key, a unique item no., and three more fields. If you were to interact directly with SQLite from the command line, it would look something like this:

$ sqlite3 sample.db
SQLite version 3.3.13
Enter ".help" for instructions
sqlite>  create table item (id integer primary key, itemno text unique,
   ...>         scancode text, descr text, price real);

As you can see, our sample Python code passes a "create table" statement, via the execute() method. The argument to the execute method is the string that we would key in interactive mode, without the semicolon.

Using SQLite in Python
Using SQLite in Python - Cont.