How to debug 'You have an error in your SQL syntax'

Question

There are several questions on You have an error in your SQL syntax - but they all seem to address a specific syntax error in the query, which is generally not helpful to others.

My question is how can I get the formatted query from a MySQL command in Python so that I can actually inspect it?

So I have a statement like:

cursor.execute("INSERT INTO products(acc, title, sku, price, price_checked, desc, imgs) VALUES (%s,%s,%s,%s,%s,%s,%s)", (1, prod.title, prod.sku, prod.price, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), prod.desc, prod.imgs))

And the error is:

_mysql_exceptions.ProgrammingError: (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 n ear 'desc, imgs) VALUES (1,'Holy Stone HS700 FPV Drone with 1080p HD Camera Live Vide' at line 1")

Any idea how I can get the executed query? I don't want to format the query myself then print it before executing, but rather use MySQL's built in formatting for security reasons, mainly.

Not sure what is your question. But if you want format the query try http://dpriver.com/pp/sqlformat.htm , if you want debug the query why dont test it on mysql directly? — Juan Carlos Oropeza, Dec 22 '18 at 17:44
That isn't the query that's throwing the error? It's talking about `desc` so presumably it's a SELECT query. This is an INSERT and doesn't include DESC — roganjosh, Dec 22 '18 at 17:44
@roganjosh Yes it does include desc. It's the second last argument. Do you see it? — User, Dec 22 '18 at 17:45

roganjosh · Answer 1 · 2018-12-22T18:18:58.650

0

DESC is a keyword in SQL for sorting in descending order. You can't call one of your parameters "desc" because it will be interpreted as such. Think about if you called one of your columns "Select"; it's the same issue. You need to rename that field.

Initially I was focused on prod.desc but in your query string, you have 'desc' listed as an actual column name: "... products(acc, title, sku, price, price_checked, desc, imgs)"

You can see the last query run using the advice here but I can't test as I don't have any MySQL instance.

edited Dec 22 '18 at 18:18

answered Dec 22 '18 at 17:49

roganjosh

12,594
4
29
46

`prod` is an object with `desc` property. That is pass to `%s` as parameter – Juan Carlos Oropeza Dec 22 '18 at 17:49
@JuanCarlosOropeza no, forget about the python object being passed (I saw that first too), they literally have a column called "desc"; `...INTO products(acc, title, sku, price, price_checked, desc, imgs)` – roganjosh Dec 22 '18 at 17:51
Thanks - this worked. Can you just add a method to view the query executed? – User Dec 22 '18 at 18:08
@User edited but can't test. It's not relevant to your issue anyway because you didn't spot that you were using a keyword and it's not flagging it as such in the error (explicitly, anyway). – roganjosh Dec 22 '18 at 18:19

How to debug 'You have an error in your SQL syntax'

1 Answers1