#!/usr/bin/python

###################################################
# Author: christos faloutsos
# Date: Jan. 2012
# Purpose: Mainly wants to illustrate cursors
#          Specifically,
#             * expects a csv file, and 
#             * loads it into a sqlite db file
#             And answers a few queries, for fun
###################################################


import sqlite3
import csv

fname='tst.csv'
dbname='tst.db'

# conn = sqlite3.connect(':memory:')
conn = sqlite3.connect(dbname)

conn.execute('create table if not exists tst (name text, address text, state text, salary integer)')

csvReader = csv.reader(open(fname), delimiter=',', quotechar='"')

print " --- csv2sql started on ", fname
for row in csvReader:
        conn.execute('insert into tst values' +
		'(?, ?, ?, ?)', row)

print " --- csv2sql inserted ", fname
print " "
print " --- printing all tuples --- "
cur = conn.cursor()
cur.execute('select * from tst')
for row in cur:
    for elem in row:
	    print elem, "\t",
    print ""

print " "
print " --- printing rich tuples (sal>2.5K), sorted  --- "
cur.execute('select * from tst where salary > 2500 order by salary desc ')
for row in cur:
    for elem in row:
	    print elem, "\t",
    print ""

print " "
print " --- printing sum-salary per state ----"
cur.execute('select state, sum(salary) from tst  group by state')
for row in cur:
    for elem in row:
	    print elem, "\t",
    print ""

conn.commit()
cur.close()
conn.close()