import sqlite3
import sys

# Create an example database and table ...

timespend=2

if len(sys.argv) > 1:
    if sys.argv[1] == "seed":
        conn = sqlite3.connect('mysample.db') # name of the file to hold the database

        c = conn.cursor()
        c.execute("CREATE TABLE course (cid int, name text, days int)")
        c.execute('INSERT INTO course VALUES(1,"Python Programming",3)')
        c.execute('INSERT INTO course VALUES(2,"Intermediate Python",3)')
        c.execute('INSERT INTO course VALUES(3,"Feeding your Python",2)')
        conn.commit()

        c = conn.cursor()
        c.execute("CREATE TABLE endday (eid int, dayname text)")
        c.execute('INSERT INTO endday VALUES(1,"Monday")')
        c.execute('INSERT INTO endday VALUES(2,"Tuesday")')
        c.execute('INSERT INTO endday VALUES(3,"Wednesday")')
        c.execute('INSERT INTO endday VALUES(4,"Thursday")')
        c.execute('INSERT INTO endday VALUES(5,"Friday")')
        conn.commit()
    else:
        timespend = int(sys.argv[1])

# Then - (default use) use the data

conn = sqlite3.connect('mysample.db') # name of the file to hold the database
c = conn.cursor()

print "Sample select using a program generated query"
rows = 0
gimme = c.execute("SELECT name, days FROM course WHERE days > " + str(timespend))
for gotme in gimme:
        print gotme
        rows += 1
print "Total of",rows,"records selected"

print "Sample select using a join of 2 tables"
rows = 0
gimme = c.execute("SELECT name, days FROM course WHERE days > " + str(timespend))
gimme = c.execute("SELECT * FROM course JOIN endday ON days = eid")
for gotme in gimme:
        print gotme
        rows += 1
print "Total of",rows,"records selected"

""" Samples of that running:

Accessing the tables when they don't exist (error condition test)

munchkin:pf13 grahamellis$ python databases_python
Sample select using a program generated query
Traceback (most recent call last):
  File "databases_python", line 38, in <module>
    gimme = c.execute("SELECT name, days FROM course WHERE days > " + str(timespend))
sqlite3.OperationalError: no such table: course

Seeding the tables and reporting on what was added

munchkin:pf13 grahamellis$ python databases_python seed
Sample select using a program generated query
(u'Python Programming', 3)
(u'Intermediate Python', 3)
Total of 2 records selected
Sample select using a join of 2 tables
(1, u'Python Programming', 3, 3, u'Wednesday')
(2, u'Intermediate Python', 3, 3, u'Wednesday')
(3, u'Feeding your Python', 2, 2, u'Tuesday')
Total of 3 records selected

Accessing the tables - courses of longer than one day

munchkin:pf13 grahamellis$ python databases_python 1
Sample select using a program generated query
(u'Python Programming', 3)
(u'Intermediate Python', 3)
(u'Feeding your Python', 2)
Total of 3 records selected
Sample select using a join of 2 tables
(1, u'Python Programming', 3, 3, u'Wednesday')
(2, u'Intermediate Python', 3, 3, u'Wednesday')
(3, u'Feeding your Python', 2, 2, u'Tuesday')
Total of 3 records selected

Accessing the tables - courses of longer than three daya

munchkin:pf13 grahamellis$ python databases_python 3
Sample select using a program generated query
Total of 0 records selected
Sample select using a join of 2 tables
(1, u'Python Programming', 3, 3, u'Wednesday')
(2, u'Intermediate Python', 3, 3, u'Wednesday')
(3, u'Feeding your Python', 2, 2, u'Tuesday')
Total of 3 records selected

Accessing the tables - courses of longer than two daya

munchkin:pf13 grahamellis$ python databases_python 2
Sample select using a program generated query
(u'Python Programming', 3)
(u'Intermediate Python', 3)
Total of 2 records selected
Sample select using a join of 2 tables
(1, u'Python Programming', 3, 3, u'Wednesday')
(2, u'Intermediate Python', 3, 3, u'Wednesday')
(3, u'Feeding your Python', 2, 2, u'Tuesday')
Total of 3 records selected

Try creating tables that already exist - ensuring error is generated

munchkin:pf13 grahamellis$ python databases_python seed
Traceback (most recent call last):
  File "databases_python", line 14, in <module>
    c.execute("CREATE TABLE course (cid int, name text, days int)")
sqlite3.OperationalError: table course already exists
munchkin:pf13 grahamellis$

"""