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 ?

Answer 1

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)