将字符串变量传递给MySQL，失败为元组 [英] Passing string variable to MySQL, fails as tuple
问题描述
使用新购买的RaspberryPi，我对Python/MySQL还是很陌生，所以请原谅我发布的幼稚问题. 我已经看过许多有关此问题的问答，但是我似乎无法避免为什么"失败了.我收到错误消息:必须是字符串或只读缓冲区，而不是元组" .如果使用TYPE进行测试，我的变量将显示为字符串，所以现在我迷路了.
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.
推荐答案
sql变量是一个元组.它的一半是您的SQL语句，另一半是您的语句中%s参数的标记值.但是，仅将元组传递给参数并不能将其拆分，并且可以将元组中的每个元素用作单独的参数.为此，您必须使用星号:function_to_call(*tuple_args) ...但我认为您也会遇到问题，因为数据库游标期望statement参数使用字符串，并且使用序列parameters参数. parameters参数必须是一个序列(元组，列表，集合等).即使只有一个值.
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-您需要做更多类似的事情:
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)
我想我没有弄清楚……虽然用括号括起来的字符串不会创建元组，但添加逗号确实.因此，(string_var)不是元组，而(string_var,)是元组.希望这可以消除对上面代码的操作方式的任何困惑.
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:
- Boring Python docs
- Blog post
这篇关于将字符串变量传递给MySQL，失败为元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
