Peter's Blog

Been doing some python programming. Since my hassles I am now getting to grips with python 2.5 and the first thing I did required a fast database so I reached for sqlite. Looking on the pysqlite website there was no build for python 2.5. Odd. On a hunch/half remembered thing I simply tried it from the python 2.5 console:

import sqlite3

and there it was, it comes with the python standard library. Cool. The interface follows standard python dbi so it's easy for me to use.

   1  import sqlite3
   2  
   3  oDB = sqlite3.connect( "c:\\desktop\\my database")
   4  oCursor = oDB.cursor()
   5  oCursor.execute( "Select * from table")
   6  while 1:
   7      oRow = oCursor.fetchone()
   8      if not oRow:
   9          break
  10      print oRow
  11  

It's fast, the job I am doing involves creating tens of thousands of records and sqlite is managing about 60,000 in a minute (1.1GHz AMD cpu). This is writing to disk files, creating the database in memory wasn't much faster (probably because my pc has only 512M and is swapping).

Sqlite is a straight database library, python is calling it directly via function calls. There is no inter-process communications, no COM overheads, no network overheads, this is pretty much as fast as databases get.

It is still SQL so there is still the overhead of parsing sql for the database to perform. Something I am getting into is this syntax:

strFirstName = "Fred"
strSurname = "Bloggs"

oCursor.execute( """SELECT * from table
                    WHERE firstname = ?, surname = ?""",
                   (strFirstName, strSurname))

The ?s get replaced with the parameters in the tuple. This is great as it saves a lot of messing around with quoting strings and escapes and whatever and it makes you more resistant to sql injection attacks, should that worry you at all. This technique has got to be faster than building sql strings holding the parameter values, passing them to sqlite and it having to parse them out again. Everybody wins.