Insert Data with pymysql using inputs

Question

I'm working on a DB and I'm having trouble when using pymysql to INSERT some values

cur.execute("""INSERT INTO orders (name, size, type, is_done) VALUES (%s, %s, %s, %s)""" 
% (name, size, type, is_done))

Where name, size and type are strings and is_done is a bool

It gives me the typical error 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, so I suppose the problem is a ', but how can I solve it?

Edit

I should also add that the name value is retrieved from a MySQL DB

check which query is really executed. http://stackoverflow.com/questions/7071166/print-the-actual-query-mysqldb-runs I think this link can help you. — huhushow, Jan 25 '17 at 18:01
Thanks, but I have seen that the command really is executed, so it's a dead end... — Mikicat, Jan 25 '17 at 18:07
@huhushow I have alredy found the problem, thanks for all :) — Mikicat, Jan 25 '17 at 18:11

score 3 · Accepted Answer · answered Jul 13 '20 at 08:37

The current accepted solution has a SQL injection vulnerability. You are not supposed to format the string with the % operator - just pass the tuple of arguments as a second argument, and the library will deal with the rest.

cur.execute("INSERT INTO orders (name, size, type, is_done) VALUES (%s, %s, %s, %s)",
    (name, size, type, is_done))

Also see this answer and pymysql documentation.

score 1 · Answer 2 · edited May 06 '19 at 20:34

1

I have found the problem, which was that instead of

  cur.execute("""INSERT INTO orders (name, size, type, is_done) 
  VALUES (%s, %s, %s, %s)""" 
  % (name, size, type, is_done))

I should have done

cur.execute("""INSERT INTO orders (name, size, type, is_done) 
 VALUES ("%s", "%s", "%s", "%s")""" 
% (name, size, type, is_done))

edited May 06 '19 at 20:34

dreftymac

31,404
26
119
182

answered Jan 25 '17 at 18:09

Mikicat

73
2
9

score 0 · Answer 3 · answered Dec 02 '19 at 11:49

0

if you don't input value for id. You have an error. Try this query.

cur.execute("insert into orders values(%s, %s, %s, %s, %s)", (None, name, size, type, is_done))

"%s" and "None" for id column. This query running my code. Note: Don't forget commit()

answered Dec 02 '19 at 11:49

yunusemredemirbas

112
8

Insert Data with pymysql using inputs

3 Answers3