Python操作MySQL：单查作者正常，函数内遍历查询无结果求助

我要实现书籍元数据自动插入MySQL数据库，计划基于作者集合创建字典，后续用字典将书籍对象中的作者替换为对应的author_id再入库。

示例作者集合：

authors_set = {'Maurice Blanchot', 'Paul Celan', 'Jean-Pierre Martinet', 'Yves Citton'}

我编写了如下函数：

AUTHOR_EXISTENCE = "SELECT id, full_name FROM authors WHERE full_name = %s"

def create_dict(iterable, query):
    dictionary = {}
    mysql_db = ms.connect(**mysql_conn_params)
    cur = mysql_db.cursor(buffered=True)        
    for author in iterable:
        if cur.execute(query, [author]):
            for id, value in cur:
                dictionary[value] = id
        else:
            dictionary[author] = author
    return dictionary

authors_dict = create_dict(authors_set, AUTHOR_EXISTENCE)

但该函数始终返回空字典，我确认至少有一个作者（如Yves Citton）存在于数据库中，单独查询能正常返回结果：

mysql_db = ms.connect(**mysql_conn_params)
cur = mysql_db.cursor(buffered=True)
cur.execute(AUTHOR_EXISTENCE, ['Yves Citton'])
for id, value in cur:
    print(id, value)

返回结果：2 Yves Citton

已查阅MySQL官方文档，未找到问题原因，恳请帮忙排查解决。

问题排查与修复 #

1. 核心问题：连接与游标管理不当

原函数在循环内重复创建数据库连接，且未正确关闭游标和连接，导致游标状态异常，无法正确获取后续查询结果。同时，buffered=True的游标在未处理完前一次结果时，后续查询会受到影响。

2. 修复后的代码

AUTHOR_EXISTENCE = "SELECT id, full_name FROM authors WHERE full_name = %s"

def create_dict(iterable, query):
    dictionary = {}
    mysql_db = ms.connect(**mysql_conn_params)
    try:
        cur = mysql_db.cursor(buffered=True)        
        for author in iterable:
            # 执行查询，获取匹配行数
            row_count = cur.execute(query, (author,))
            if row_count > 0:
                # 因full_name应为唯一值，直接用fetchone获取单条结果
                author_id, full_name = cur.fetchone()
                dictionary[full_name] = author_id
            else:
                dictionary[author] = author
    finally:
        # 确保游标和连接关闭，避免资源泄漏
        cur.close()
        mysql_db.close()
    return dictionary

authors_dict = create_dict(authors_set, AUTHOR_EXISTENCE)

3. 额外优化点

• 参数传递改用元组(author,)，符合MySQLdb的规范用法，避免潜在的参数解析问题。
• 利用fetchone()替代循环读取结果，因full_name应是唯一约束字段，单条读取更高效。
• 添加try...finally确保资源释放，避免数据库连接泄漏。

4. 可选：增加异常捕获（方便排查错误）

AUTHOR_EXISTENCE = "SELECT id, full_name FROM authors WHERE full_name = %s"

def create_dict(iterable, query):
    dictionary = {}
    try:
        mysql_db = ms.connect(**mysql_conn_params)
        cur = mysql_db.cursor(buffered=True)        
        for author in iterable:
            try:
                row_count = cur.execute(query, (author,))
                if row_count > 0:
                    author_id, full_name = cur.fetchone()
                    dictionary[full_name] = author_id
                else:
                    dictionary[author] = author
            except Exception as e:
                print(f"查询作者 {author} 出错: {str(e)}")
                dictionary[author] = author
    except Exception as e:
        print(f"数据库连接出错: {str(e)}")
    finally:
        if 'cur' in locals():
            cur.close()
        if 'mysql_db' in locals():
            mysql_db.close()
    return dictionary

authors_dict = create_dict(authors_set, AUTHOR_EXISTENCE)

内容的提问来源于stack exchange，提问作者HerrK