3

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 ?

asked Apr 9, 2015 at 18:57
1
  • 2
    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() Commented Apr 9, 2015 at 19:16

2 Answers 2

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)
answered Apr 9, 2015 at 19:02
Sign up to request clarification or add additional context in comments.

Comments

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

Comments

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.