Python中mysql.connector游标连接异常：跨函数使用失败

问题场景 #

使用Python的mysql.connector操作MySQL时，将功能拆分为初始化连接、创建数据表、记录数据三个独立函数。但从初始化函数返回的游标，在创建表函数中调用时抛出Cursor is not connected错误。将创建表逻辑合并到初始化函数可正常运行，但希望保持功能拆分，调整max-packet size未解决问题。

原代码 #

def initialize_database():
    MySQL = mysql.connector.connect(host=MySQL_HOST,database='records',user=MySQL_USERNAME,password=MySQL_PASSWORD)
    if MySQL.is_connected():
        db_Info = MySQL.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = MySQL.cursor()
        cursor.execute("select database();")
        response = cursor.fetchone()
        return cursor

def database_create_table(device_id:str,device_type:str,device_name:str):
    Query = ""
    cursor = initialize_database()
    if device_type == "light":
        Query = "CREATE TABLE ",device_name," (",device_id," varchar(30) NOT NULL,power_state int NOT NULL,brightness_level int NOT NULL,PRIMARY KEY (",device_id,"))"
    result = cursor.execute(Query)
    print("Table created successfully")

错误信息 #

Traceback (most recent call last):
File "PycharmProjects\tuya-connector-python\example\mainfunctions.py", line 56, in
database_create_table(DEVICES_ID[0],"light","yeah")
File "PycharmProjects\tuya-connector-python\example\mainfunctions.py", line 36, in database_create_table
result = cursor.execute(mySql_Create_Table_Query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "PycharmProjects\tuya-connector-python\venv\Lib\site-packages\mysql\connector\cursor_cext.py", line 302, in execute
raise ProgrammingError("Cursor is not connected", 2055) from err
mysql.connector.errors.ProgrammingError: 2055: Cursor is not connected

错误原因 #

1. 连接对象被垃圾回收：initialize_database函数中的MySQL是局部变量，函数执行完毕后会被Python自动回收，导致依附于该连接的游标失去有效连接。
2. SQL语句拼接错误：用逗号拼接字符串会生成元组，而非合法的SQL字符串，cursor.execute无法识别这种格式。

修复方案 #

1. 返回连接对象+游标，而非仅游标 #

修改初始化函数，同时返回连接和游标，确保连接对象在外部函数中被持有，避免被回收。

2. 正确拼接SQL语句 #

使用f-string或字符串拼接方法生成合法的SQL语句，避免生成元组。

3. 增加异常处理与资源管理（可选） #

添加try-except-finally块，确保连接和游标能正确关闭，避免资源泄漏。

修复后的完整代码 #

import mysql.connector
from mysql.connector import Error

# 替换为你的数据库配置
MySQL_HOST = "your_host"
MySQL_USERNAME = "your_username"
MySQL_PASSWORD = "your_password"

def initialize_database():
    try:
        conn = mysql.connector.connect(
            host=MySQL_HOST,
            database='records',
            user=MySQL_USERNAME,
            password=MySQL_PASSWORD
        )
        if conn.is_connected():
            db_info = conn.get_server_info()
            print(f"Connected to MySQL Server version {db_info}")
            cursor = conn.cursor()
            cursor.execute("select database();")
            db_name = cursor.fetchone()[0]
            print(f"Current database: {db_name}")
            return conn, cursor
    except Error as e:
        print(f"Database connection error: {e}")
        return None, None

def database_create_table(device_id: str, device_type: str, device_name: str):
    conn, cursor = initialize_database()
    if not conn or not cursor:
        print("Failed to initialize database connection")
        return
    
    try:
        if device_type == "light":
            # 用f-string拼接SQL，表名/字段名加反引号避免关键字冲突
            query = f"""CREATE TABLE `{device_name}` (
                        `{device_id}` varchar(30) NOT NULL,
                        power_state int NOT NULL,
                        brightness_level int NOT NULL,
                        PRIMARY KEY (`{device_id}`)
                    )"""
        else:
            print(f"Unsupported device type: {device_type}")
            return
        
        cursor.execute(query)
        conn.commit()  # 创建表需提交事务
        print("Table created successfully")
    except Error as e:
        print(f"Failed to create table: {e}")
    finally:
        # 关闭游标与连接
        if cursor:
            cursor.close()
        if conn.is_connected():
            conn.close()
            print("MySQL connection closed")

# 调用示例
# database_create_table("device_123", "light", "light_device")

关键说明 #

• 必须持有连接对象的引用，否则连接会被回收，游标失效。
• SQL语句中，表名和字段名若包含特殊字符（如空格、关键字），需用反引号`包裹，避免语法错误。
• 创建表后需调用conn.commit()提交事务，确保操作生效。
• 最终要关闭游标和连接，避免占用数据库资源。

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