英文:
Empty result when using cursor.fetchall() or cursor.fetchone()
问题
以下是翻译好的部分：
有一个简单的MySQL表格
| id | name |
|---|---|
| 1 | name_1 |
| ... | ... |
我正在尝试通过以下代码根据name获取id（顺便说一下，它是主键）
import mysql.connector
# ...创建连接
def get_id(name):
cursor = connection.cursor()
query = """SELECT id FROM table WHERE name = %s"""
try:
cursor.executemany(query, [name])
# connection.commit() - it didn't help, and I use connection.autocommit = True
result = cursor.fetchall() # or cursor.fetchone()
return result
except Error as err:
print(f"发生错误 '{err}'")
但这只返回一个空列表 [] 或 None（取决于提取方法）。
我已经尝试过更改代码，插入检查，例如：
if result != None:
return(result)
以及使用 cursor.stored_results()。它都没有帮助。
此外，如果在MySQL Workbench中使用 SELECT id FROM table WHERE name = "name_1"，它会输出正确的id。所以，我不明白问题可能是什么...
英文:
There is a simple table in MySQL
| id | name |
|---|---|
| 1 | name_1 |
| ... | ... |
And I'm trying to get the id (it's the primary key by the way) number by name, using this code
import mysql.connector
# ...create connection
def get_id(name):
cursor = connection.cursor()
query = """SELECT id FROM table WHERE name = %s"""
try:
cursor.executemany(query, [name])
# connection.commit() - it didn't help, and I use connection.autocommit = True
result = cursor.fetchall() # or cursor.fetchone()
return result
except Error as err:
print(f"The error '{err}' occurred")
But this returns only an empty list [] or None (depending on the fetch method).
I have already tried changing the code, inserting checks like:
if result != None:
return(result)
And using cursor.stored_results(). It doesn't help.
Also, if using SELECT id FROM table WHERE name = "name_1" in MySQL Workbench, it outputs the correct id. So, I don't understand what the problem might be...
答案1
得分: 1
SELECT语句不能与exectutemany()一起使用。
将调用更改为executemany()，将元组作为第二个参数传递。
query = "SELECT id FROM table WHERE name = %s" cursor.execute(query, (name,))
有关executemany()示例，请参见API文档。
英文:
The SELECT statement won't work with the exectutemany().
Change call to executemany() to execute() with tuple as the second argument.
query = "SELECT id FROM table WHERE name = %s"
cursor.execute(query, (name,))
For executemany() examples, see API docs
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论