Python: MySQLdb: Error: 1064 "You have an error in your SQL syntax."

Question

I'm new to MySQLdb and Python. I'm trying to execute the following statement:

header_string = 'number_one, number_two, number_three'
values = '1, 2, 3'
cursor.execute("""INSERT INTO my_table (%s) VALUES (%s)""", (header_string, values))

and it returns with the following error:

Error: 1064 "You have an error in your SQL syntax."

From my limited understanding of MySQLdb the above execute statement should execute the following SQL statement:

INSERT INTO my_table (number_one, number_two, number_three) VALUES (1, 2, 3)

Any ideas what I might be doing wrong?

SQL doesn't allow variables to represent comma delimited values at runtime -- if you create the SQL query as a string & use the variables in Python before submitting the query, it should work. — OMG Ponies
– OMG Ponies, Commented Mar 1, 2011 at 18:15

MikeyB · Accepted Answer · 2011-03-01 18:22:09Z

6

Try:

header_string = ('number_one','number_two','number_three')
values = (1,2,3)
cursor.execute("""INSERT INTO my_table (%s,%s,%s) VALUES (%s,%s,%s)""", (header_string+values))

answered Mar 1, 2011 at 18:22

MikeyB

3,4001 gold badge30 silver badges39 bronze badges

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

3 Comments

jdferreira Over a year ago

That is still not going to work. MySQLdb escapes strings so that no SQL injection can occur in your code. That query will be translated into INSERT INTO my_table("number_one", "number_two", "number_three") VALUES (1, 2, 3), which is also syntactically wrong.

MikeyB Over a year ago

Actually it'll be translated into: INSERT INTO my_table(`number_one`, ...). Apparently it did work, since the answer was accepted.

jdferreira Over a year ago

My bad. I really thought mysqldb changed any string into its quoted form, and was unaware that it correctly handled column names. One learns new stuff every day :)

Collectives™ on Stack Overflow

Python: MySQLdb: Error: 1064 "You have an error in your SQL syntax."

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related