1064，“您的SQL语法有错误； ..." Python MySQL [英] 1064, "You have an error in your SQL syntax;..." Python MySQL

问题描述

因此，自上周五以来，我一直在研究此问题，但无法解决此错误:

So I have been working on this since last Friday and cannot get around this error:

1064，您的SQL语法有误；请查看手册， 对应于您的MySQL服务器版本以使用正确的语法 在'[u'161010-035670']附近，第1行的WHERE order_id = 87'或类似内容 与此错误相同.

1064, "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 '[u'161010-035670'] WHERE order_id=87' at line 1" or something along the same lines as this error.

基本上，我的python将从MySQL数据库中获取数据，它使用Simple-Salesforce在SalesForce中创建一个案例，然后查询该案​​例是否正确创建，但是我需要它将该案例编号写回到我专门创建的列中的数据库中的票号.

Basically my python will grab data from MySQL database, it creates a case in SalesForce using Simple-Salesforce and then queries that case it created correctly but I need it to write that case number back into the database in a column I created specifically for the ticket number.

当前代码:

for rowx in xrange(1, sheet.nrows):
    SN = sheet.row_values(rowx, start_colx=3, end_colx=None)[0]
    print SN
    Id = sheet.row_values(rowx, start_colx=6, end_colx=None)[0]
    print Id
    d = sf.query("SELECT CaseNumber FROM Case WHERE Serial_Number__c ='%s' AND Status = 'New Portal RMA'" % SN)

    data = [e["CaseNumber"] for e in d["records"]]
    print (data)



    try:
        con = MySQLdb.connect(user=ur, passwd=pd, host=ht, port=pt, db=db)
        cursor = con.cursor()

        cursor.execute("UPDATE rma_order SET rma_num=%s WHERE order_id=%s" % (data, Id))

        con.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        con.close()

主要问题在于以下代码行:

Main issue is with this line of code:

 cursor.execute("UPDATE rma_order SET rma_num=%s WHERE order_id=%s" % (data, Id))

我尝试使用'％s'和不使用'％s'都没有区别，尝试了"... WHERE order_id =％s"，(数据，ID))，并出现相同的错误.如果我替换了"order_id = 87"并使用cursor.execute("UPDATE rma_order SET rma_num =％s WHERE order_id = 87"％(data))将数据保留在那里，那么它可以正常工作，并以正确的格式写入案例编号在数据库中，一旦我将"Id"添加为％s的因数，它就会给我错误.我也尝试过％d并得到相同的结果.

I have tried with and without '%s' with no difference, tried "...WHERE order_id=%s", (data, Id)) with same error. If I replace "order_id=87" and let data stay there with cursor.execute("UPDATE rma_order SET rma_num=%s WHERE order_id=87" % (data)) then it works fine and writes the case number in the correct format into the database, as soon as I add "Id" as a factor with %s then it gives me errors. I have also tried with %d with same result.

任何帮助将不胜感激.

Any help would be greatly appreciated.

推荐答案

data值是一个 list ，您正尝试将其格式化为查询格式.而且，不要使用字符串格式将变量插入查询中-而是使用正确的查询参数化:

The data value is a list and you are trying to format it into the query. And, don't use string formatting to insert variables into a query - use a proper query parameterization instead:

cursor.execute("""
    UPDATE 
        tplinkus_rma.rma_order 
    SET 
        rma_num=%s 
    WHERE 
       order_id=%s""", (data[0], Id))

请注意如何将查询参数放置在元组中并作为单独的参数传递.

Note how the query parameters are placed in a tuple and passed as a separate argument.

这篇关于1064，“您的SQL语法有错误； ..." Python MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！