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)

Martijn Pieters Martijn Pieters · Accepted Answer · 2013-11-16T18:21:08

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.

pyodbc - Insert into MySQL not working

1 Answers