Note: I have discovered that re.escape(string) will do escaping but man, does it escape a lot. I can use that if I have to, but I am leaving the question open for any other ideas, also in case this is stumbled upon in the future.
I found a great S.O. answer here regarding INSERTing data into MySQL with Python ( I'm using 2.7 ) where the data contains apostrophes : Python mySQL - escaping quotes
In a nutshell, the S.O. answer was to do this.....
sql = "INSERT INTO TABLE_A(COL_A,COL_B) VALUES(%s, %s)"
a_cursor.execute(sql, (val1, val2))
sql = "UPDATE table SET COL_A='R', COL_B='%s', COL_C='%s', COL_D='%s', COL_E='%s',COL_F='%s' WHERE COL_G='%s'" % (val1, val2, val3, val4, val5, val6)
Use SQL parameters; these are escaped and quoted for you; note that the
INSERT query you found uses this technique. SQL parameter escaping handle apostrophes properly, and makes sure you don't fall victim to a SQL injection attack, among other advantages.
sql value, remove the quoting around your parameters, and pass in the values as parameters, do not use interpolation:
sql = "UPDATE table SET COL_A='R', COL_B=%s, COL_C=%s, COL_D=%s, COL_E=%s, COL_F=%s WHERE COL_G=%s" cur.execute(sql, (val1, val2, val3, val4, val5, val6))
or using a multiline string for better readability:
sql = """ UPDATE table SET COL_A='R', COL_B=%s, COL_C=%s, COL_D=%s, COL_E=%s, COL_F=%s WHERE COL_G=%s" cur.execute(sql, (val1, val2, val3, val4, val5, val6))
As an aside,
re.escape() is a function to handle escaping of strings you want to treat as literal values in a regular expression. The function is entirely unsuited for escaping values in a SQL setting.