Add Checklist To MySQL/MariaDB Database

This script adds a record to an existing MySQL or MariaDB database.

This includes the ability to store images with the record in BLOB columns. We suggest using medium or large blobs, as the base blob type is not large enough to fit the files likely to be delivered from smartphones.

In this example, we add the record to a table that is named after the form we are submitting - for instance, if the checklist is named isolation, it is added to a table called isolation.

import pymysql
from datetime import datetime
import os
import sys
import traceback
import warnings
 
#Set default details
host='servername'
username='username'
password='userpassword'
database='database'
 
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)
 
#Used to embed an attachment/image file to a BLOB column.
def readAttachment(filename):
    #Convert digital data to binary format
    with open(filename, 'rb') as file:
        binaryData = file.read()
    return binaryData
 
img = readAttachment(os.environ["at0"])
values =  (asset,user,tm,img)
 
#Choose a table based on the checklist name
query="INSERT INTO " + checklist.replace(' ','') +" (asset,username,stamp,img) VALUES (%s,%s,%s,%s)";
 
#Filter out a common Python/MySQL error with attempting to encode strings inside a BLOB.
warnings.filterwarnings('ignore', category=pymysql.Warning)
try:
    db = pymysql.connect(host, username, password, database)
    cursor = db.cursor()
    try:
        cursor.execute(query,values)
    except:
        traceback.print_exc()
    db.commit()
except:
    traceback.print_exc()
 
#If everything worked out, send the word 'OK'.
print 'OK'