Add Checklist To Access Database

This script adds a record to an existing Access database (MDB or ACCDB, if you have the latest ODBC drivers installed).

Note that image storage is very inefficient in Access via the old OLE method, and the newer Attachment method doesn't have an SQL syntax, so this submission script does not allow you to save images to the database.

import pyodbc
from datetime import datetime
import os
import sys
import traceback
import warnings
 
#Set default details
filename="pathtofile"
 
drivertouse = 'Microsoft Access Driver (*.mdb)'
for x in pyodbc.drivers():
    if x == 'Microsoft Access Driver (*.mdb, *.accdb)':
        drivertouse = 'Microsoft Access Driver (*.mdb, *.accdb)'
 
asset = "Unknown Asset"
checklist = "Unknown Checklist"
user = "Unknown User"
assetid = "1"
tm = datetime.now().strftime("%Y/%m/%d %H:%M:%S")
 
#Get submission details from environment variables
try:
    asset = os.environ["assetname"]
except:
    print "No Asset Provided"
    sys.exit(0)
 
try:
    assetid = os.environ["assetid"]
except:
    print "No Asset ID Provided"
    sys.exit(0)
 
try:
    checklist = os.environ["checklist"]
except:
    print "No Checklist Provided"
    sys.exit(0)
 
try:
    user = os.environ["user"]
except:
    print "No User Provided"
    sys.exit(0)
 
def Sub(st,ass,chk,usr,dt):
    return st.replace("{checklist}",chk).replace("{asset}",ass).replace("{user}",usr).replace("{date}",dt)
 
constr = (
    r'DRIVER={' + drivertouse + '};'
    r'DBQ=' + filename + ';'
    )
 
values =  (asset,user,tm)
 
#Create an insert statement.
query="INSERT INTO " + checklist.replace(' ','') +" (asset,user,stamp) VALUES ('" + str(asset) + "','" + str(user) + "','" + tm + "')";
 
try:
    db = pyodbc.connect(constr)
 
    cursor = db.cursor()
    try:
        cursor.execute(query)
    except:
        traceback.print_exc()
    db.commit()
except:
    traceback.print_exc()
 
#If everything worked out, send the word 'OK'.
print 'OK'