CODEWITHSUNDEEP
pythonmysqlraspberry-pi2
generatorlabs
generatorlabs

Reputation: 47

Passing string variable to MySQL, fails as tuple

Working with a newly purchased RaspberryPi and I am very new to Python/MySQL so please excuse the naive question I am posting. I have looked at many Q&A's about this but I cannot seem to get my head around 'why' this is failing. I get error: "must be string or read-only buffer, not tuple". My variable appears as a string if I test it with TYPE so now I am lost.

import MySQLdb
import time
db = MySQLdb.connect(host="localhost", user="user",passwd="easypwd", db="imagepi")
cursor = db.cursor()
current_time = time.strftime("%H:%M:%S")
current_date = time.strftime("%Y-%m-%d")
filename = (current_time+'.jpg')
sql = ("""INSERT INTO imagelocator(batch, date, time, filename) VALUES
('1001', current_date, current_time, %s)""", filename)
cursor.execute(sql)
db.commit()
db.close()

Thanks so much for offering me a little push in the right direction.

Upvotes: 3

Views: 1487

Answers (1)

haliphax
haliphax

Reputation: 393

The sql variable is a tuple. One half of it is your SQL statement, and the other half is the token value for the %s parameter in your statement. However, simply passing a tuple to an argument does not break it apart and use each element in the tuple as a separate parameter. For that, you have to use an asterisk: function_to_call(*tuple_args) ... but I think you'll have a problem with that, as well, since the database cursor expects a string for the statement argument, and a sequence for the parameters argument. The parameters argument must be a sequence (tuple, list, set, etc.) even if there is only one value.

TL;DR - You need to do something more like this:

sql = "INSERT INTO table_name (a_column, b_column) VALUES ('asdf', %s)"
args = (filename,)
cursor.execute(sql, args)

... or, if you really wanted to be tricksy and use a tuple for everything:

sql = ("INSERT INTO table_name (a_column, b_column) VALUES ('asdf', %s)", (filename,))
cursor.execute(*sql)

Edit: I guess I didn't clarify... while enclosing a string with parentheses does not create a tuple, the addition of a comma does. So, (string_var) is not a tuple, while (string_var,) is. Hopefully, that removes any confusion with how the above code operates.

Also, here's some documentation on the asterisk stuff; both the boring official docs and an easier-to-understand blog post:

Upvotes: 2

Related Questions