Python向MSSQL插入JSON字典遇阻：关联与插入双问题求助

问题1：基于'id'字段关联JSON对象与DataFrame #

从API获取JSON对象列表，需将每个JSON对象作为一列加入由该列表转换的标准DataFrame中。目前通过索引关联Series与DataFrame实现，但希望改为基于JSON字典和DataFrame中的'id'字段关联，消除顺序依赖，确保对应关系准确。

问题2：含JSON字典的列插入MSSQL报错 #

关联后的DataFrame使用to_sql插入MSSQL时，含JSON字典的'Json_Series'列报错：

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('Invalid parameter type. param-index=0 param-type=dict', 'HY105')

尝试过astype('str')无效，希望找到无需自定义插入语句的通用解决方案。

附相关代码 #

Python数据处理代码

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

data = [{"id": 1, "value": 2}, {"id": 3, "value": 4}]
df = pd.Series(data, name='Json_Series')
df2= pd.DataFrame(data)
inner_merged = pd.merge(df, df2, left_index=True, right_index=True)
print(df)
print(df2)
# inner_merged['Json_Series'] = inner_merged['Json_Series'].astype('str')
print(inner_merged)

MSSQL建表语句

CREATE TABLE [dbo].[TestInsert_JsonObject_Python](
    [Json_Series] [varchar](500) NULL,
    [id] [int] NULL,
    [value] int Null,
) 
GO

CREATE TABLE [dbo].[TestInsert_NoJsonObject_Python](
    [id] [int] NULL,
    [value] int Null,
) 
GO

数据库插入代码

server = 'enteryourserver'
database = 'enteryourdatabase'
username = 'enteryourusername'
password = 'enteryourpassword'
driver = '{ODBC Driver 18 for SQL Server}'
table1 = 'TestInsert_NoJsonObject_Python'
table2 = 'TestInsert_JsonObject_Python'
schema = 'dbo'

connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = create_engine(connection_url)

df2.to_sql(table1, con=engine, schema= schema, if_exists='replace', index=False)
inner_merged.to_sql(table2, con=engine, schema= schema, if_exists='replace', index=False)

针对问题1：基于'id'字段关联的实现 #

不需要依赖索引关联，直接从原始JSON列表中提取'id'作为Series的索引，再和DataFrame按'id'字段合并，彻底消除顺序依赖：

data = [{"id": 1, "value": 2}, {"id": 3, "value": 4}]
# 提取每个JSON的id作为Series的索引
df = pd.Series(data, index=[item['id'] for item in data], name='Json_Series')
df2 = pd.DataFrame(data)
# 按'id'字段精准匹配合并
inner_merged = pd.merge(df.reset_index(names='id'), df2, on='id')
print(inner_merged)

针对问题2：解决JSON字典插入MSSQL的报错 #

astype('str')无效是因为Pandas仍会将字典识别为非字符串类型，需用json.dumps将字典序列化为标准JSON字符串，确保字段类型与MSSQL的varchar兼容：

完整处理代码 #

import pandas as pd
import json
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

data = [{"id": 1, "value": 2}, {"id": 3, "value": 4}]
df = pd.Series(data, index=[item['id'] for item in data], name='Json_Series')
df2 = pd.DataFrame(data)
inner_merged = pd.merge(df.reset_index(names='id'), df2, on='id')

# 将字典列序列化为标准JSON字符串
inner_merged['Json_Series'] = inner_merged['Json_Series'].apply(json.dumps)

# 数据库插入执行
server = 'enteryourserver'
database = 'enteryourdatabase'
username = 'enteryourusername'
password = 'enteryourpassword'
driver = '{ODBC Driver 18 for SQL Server}'
table2 = 'TestInsert_JsonObject_Python'
schema = 'dbo'

connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = create_engine(connection_url)

inner_merged.to_sql(table2, con=engine, schema=schema, if_exists='replace', index=False)

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