将字符串变量传递给MySQL

将字符串变量传递给MySQL

本文介绍了将字符串变量传递给MySQL,失败为元组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用新购买的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,失败为元组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-14 18:07