如何将MongoDB数据导入MySQL表？解决‘cannot set options’报错

错误原因 #

• 第一次插入channel数据后就关闭了mycursor，后续又尝试用已关闭的游标执行插入操作，触发报错
• 读取playlist数据的逻辑错误：sqlData是channel列表，应该从当前循环的channel对象中获取playlist数据，而非直接从sqlData取值
• 游标创建、关闭的时机混乱，循环内重复操作游标导致连接状态异常

修正后的代码 #

def sql_connect(sqlData):
    # 建立MySQL连接
    mysql_connect = mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="tb_d",
        port="3306"
    )
    # 创建游标（全程复用一个游标即可，不需要多次创建/关闭）
    mycursor = mysql_connect.cursor(buffered=True)

    for channel in sqlData:
        # 插入channel数据
        channel_info = channel['channel_id']
        channel_id = channel_info['channel_id']
        channel_name = channel_info['channel_name']
        channel_views = channel_info['channel_views']
        channel_description = channel_info['channel_des']
        channel_status = channel_info['channel_status']
  
        mycursor.execute(
            "INSERT INTO channel (channel_id, channel_name, channel_views, channel_description, channel_status) VALUES (%s, %s, %s, %s, %s)",
            (channel_id, channel_name, channel_views, channel_description, channel_status)
        )
        print("Data saved to CHN TAB")  

        # 处理当前channel对应的playlist数据
        playlist_data = channel['playlist_id']
        playlist_id = playlist_data['playlist_id']
        playlist_channel_id = playlist_data['channel_id']
        playlist_names = playlist_data['playlist_name']

        # 批量插入playlist数据
        for playlist_name in playlist_names:
            mycursor.execute(
                "INSERT INTO playlist (playlist_id, channel_id, playlist_name) VALUES (%s, %s, %s)",
                (playlist_id, playlist_channel_id, playlist_name)
            )
        print("Data saved to playlist table")

    # 所有数据插入完成后统一提交，减少IO操作
    mysql_connect.commit()
    # 最后统一关闭游标和连接
    mycursor.close()
    mysql_connect.close()

关键修复点 #

• 全程复用一个游标，不在循环内关闭游标，避免重复创建/关闭导致的状态异常
• 修正playlist数据的读取逻辑，从当前循环的channel对象中获取对应数据
• 将提交操作移到循环外，批量提交提升性能，也避免频繁提交引发的连接问题
• 最后统一关闭游标和数据库连接，规范资源管理

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