Python向同数据库两张表插入数据时出现Cursor closed报错

错误原因 #

你遇到的pymysql.err.ProgrammingError: Cursor closed报错核心原因有两个：

• cursor对象创建位置不对，大概率是将cursor定义在了for循环内部，或者cursor的上下文管理器（with cursor() as ...）的作用域只覆盖了for循环内的插入操作，循环跑完后cursor被自动释放关闭，执行循环外的第二条插入时就找不到可用的cursor了
• 你写的SQL占位符是% s（中间带空格），pymysql的标准占位符是无空格的%s，错误的占位符格式会触发执行异常，也可能提前导致cursor被关闭

修复步骤 #

1. 调整cursor的作用域，确保所有数据库操作都在cursor的活跃范围内完成，所有插入操作执行完成后再统一提交事务、释放资源
2. 修正所有SQL语句中的占位符格式，删除%和s之间的空格
3. 增加异常捕获逻辑，出现错误时回滚事务，避免数据不一致

修正后的代码示例 #

# 假设你已经正确初始化了数据库连接对象connection
with connection:
    # 在connection上下文内创建cursor，确保所有操作完成前cursor不会被关闭
    with connection.cursor() as cursor:
        try:
            # for循环内的user_repo表插入操作，替换成你自己的循环遍历对象
            for repo in your_repo_list:
                sql = "INSERT INTO user_repo (user_id, github_id, repo_id, user_connection, repo_name, contributors, total_commits, commit_by_user, created_date, last_commit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                cursor.execute(sql, (
                    initial_details['id'],               
                    username,
                    all_repo_data[repo['name']]['id'],
                    all_repo_data[repo['name']]['owner'],
                    all_repo_data[repo['name']]['name'],
                    all_repo_data[repo['name']]['contributors'],
                    all_repo_data[repo['name']]['total_commits'],
                    all_repo_data[repo['name']]['commit_by_user'],
                    all_repo_data[repo['name']]['created_at'],
                    all_repo_data[repo['name']]['updated_at'],
                ))
            # 循环外的initialdetails表插入操作
            sql = "INSERT INTO initialdetails (github_id, name, email, user_id, location, followers, following,total_commits,total_stars,total_repos) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (
                initial_details['username'],
                initial_details['name'],
                initial_details['email'],
                initial_details['id'],
                initial_details['location'],
                initial_details['followers'],
                initial_details['following'],
                Total_commits_of_user,
                total_stars,
                total_repos,
            ))
            # 所有操作完成后统一提交
            connection.commit()
        except Exception as e:
            # 出错时回滚事务
            connection.rollback()
            print(f"操作出错，已回滚：{e}")

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