python - 使用多个参数格式化字符串,以便 MySQL 可以处理它们
问题描述
我想知道如何格式化 API 返回的字符串,以便 Mysql 可以处理它。
这是我的 API 返回的字符串:bad_string = "History,Romance,Business & Money"
尝试1:
cursor.execute("SELECT * FROM book WHERE scrape_category IN (%s) ORDER BY RAND() LIMIT 15", bad_string)
返回此消息:
Traceback (most recent call last):
File "C:\PATH", line 23, in <module>
cursor.execute("SELECT * FROM book WHERE scrape_category IN (%s) ORDER BY RAND() LIMIT 15", bad_string)
File "C:\PATH\AppData\Roaming\Python\Python37\site-packages\mysql\connector\cursor_cext.py", line 259, in execute
"Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
尝试2:
bad_string_2 = "'Romance', 'History', 'Business & Money'"
cursor.execute("SELECT * FROM book WHERE scrape_category IN (%s) ORDER BY RAND() LIMIT 15", bad_string_2)
返回此消息:
Traceback (most recent call last):
File "C:PATH", line 21, in <module>
cursor.execute("SELECT * FROM book WHERE scrape_category IN (%s) ORDER BY RAND() LIMIT 15", bad_string_2)
File "C:\PATH\AppData\Roaming\Python\Python37\site-packages\mysql\connector\cursor_cext.py", line 246, in execute
prepared = self._cnx.prepare_for_mysql(params)
File "C:\PATH\AppData\Roaming\Python\Python37\site-packages\mysql\connector\connection_cext.py", line 520, in prepare_for_mysql
raise ValueError("Could not process parameters")
ValueError: Could not process parameters
这有效:
cursor.execute("SELECT * FROM book WHERE scrape_category IN ('Romance', 'History', 'Business & Money') ORDER BY RAND() LIMIT 15")
当我这样做时,查询有效:
cursor.execute("SELECT * FROM book WHERE scrape_category IN ('Romance', 'History', 'Business & Money') ORDER BY RAND() LIMIT 15")
如何格式化字符串并使 MySQL 返回结果?
解决方案
尝试一些这样的:
filter ="History,Romance,Business & Money".split(',')
sqlcommand = "SELECT * FROM book WHERE scrape_category IN ({0})".format(
', '.join(['%s'] * len(filter)))
print(sqlcommand)
cursor.execute(sqlcommand, filter)
推荐阅读
- java - 有没有比在 Java 中使用 imageIcon 数组更性能友好的方式来遍历动画的图像?
- c++ - 如何将 Visual Studio 与 CMake 一起使用并保留文件结构
- python - 如何在 m 长列表和 int y 的一行中获取两个输入
- php - 成对函数不同的计算
- r - R根据参考列的最小值或最大值创建包含另一列值的列
- r - 当grid.arrange()时如何对齐我的ggplots?
- powershell - 如何在 windows10 上安装 PlaidMl
- mysql - 仅在第一次匹配时加入 SQL
- android-studio - 要求我创建一个已经存在的“activity_main.xml”文件时出错
- c - 如何修复在 else if 语句中在 C 中检测到的堆栈粉碎?