python - pyodbc - 插入 MySQL 不工作

Question

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

pyodbc.ProgrammingError: ('42000', "[42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.12-log]你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以便在第 1 行的“Pipedly”附近使用正确的语法 (1064) (SQLExecDirectW)")

脚本如下(隐藏的数据库凭据和 API key )。请阅读脚本中的注释以遵循我上面的描述。感谢大家的帮助

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

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