Python and SQLite3

18 Dec

I need to generate a report every month. This report needs to have 30 names on it and I need to make sure than none of the people have appeared in previous months. I used Python and SQLite to store my items and to verify them. Here is what I did:

I wrote a script to empty all the data from an Excel sheet in to a SQLite Database Table. Then I wrote my program. The if __name__ == “__main__”: takes my command line arguments (load or scan and the data file.xls) and calls the appropriate function. If there is an error, it prints out the correct usage.

if __name__ == “__main__”:

if (len(sys.argv) < 3):

print “USAGE: warrant.py load/scan excelfile.xls”

elif sys.argv[1]==”load”:

load(sys.argv[2])

elif sys.argv[1]==”scan”:

scan(sys.argv[2])

else: print “Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls”

The function scan() runs through an excel sheet and compares the names to the database. Currently, if the same last and first appear it prints out the info for the person. I will fix this by using ss# as another filter.

The load() function takes an excel file and uploads them to the database. No error checking. It will crash if the name already exists, but the list should have been cleaned with scan() first. I need to fix this anyway.

I hate error checking. My programs are usually for me so I don’t care if they fail because I can read the error and track it down. But now that I need anyone to be able to use it,  I cant have this – when the excel file doesn’t exist:

Error when Python can't find the Excel file.

Error when Python can’t find the Excel file.

Instead, I need to catch the errors and fail gracefully – like when the commands entered are wrong:

Catching an error in usage - gracefully.

Catching an error in usage – gracefully.

Fixed the error if the file is not found by using from os.path import exists then updating the if name=main to include:

elif sys.argv[1]==”load”:
           if exists(sys.argv[2]):
                  load(sys.argv[2])
           else:
                  print “file not found”

So here is my code thus far:

import sqlite3 as lite
import sys
from os.path import exists
from xlrd import open_workbook

def load(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            fname=sheet.cell(rownum,0).value
            lname=sheet.cell(rownum,1).value
            dob=sheet.cell(rownum,2).value
            ss=sheet.cell(rownum,3).value
            cur.execute(“INSERT INTO previous VALUES(fname,lname,dob,ss)”)
            print “Added: “+fname+”, “+lname+”, “+dob+”, “+ss

def scan(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            lastname=sheet.cell(rownum,1).value
            firstname=sheet.cell(rownum,0).value
            query=”SELECT * FROM previous WHERE lname='”+lastname+”‘ AND fname='”+firstname+”‘”
            cur.execute(query)    
            while True:
                row=cur.fetchone()
                if row==None:
                    break
                print row[0], row[1], row[2], row[3]

if __name__ == “__main__”:
    if (len(sys.argv) < 3):
        print “\n*************************\n\n”+”USAGE: warrant.py load/scan excelfile.xls\n\n”+”*************************”
    elif sys.argv[1]==”load”:
        if exists(sys.argv[2]):        
            load(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    elif sys.argv[1]==”scan”:
        if exists(sys.argv[2]):
            scan(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    else:
        print “\n*************************\n\n”+”Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls\n\n”+”*************************”

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: