Execute query in python

Question

I have a simple table

******************
MId  |  Title
1    |  pqr
2    |  abc
******************

now the code which i have written to append data to table is

 import MySQLdb
 db = MySQLdb.connect("localhost", "root", "a", "Project")
 cursor = db.cursor()
 sql = "INSERT INTO Project.Movie(MId, Title) VALUES(%d, %s)" % (21,'aman') 
 cursor.execute(sql)

But the above code generates error

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
    query = query % db.literal(args)
TypeError: %d format: a number is required, not str

I have just passed number and not in quotes then why is there an error ?

Regardless of the answers below, formatting the SQL on the python side is a bad practice due to SQL injection. Look at how the official docs pass parameters into execute() — Izkata
– Izkata, Commented Apr 9, 2015 at 19:16

miradulo · Accepted Answer · 2015-04-09 19:44:04Z

4

Your real issue here is quite simply not putting quotes around your inserted string. As Izkata remarked, you really shouldn't be doing this Python-side wise due to SQL injection. Were this SQL-side, your entire query must be in a string format when being executed, thus you must use %s for every field.

Your string (instead of %d) will be converted into an SQL literal value in any case upon executing your query.

In your case, you should be writing your insertion statement like this (as seen in the docs):

sql = (
  "INSERT INTO Project.Movie (MId, Title) "
  "VALUES (%s, %s)"
)
data = (21,'aman')
cursor.execute(sql, data)

edited Apr 9, 2015 at 19:44

answered Apr 9, 2015 at 19:02

miradulo

29.8k7 gold badges86 silver badges97 bronze badges

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

Comments

Dadi Hrolfsson · Accepted Answer · 2015-04-09 19:08:23Z

2

Also you want quotes around your string value:

sql = "INSERT INTO Project.Movie(MId, Title) VALUES(%s, '%s')" % ('21','aman')

answered Apr 9, 2015 at 19:08

Dadi Hrolfsson

294 bronze badges

Collectives™ on Stack Overflow

Execute query in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related