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?

vaultah · Accepted Answer · 2014-04-15 12:16:19Z

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.

edited Apr 15, 2014 at 12:16

answered Apr 15, 2014 at 11:55

vaultah

46.9k12 gold badges120 silver badges145 bronze badges

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

2 Comments

praxmon Over a year ago

Tried that too, to no avail.

praxmon Over a year ago

Oh thanks thanks! It works! What is the logic behind this btw? Why do I need the value to be a tuple?

Collectives™ on Stack Overflow

MySQL and Python-Insertion

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related