Tuesday, May 5, 2009

little python sqlite tutorial

Sqlite is an embedded database engine that has bindings in a lot of popular programming languages (Perl, C++ and most notably Python). Sqlite3 is part of the standard Python library so there's no need to muck about with installation.

Here's my mini tutorial to using sqlite3 in Python:

1. First thing you have to do is import the module so:

import sqlite3

2. Next is to connect to a 'database' which is in reality a single file in the filesystem. Note that this will be created in the working directory of your current process (in this case the Python interpreter). Let's use an absolute path to make it clear:

conn = sqlite3.connect( '/yourpath/exampledb' )

3. Once you have a connection, you probably will create a cursor. You need a cursor object to use its execute method to perform SQL commands:

cur = conn.cursor()

4. Run your SQL commands. Note that like most database engines, sqlite3 supports its particular dialect of SQL. Check it out here: SQL As Understood by SQLite.

Here are some examples:

- to create a table:

cur.execute( '''CREATE TABLE books (title text, author text)''' )

- to insert a row of data:

cur.execute( '''INSERT INTO books VALUES ('Judas Unchained', 'Hamilton, Peter' )''' )

5. Most importantly, to save your data, issue the following command:


6. To close the database:


That's it! :)

Here are some more Python links:

Here are some online docs:
Python docs for the sqlite3 Python bindings: DB-API 2.0 interface for SQLite databases

SQLite homepage is here.


Anonymous said...

That was helpful! I was too lazy to read the manual, with your example i could start playing right away.

raoul said...

You're welcome! Glad it helped. :)

Anonymous said...

another little tip... to extend the above to persistance and retrieval of any arbitrary Python object with compression, import the y_serial module which is open source at http://yserial.sourceforge.net

It uses SQLite and is fast, very easy to implement in less than 10 minutes.

Anonymous said...

Hi, I am trying to run a query on Firefox sqlite3 db.

import sqlite3
c = db.cursor()
c.execute(query )
table_info = c.fetchall()

the connection is good but when I execute the query I get

DatabaseError: file is encrypted or is not a database