Sqlite with Python

2008-03-10

Python 2.5 has a SQLite library. The documentation says it best what this can be used for :

SQLite is a C library that provides a lightweight disk-based database
that doesn't require a separate server process and allows accessing
the database using a nonstandard variant of the SQL query language.
Some applications can use SQLite for internal data storage. It's also
possible to prototype an application using SQLite and then port the
code to a larger database such as PostgreSQL or Oracle.

The following code creates the database.

import sqlite3

  conn = sqlite3.connect('feeds.db')

  c = conn.cursor()

  # Create table
  c.execute('''create table feeds (date text, url text)''')

Adding data can be done as follows. This adds a row to the feeds table, with the current time as a string in the date column and a HTML link in the url field.

import sqlite3, time

  conn = sqlite3.connect('feeds.db')
  conn.isolation_level = None
  c = conn.cursor()

  # Insert a row of data
  data = ( time.strftime("%a, %d %b %Y %H:%M:%S +0000",
                                time.localtime(None) ),
              'http://www.sqlite.org/sqlite.html' )
  c.execute("""insert into feeds values ('%s','%s')""" % data )

Most Linux systems have a command line SQLite client, called sqlite3 for SQLite version 3. You can use this to open the database created in Python.

$ sqlite3 feeds.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> select * from feeds;
Mon, 10 Mar 2008 14:49:11 +0000|http://www.sqlite.org/sqlite.html
sqlite> .quit

Cool, he ?