Exploring Python SQL Module sqlite3

''' sqlite3_test1.py
test the sqlite3 module, write and read a database file
note that Python25 and higher versions have sqlite3 builtin

sqlite3.connect(database, timeout=5.0, isolation_level=None,
    detect_types=0, factory=100)

timeout=5.0 --> allows multiple access for 5 seconds (for servers)
isolation_level=None -->  autocommit mode
detect_types=0 --> native types TEXT, INTEGER, FLOAT, BLOB and NULL
factory=100 --> statement cache to avoid SQL parsing overhead

tested with Python273, IronPython273 and Python33  by vegaseat  08jan2013
'''

import sqlite3

# create/connect to a permanent file database
con = sqlite3.connect("my_db.db3")
# for temporary testing you can use memory only
#con = sqlite3.connect(":memory:")

# establish the cursor, needed to execute the connected db
cur = con.cursor()

# query language in upper case is optional
# create/execute a table:
cur.execute('CREATE TABLE IF NOT EXISTS clients \
    (id INT PRIMARY KEY, \
    firstname CHAR(60), \
    lastname CHAR(60))')

# insert several lines at once using a
# list of (id, firstname, lastname) tuples
# use try/except or the existing db will complain about
# the non-unique id since it is already in the db
try:
    clients = [
    (107, "Ella", "Fitzgerald"),
    (108, "Louis", "Armstrong"),
    (109, "Miles", "Davis")
    ]
    cur.executemany("INSERT INTO clients (id, firstname, lastname) \
        VALUES (?, ?, ?)", clients )
except:
    pass

# add another client
# again, use try/except or the existing db will complain about
# the non-unique id if it is already in the db
try:
    new_client = (110, "Benny", "Goodman")
    cur.execute("INSERT INTO clients (id, firstname, lastname) \
        VALUES (?, ?, ?)", new_client)
except:
    pass

# important if you make changes to the database
# commits current data to the db file (data is persistent now)
# if the file given in connect() does not exist, it will be created
con.commit()

# now test it
# get data row by row
print("Show data row by row:")
# also tell it to sort/order data by lastname
cur.execute('SELECT id, firstname, lastname FROM clients \
    ORDER BY lastname')
for row in cur:
    print(row)

print('-'*40)

# select just one data item from each row ...
cur.execute('SELECT firstname FROM clients')
print(cur.fetchall())

print('-'*40)

# or ...
cur.execute('SELECT firstname FROM clients')
for row in cur:
    print(row[0])

print('-'*40)

# select a specific data row ...
cur.execute('SELECT * FROM clients WHERE lastname="Davis"')
print(cur.fetchall())

# finally ...
con.close()

''' result (Python33) ...
Show data row by row:
(108, 'Louis', 'Armstrong')
(109, 'Miles', 'Davis')
(107, 'Ella', 'Fitzgerald')
(110, 'Benny', 'Goodman')
----------------------------------------
[('Ella',), ('Louis',), ('Miles',), ('Benny',)]
----------------------------------------
Ella
Louis
Miles
Benny
----------------------------------------
[(109, 'Miles', 'Davis')]
'''