MySQL and Python-Insertion

Question

I am trying to insert some values into my table. The code is:

import MySQLdb
db=MySQLdb.connect(host="localhost",user="user_name", passwd="",db="name_of_db", port=3306)
cursor=db.cursor()
readcount=52
sql="Insert into pressure_image(idItem, pressure_val, image_loc, array_time, Time_Stamp) values ('%d', 260, 'a.jpg', 'aray', 14-11-2000)"
try:
 cursor.execute(sql, (readcount))
 db.commit()
except:
 print "No value inserted"
 db.rollback()

The problem is when I try the insertion command like:

sql="Insert into pressure_image(idItem, pressure_val, image_loc, array_time, Time_Stamp) values (30, 260, 'a.jpg', 'aray', 14-11-2000)" the values are inserted in the table correctly but when I try the command as given in the code no values are inserted. So essentially, constants work but variables do not.

How do I insert variables into the table?

Answer 1

Try adding a comma:

cursor.execute(sql, (readcount,))

Here (readcount) == readcount, but (readcount,) is a tuple containing readcount.

Also replace %d with %s in your query. As said in the documentation

execute(self, query, args=None)

query - string, query to execute on server

args - optional sequence or mapping, parameters to use with query.

Note: If args is a sequence, then %s must be used as the parameter placeholder in the query. If a mapping is used, %(key)s must be used as the placeholder.

So, the MySQLdb forces us to use a sequence for args argument (which can be of any iterable type) and %s. %s format specifier is used because values from args are escaped and converted to string before formatting the query.

• praxmon: Tried that too, to no avail.
• praxmon: Oh thanks thanks! It works! What is the logic behind this btw? Why do I need the value to be a tuple?