the best mssql commit ever

update_test_db.py

import csv
#from datetime import datetime
#from datetime import date
import datetime
import pymssql
from decimal import Decimal
from pprint import pprint

### Single quoter
def singlequoter(s1):
    return "'{}'".format(s1)

### a function to update the database
# def droptableslol(corr, wsnum, wateryear):
corr = "corr_table_2013.csv"
wsnum = "GSWS02"
wateryear = "2013"

## Teach python the right date format: 
dateformat='%Y-%m-%d %H:%M:%S'
if wateryear == "2010":
    firstdate = "2010-01-01 00:00:00"
elif wateryear == "2011":
    firstdate = "2011-01-01 00:00:00"
elif wateryear == "2012":
    firstdate = "2012-01-01 00:00:00"
elif wateryear == "2013":
    firstdate = "2013-01-01 00:00:00"
else:
    pass 
sitecode = wsnum


############## DATABASE CONNECTION ########
#
# # Connect to MSSQL Server
conn = pymssql.connect(server="stewartia.forestry.oregonstate.edu:1433",
                       user="petersonf",
                       password="D0ntd1sATLGA!!",
                       database="FSDBDATA")

# Create a database cursor
cursor = conn.cursor()

#cursor.execute("""SELECT * FROM FSDBDATA.dbo.HF00206_test
#WHERE bgn_date_time >= %s AND sitecode LIKE %s
#ORDER BY bgn_date_time ASC""",(firstdate, sitecode))

#for row in cursor:
#    print row

#import the reader which generates a reader object 
reader = csv.reader(open(corr))

# iterate through the reader
for row in reader:
    dbcode2 = str(row[0])
    entitycode = str(row[1])
    othersitecode = str(row[2])
    other_bgn_date_time = str(row[3])
    other_bgn_cr = str(row[4])
    other_bgn_cr = None if other_bgn_cr == '' else Decimal(other_bgn_cr)
    other_bgn_hg = str(row[5])
    other_bgn_hg = None if other_bgn_hg == '' else Decimal(other_bgn_hg)
    try:
        other_end_date_time = str(row[6])
    except Exception as exc:
        other_end_date_time = "''"
    other_end_cr = str(row[7])
    other_end_cr = None if other_end_cr == '' else Decimal(other_end_cr)
    other_end_hg = str(row[8])
    other_end_hg = None if other_end_hg == '' else Decimal(other_end_hg)

    other_comments = str(row[9])

    if sitecode == othersitecode:
    #print "this line would get replaced" + str(row)

#     ### This query will only show the data points which are potentially questionable
  #Invalid literal for Decimal: ''
    
   # (dbcode2,entitycode,othersitecode,other_bgn_date_time,Decimal(other_bgn_cr),Decimal(other_bgn_hg),other_end_date_time,Decimal(other_end_cr),Decimal(other_end_hg),other_comments))

#     # cursor.execute("""DELETE FROM FSDBDATA.dbo.HF00206_test
#     # WHERE bgn_date_time >= %s AND sitecode LIKE %s""",(firstdate,sitecode))
        try:
            #print cursor.mogrify("""INSERT INTO FSDBDATA.dbo.HF00206_test(dbcode,entity,sitecode,bgn_date_time,bgn_cr,bgn_hg,end_date_time,end_cr,end_hg,comments)
            #VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",(dbcode2,entitycode,othersitecode,other_bgn_date_time,other_bgn_cr,other_bgn_hg,other_end_date_time,other_end_cr,other_end_hg,other_comments))
            cursor.execute('INSERT INTO FSDBDATA.dbo.HF00206_test (dbcode,entity,sitecode,bgn_date_time,bgn_cr,bgn_hg,end_date_time,end_cr,end_hg,comments) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', (dbcode2,entitycode,othersitecode,other_bgn_date_time,other_bgn_cr,other_bgn_hg,other_end_date_time,other_end_cr,other_end_hg,other_comments))
            conn.commit()
            print (dbcode2,entitycode,othersitecode,other_bgn_date_time,other_bgn_cr,other_bgn_hg,other_end_date_time,other_end_cr,other_end_hg,other_comments)
            

        except Exception as exc:
            print exc
            print "i died"
            print "i died"