Yu Duan Yu Duan - 1 year ago 114
MySQL Question

invalid syntax when using pymysql

I'm learning using python with Mysql.
Same query works differently between Mysql and pymysql.
For example:In mysql console I have a table named "pages"

INSERT INTO pages (title,content) VALUES ("test title","test content")

And It works.
In python,I import pymysql ,create connection and cursor = "cur",when type:

cur.execute("INSERT INTO pages (title,content) VALUES ("test title","test content")")

raise an error : invalid syntax.
But when I try this with place holder:

cur.execute("insert into pages (title,content) values (%s,%s)",("test title","test content"))

It works!
So I was confused why same query work in Mysql but not in python,and does It means I should always use place holder when using pymysql?

Answer Source

First of all, the last version (the parameterized query) should be the most preferred one since you don't need to put the quotes around the placeholders at all letting the database driver figure out the type conversion and the quotes automatically. And, this way you are safe from SQL injection attacks.

Now, to answering your question. If you want to use double quotes inside double quotes, you need to escape them:

cur.execute("INSERT INTO pages (title,content) VALUES (\"test title\", \"test content\")")

Or, use single quotes outside and double quotes inside:

cur.execute('INSERT INTO pages (title,content) VALUES ("test title", "test content")')