24th May, 2008

Sqlite in Python

Sqlite is a small, standalone SQL based database engine. As long as you know SQL, you can get get started with a small file based database in seconds.

I haven't stress tested it yet, but here's some basics:

from pysqlite2 import dbapi2

connection = dbapi2.connect("test.db")

or to create a database in memory

connection = dbapi2.connect(":memory:")

The cursor object provides SQL command access

cursor = connection.cursor()

From here, cursor can run queries

cursor.execute(sql)

Run queries with sanitized inputs

cursor.execute('insert into table values (?,?)', (item1, item2))

Get last index

cursor.lastrowid

To commit changes

connection.commit()

or rollback

connection.rollback()

Get all data as a list

cursor.fetchall()

After running a select, a cursor returns an iterator for each row

for loopItem in cursor:
    print loopItem[column]

You can do top 10 * style operations with

cursor.fetchone()

and

cursor.fetchmany(10)

 

The opinions expressed here are my own and not those of my employer.