pyodbc - Insert into MySQL not working

Question

I am writing a script that reads data from one table, translates text in one of the columns into German using Google Translate API and load the data back into another table. Everything works except the last step which is the insert into the second table. I printed out the sql statement that is constructed in the script. If i hard code that output and execute the SQL it works just fine. But if i pass it as an argument it fails with the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.12-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Pipedly' at line 1 (1064) (SQLExecDirectW)")

The script is as follows (masked db credentials & API key). Please read the comments in the script to follow my description above. Thanks to everyone for the help

import pyodbc
import json
import collections
import urllib
import urlparse
import os
import time
import string
def insertData(databaseName, tableName, insertList):
 insertStatement = "INSERT INTO " + databaseName + "." + tableName 
 for setLine in insertList:
 insertStatement = insertStatement + setLine
 return insertStatement 
def createInsertFtpStatusList(customer, text, location, channel):
 insertList = []
 insertList.append("(`customer`, ")
 insertList.append("`text`, ")
 insertList.append("`location`, ")
 insertList.append("`channel`) ")
 insertList.append(" VALUES ('%s', '%s', '%s', '%s');" % (customer, text, location, channel))
 return insertList
def url_fix(s, charset='utf-8'):
 if isinstance(s, unicode):
 s = s.encode(charset, 'ignore')
 scheme, netloc, path, qs, anchor = urlparse.urlsplit(s)
 path = urllib.quote(path, '/%')
 qs = urllib.quote_plus(qs, ':&=')
 return urlparse.urlunsplit((scheme, netloc, path, qs, anchor))
cnxn = pyodbc.connect("DSN=*mydsn*;UID=*username*; PWD=*password*")
cursor = cnxn.cursor()
cursor.execute("""
 SELECT customer, replace(text,'#','') as text, location, channel FROM feeds.vw_pp;
 """)
url1="https://www.googleapis.com/language/translate/v2?key=*myAPIkey*&q="
url2="&source=en&target=de"
rows = cursor.fetchall()
for field in rows:
 text= field.text
 text= text.replace(" ", "%20")
 url=url1 + text + url2
 url = url_fix(url)
 result = urllib.urlopen(url) 
 data = json.load(result)
 ts_text= data["data"]["translations"][0]["translatedText"]
 ts_text= ts_text.replace("% 20% 20","")
 ts_text= ts_text.replace(" 20%","")
 ts_text= ts_text.replace("%","")
 ts_text= ts_text.replace("20","")
 ts_text= ts_text.replace(" "," ")
 insertList= createInsertFtpStatusList(field.customer, ts_text, field.location, field.channel)
 sqlStatement= '"' + insertData("feeds", "translated_pp", insertList) + '"' 
 print sqlStatement
# The sql statement that prints above: 
# "INSERT INTO feeds.translated_pp(`customer`, `text`, `location`, `channel`) VALUES ('Pipedly', 'pipedly kommen und seine gehen bis fantastisch sein!', '', 'Facebook');"
# Success - If i copy paste the statement above and execute hardcoded as below, it works just fine.
 cnxn.execute("INSERT INTO feeds.translated_pp(`customer`, `text`, `location`, `channel`) VALUES ('Pipedly', 'pipedly kommen und seine gehen bis fantastisch sein!', '', 'Facebook');"
)
# Fail - But if i pass the string as an argument it fails :(
 cnxn.execute(sqlStatement)

Answer 1

Don't interpolate data into SQL statements yourself; leave proper escaping to the database adapter by using SQL parameters:

sqlStatement = "INSERT INTO feeds.translated_pp(`customer`, `text`, `location`, `channel`) VALUES (%s, %s, %s, %s)"
cnxn.execute(sqlStatement, (field.customer, ts_text, field.location, field.channel))

The database adapter then ensures that each value is properly escaped, including handling of embedded quotes in the value.

• karthicks: Thanks very much! That worked except i had to change %s to ? as pyodbc seems to want arguments passed with ? sqlStatement = "INSERT INTO zapier_feeds.translated_pp(`customer`, `text`, `location`, `channel`) VALUES (?, ?, ?, ?)"
• Martijn Pieters: @karthicks: My apologies. Yes, the pyodbc module uses the question mark style for paramaters (the Python DB API standard allows for both styles, plus a few others). I had it confused with the standard MySQL python module.