使用pytds TVP插入字典列表到SQL Server报错KeyError:0求解决

需要通过pytds的Table-Valued Parameter（TVP）将字典列表写入SQL Server数据库，使用元组列表时可正常运行，但担心元组顺序调换导致数据错位，改用字典列表后出现KeyError: 0报错。

原代码 #

def call_sproc(val):
    server = 'my_server'
    user = 'my_user'
    password = 'secret'
    database = 'my_db'
    
    try:
        conn = pytds.connect(server=server, user=user, password=password, database=database)
        cursor = conn.cursor()

        tvp = pytds.TableValuedParam(type_name='dbo.tbl', rows=val)
        cursor.callproc("dbo.stproc", (tvp,))
        conn.commit()
        cursor.close()
        conn.close()

    except pytds.Error as e:
        print("Connection issue:", e)

# 测试数据
val=[{'id': 'st001', 'name':'abc'}, {'id': 'st002', 'name':'pqr'}, {'id': 'st003', 'name':'xyz'}]
call_sproc(val)

报错信息 #

Traceback (most recent call last):
File "c:/Users/mydesk/Desktop/test/insertpytds.py", line 60, in
connect_and_call_stored_procedure(val)
File "c:/Users/mydesk/Desktop/test/insertpytds.py", line 23, in call_sproc
cursor.callproc("[dbo].[stproc]", (tvp,))
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init_.py", line 597, in callproc
return self.callproc(procname, parameters)
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init.py", line 562, in _callproc
self._exec_with_retry(lambda: self.session.submit_rpc(procname, parameters, 0))
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init.py", line 668, in exec_with_retry
return fun()
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init.py", line 562, in
self._exec_with_retry(lambda: self._session.submit_rpc(procname, parameters, 0))
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds\tds.py", line 1032, in submit_rpc
serializer.write(w, param.value)
File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds\tds_types.py", line 2302, in write
self._columns_serializers[i].write(w, row[i])
KeyError: 0

解决方案 #

pytds的TableValuedParam默认将行数据视为序列（元组/列表），通过索引取值，因此传入字典时会触发索引KeyError。要支持字典按列名映射，需显式指定columns参数，该参数的顺序必须与SQL Server中定义的TVP类型（dbo.tbl）的列顺序完全一致，这样pytds会按列名从字典中提取对应值，同时匹配数据库列顺序，避免数据错位。

修改后的代码如下：

def call_sproc(val):
    server = 'my_server'
    user = 'my_user'
    password = 'secret'
    database = 'my_db'
    # 定义TVP列名，顺序需与SQL Server的dbo.tbl类型列顺序一致
    tvp_columns = ['id', 'name']
    
    try:
        conn = pytds.connect(server=server, user=user, password=password, database=database)
        cursor = conn.cursor()

        # 传入columns参数，指定列名映射
        tvp = pytds.TableValuedParam(type_name='dbo.tbl', rows=val, columns=tvp_columns)
        cursor.callproc("dbo.stproc", (tvp,))
        conn.commit()
        cursor.close()
        conn.close()

    except pytds.Error as e:
        print("Connection issue:", e)

# 测试数据，字典键顺序不影响映射
val=[{'name':'abc', 'id': 'st001'}, {'id': 'st002', 'name':'pqr'}, {'name':'xyz', 'id': 'st003'}]
call_sproc(val)

关键说明 #

• columns参数的顺序必须严格匹配SQL Server中TVP类型的列定义顺序，确保数据正确写入对应列。
• 传入的字典只需包含所有指定列名的键，键的顺序不影响最终写入结果，彻底避免元组顺序错位的风险。

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