Python中pyodbc查询结果插入MariaDB时executemany报错求助

场景复现 #

通过pyodbc查询源数据库获取数据：

# coding: cp1252 -*-
import pyodbc
import mariadb

# 省略数据库连接配置
cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
cursor.execute("select cli, lib from ficcli")
result = cursor.fetchmany(3)

尝试将查询结果批量插入MariaDB：

cnxn2 = mariadb.connect(...)
# 省略数据库连接配置
mySql_insert_query = "INSERT INTO sxfcli (cli,lib) values(?,?);"
cursor2 = cnxn2.cursor()
cursor2.executemany(mySql_insert_query, result)
cnxn2.commit()

报错信息 #

Traceback (most recent call last):
File "<pyshell#42>", line 1, in <module>
cursor2.executemany(mySql_insert_query,test_result[0])
File "C:\Users\alain\AppData\Local\Programs\Python\Python310\lib\site-packages\mariadb\cursors.py", line 350, in executemany
self._parse_execute(statement, parameters[0], is_bulk=True)
File "C:\Users\alain\AppData\Local\Programs\Python\Python310\lib\site-packages\mariadb\cursors.py", line 218, in _parse_execute
self._check_execute_params()
File "C:\Users\alain\AppData\Local\Programs\Python\Python310\lib\site-packages\mariadb\cursors.py", line 147, in _check_execute_params
raise mariadb.ProgrammingError("Data argument must be "
mariadb.ProgrammingError: Data argument must be Tuple or List

排查情况 #

• result的类型为<class 'list'>
• result内容显示为[('totoco ', ' toto limited ')]
• 手动创建相同内容的列表test=[('totoco ', ' toto limited ')]执行插入成功，但result==test返回False

解决方案 #

问题核心是pyodbc的fetchmany()返回的列表中，元素并非普通元组，而是pyodbc.Row自定义类实例——虽然外观和元组类似，但MariaDB驱动的executemany要求参数必须是纯元组/列表组成的序列，因此需要显式转换：

# 将pyodbc返回的Row对象转换为标准元组
converted_result = [tuple(row) for row in result]

# 使用转换后的列表执行批量插入
cursor2.executemany(mySql_insert_query, converted_result)
cnxn2.commit()

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