pyodbc -插入到MySQL中不工作

Question

我正在编写一个脚本，它从一个表中读取数据，使用将其中一个列中的文本翻译成德语，然后将数据加载回另一个表。除了最后一步，即插入到第二个表外，一切都正常工作。我打印了脚本中构造的sql语句。如果我硬编码输出并执行SQL，它就能正常工作。但是，如果我将它作为参数传递，它会失败，但会出现以下错误：

pyodbc.ProgrammingError：(“42000”，“42000ODBC5.2(W) DriverYou语法出错；检查与MySQL服务器版本对应的手册，以获得在第1行(1064) (SQLExecDirectW)附近使用的正确语法”)

脚本如下(屏蔽db凭据& API键)。请阅读脚本中的评论，以遵循我上面的描述。感谢大家的帮助

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

不要自己将数据插入到SQL语句中；使用SQL参数将数据正确转义给数据库适配器：

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))

然后，数据库适配器确保正确地转义每个值，包括处理值中的嵌入引号。