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