5

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.

Improve this question

1 Answer 1

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Man.... I spent over 10 hours on this trying different methods and you resolve it in all of the amount of time it took you to hit "Add Comment". Thank you alecxe

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.