python sqlite3 test code

sqlite_test.py

'''
C:\temp\sqlite> sqlite3 test.db "create table location (lid integer primary key, lname text);"
C:\temp\sqlite> sqlite3 test.db "insert into location (lid, lname) values (0, "Waitara, NSW");"
Software\sqlite3.exe: Error: too many options: "Waitara"
 -help for a list of options.
C:\temp\sqlite> sqlite3 test.db "insert into location (lid, lname) values (0, 'Waitara, NSW');"
C:\temp\sqlite> sqlite3 test.db "insert into location (lid, lname) values (1, 'Hornsby, NSW');"
C:\temp\sqlite> sqlite3 test.db "insert into location (lid, lname) values (2, 'Rhodes, NSW');"
C:\temp\sqlite> sqlite3 test.db "insert into location (lid, lname) values (3, 'Strathfield, NSW');"
C:\temp\sqlite>
C:\temp\sqlite> ls


 Directory: C:\temp\sqlite


e                LastWriteTime     Length Name
-                -------------     ------ ----
--        20/06/2012   3:55 PM       2048 test.db
--        20/06/2012   3:46 PM        435 test.py


C:\temp\sqlite> sqlite3 test.db "select * from location;"
aitara, NSW
ornsby, NSW
hodes, NSW
trathfield, NSW
C:\temp\sqlite> sqlite3 test.db "select lname from location where lid = 1;"
nsby, NSW
C:\temp\sqlite> data = cur.fetchone()
 Data section is missing its statement block.
line:1 char:6
ata  <<<< = cur.fetchone()
 + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
 + FullyQualifiedErrorId : MissingStatementBlockForDataSection

C:\temp\sqlite>
C:\temp\sqlite>

'''


import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect('test.db')
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print "SQLite version: %s" % data

    cur.execute('select lname from location where lid=1')
    data = cur.fetchone()

    #getting rowcount
    cur.execute('select count(*) from location')
    #fetchone() returns dictionary
    row_count = cur.fetchone()[0]        
    
    print "row_count: %d" % row_count

    location = 'Gosford'
    insertStmt = " insert into location (lid, lname) values ({}, '{}, NSW');".format(row_count, location)
    print insertStmt
    cur.execute(insertStmt)
    
    
except lite.Error, e:
    
    print "Error %s:" % e.args[0]
    sys.exit(1)
    
finally:
    
    if con:
        con.close()