Friday, September 15, 2006

Sqlite usage

This is a simple tutorial(?) to use sqlite. I used pysqlite 1.0.1 in Python 2.3,
which is the default in Debian 3.1 (Sarge).

The latest pysqlite version is 2.3.2, Visit this site for more info:
http://initd.org/tracker/pysqlite


"""sqlite_usage.py: sqlite module usage with examples

First import sqlite::

>>> import sqlite

Create a temporary directory::

>>> import tempfile
>>> tempdir = tempfile.mkdtemp()

Now create a db file::

>>> import os
>>> dbfile = os.path.join(tempdir, 'testdb.sdb')

Now connect to db and create a cursor::

>>> conn = sqlite.connect(dbfile)
>>> cr = conn.cursor()

Create a table::

>>> cr.execute("CREATE TABLE first_table (a INT)")

Insert a value and get::

>>> cr.execute("INSERT INTO first_table (a) values (1)")
>>> cr.execute("SELECT a FROM first_table")
>>> cr.fetchall()
[(1,)]

Test commit and rollback::

>>> conn.commit()
>>> cr.execute("DELETE FROM first_table")
>>> cr.execute("SELECT a FROM first_table")
>>> cr.fetchall()
[]
>>> #now rollback and query again
>>> conn.rollback()
>>> cr.execute("SELECT a FROM first_table")
>>> cr.fetchall()
[(1,)]

Insert more values as parameters::

>>> cr.execute("CREATE TABLE second_table (a INT, b VARCHAR(20))")
>>> cr.execute("INSERT INTO second_table (a, b) VALUES (%s, %s)",
... (1, 'hi1'))
>>> cr.execute("SELECT a, b FROM second_table")
>>> cr.fetchone()
(1, 'hi1')

Insert more values using executemany::

>>> cr.executemany("INSERT INTO second_table (a, b) VALUES (%s, %s)",
... [(2, 'hi2'), (3, 'hi3')])
>>> cr.execute("SELECT a, b FROM second_table")
>>> rst = cr.fetchall()
>>> (2, 'hi2') in rst
True
>>> (3, 'hi3') in rst
True

Hmm.. there is no rewind::

>>> cr.rewind
Traceback (most recent call last):
...
AttributeError: rewind

Views are supported::

>>> cr.execute("CREATE VIEW first_second_view \
AS SELECT a.a AS a1, b.a AS a2, b.b \
FROM first_table AS a LEFT JOIN second_table AS b \
ON a.a = b.a")
>>> cr.execute("SELECT a1, a2, b FROM first_second_view")
>>> cr.fetchone()
(1, 1, 'hi1')

Cleanup tempdir::

>>> import shutil
>>> shutil.rmtree(tempdir)

"""

def _test():
import doctest
return doctest.testmod()

if __name__ == '__main__':
_test()
pass