Having Fun with PySQLite

I had a chance to test pysqlite, a Python wrapper for SQLite Database System. pysqlite needs the following dependencies :

• Operating System and C Compiler
• SQLite version 3.0.8 or later (for pysqlite 2.2.0)
• Python 2.3 or later
  

After download the latest version (2.2.2) I did the followings to install pysqlite to my system :

$ tar xvzpf pysqlite-2.2.2.tar.gz
pysqlite-2.2.2/
pysqlite-2.2.2/doc/
pysqlite-2.2.2/doc/code
...
pysqlite-2.2.2/setup.cfg
pysqlite-2.2.2/setup.py
pysqlite-2.2.2/PKG-INFO

$ cd pysqlite-2.2.2/

$ python setup.py build
running build
running build_py
creating build
creating build/lib.linux-i686-2.4
creating build/lib.linux-i686-2.4/pysqlite2
...

# python setup.py install
running install
running build
running build_py
running build_ext
running install_lib
...

In my system, the above command will install pysqlite to /usr/lib/python2.4/site-packages directory

Next I test whether the installation success or not :

$ python
Python 2.4 (#1, Mar 22 2005, 21:42:42)
[GCC 3.3.5 20050117 (prerelease) (SUSE Linux)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import test
>>> test.test()
......................................................................
----------------------------------------------------------------------
Ran 164 tests in 1.060s
OK
>>>

Next I type several lines of Python code to test PySQLite. Here is the code. Please beware this is just a simple application :

#!/usr/bin/env python

from pysqlite2 import dbapi2 as sqlite
import os

DB_FILE = "mydb"

musics = [
    ("Michael W Smith", "In My Arms Again"),
    ("Chayanne", "Ye Te Amo"),
    ("Marc Anthony", "Everything You Do")
    ]

if os.path.exists(DB_FILE):
    os.remove(DB_FILE)

con = sqlite.connect(DB_FILE)

# create table
con.execute("""
    create table music
    (
        singer  varchar(30),
        song    varchar(30)
    )
    """)
print "Success creating table",'"%s"' % DB_FILE

# insert table
con.executemany("insert into music(singer,song) values (?,?)", musics)

# print table contents
print "\nThe content of", DB_FILE
print "=" * 20,"\n"

for row in con.execute("select singer,song from music"):
    print '%s : %s' % (row[0],row[1])

con.close()