pyodbc_sqlserver.py

"""
pyodbc_sqlserver.py
November 30, 2011
Dana Bauer
script converts time data from Cicero_Stage from Month Date, Year to Date Month Year (international form)
"""

import pyodbc
import time
import sys


#getting several strange error messages with this code that I suspect are problems with the pyodbc library, so I had to create some workarounds 
#first error -- No results. Previous SQL was not a query. This error was reported here: http://code.google.com/p/pyodbc/issues/detail?id=215
#second error -- HY010 Function sequence error, also reported here: http://code.google.com/p/django-pyodbc/issues/detail?id=115


#connection string to Cicero_Stage database. I should probably record these as separate variables. Also probably not a good idea to have the password visible here
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=Newton;DATABASE=Cicero_Stage;UID=sa;PWD=ok1n@wa')

#I am creating two cursors to work around a bug. Would normally be able to do this with one cursor
cursor = cnxn.cursor()
cursor2 = cnxn.cursor()

#the first cursor is pulling all the ElectionEventIDs
cursor.execute("select ElectionEventID from TempElectionEvent")

#normally bad to load all rows at once but we are working around a bug
#here we are fetching all the ElectionEventIDs first so that that we can match ElectionDateText one at a time
rows = cursor.fetchall()

#create count variables to track counts of dates that meet the standard form and those that don't meet the standard form
count=0
count1=0

#loop through rows
for row in rows:
    #normally would do one select and get both fields, but we are working around a bug and doing two selects. In the statement below we're selecting the ElectionDateText data for each ElectionEventID
    cursor2.execute("select ElectionDateText from TempElectionEvent where ElectionEventID = ?", row.ElectionEventID)
    
    #pulling in ElectionDateText row matched to ElectionEventID one at a time, starting with the first [0]
    text = cursor2.fetchall()[0].ElectionDateText
    try:
        #using strptime() to parse the date
        tm = time.strptime(text, '%B %d, %Y') 
    except:
        #catching errors where data is in a different form. In most cases it's an estimated data such as Autumn 1897
        print "wrong form"
        count += 1
    else:
        #using strftime() to generate the new string and storing the new string in the text variable
        text = time.strftime('%d %B %Y', tm)
        #another sql statement to update the table with a new text string per ElectionEventID (except in error cases caught above)
        cursor.execute("update TempElectionEvent set ElectionDateText=? where ElectionEventID=?", text, row.ElectionEventID)
        count1 += 1
        #printing new dates to screen to see if it's working
        print text
        
             
#must commit changes to the database. I only have to do this once, not within the loop
cnxn.commit() 
cursor.close()
cnxn.close()
print count
print count1