mysql 1064 oython,1064，“您的SQL语法有错误； ...” Python MySQL

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

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.

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.

Current Code:

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

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.

解决方案

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.