如何在Python中自动将JSON对象插入SQL Server？

Question

我正在从API中检索一些数据，并将其插入本地SQL Server数据库以进行数据分析

我想根据收到的Json对象结构创建表并在SQL Server中填充。是否有任何lib/frameworkpython可以自动执行此操作

我正在使用pyodbc插入数据，如下所示。但是，我需要在SQL Server中手动创建每个表，并在python脚本上逐个字段地写下以插入数据

response = requests.get(url, headers=headers , auth=auth )
parseResponse = json.loads(response.text) 

conn = pyodbc.connect(constant.DW_CONNECTION)
cursor = conn.cursor()

for record in parseResponse:
    print( "Insert Into LeadSource (RequestGuid, [Key], Description, LocationNumber, Inactive, ShowOnline) values " + json.dumps(convert(record)) )
    cursor.execute("Insert Into LeadSource (RequestGuid, [Key], Description, LocationNumber, Inactive, ShowOnline) values (?, ?, ?, ?, ?, ?)", ( record['RequestGuid'], record['Key'], record['Description'], record['LocationNumber'], record['Inactive'], record['ShowOnline'] ) )
conn.commit()
conn.close()

Answer 1

谢谢你，戈尔

这就是我要找的。最终代码：

response = requests.get(url, headers=headers , auth=auth )
parseResponse = json.loads(response.text) 

# Converting the JSON text to Pandas Dataframe
df = pd.read_json(response.text)

# Create an in-memory SQLite database.
engine = sal.create_engine(constant.DW_STR_CONNECTION)
conn = engine.connect()

# Create and insert the entity 'leads' into DW MSSQL
df.to_sql('LeadSource', con=engine, if_exists='replace', index_label='id')

DW_STR_连接应类似于：

服务器=主机名\\SqlInstance

DW_STR_CONNECTION='mssql+pyodbc://'+username+'：'+password+'@'+server+'/'+database+'？driver=SQL server？Trusted_CONNECTION=yes'