如何将Python字典插入SQL Server数据库表？

I have a dictionary with 3 keys which correspond to field names in a SQL Server table. The values of these keys come from an excel file and I store this dictionary in a dataframe which I now need to insert into a SQL table. This can all be seen in the code below:

我有一个包含3个键的字典,它们对应于SQL Server表中的字段名称。这些键的值来自excel文件,我将这个字典存储在一个数据框中,我现在需要将其插入到SQL表中。这可以在下面的代码中看到:

import pandas as pd
import pymssql
df=[]
fp = "file path" 
data = pd.read_excel(fp,sheetname ="CRM View" )
row_date = data.loc[3, ]
row_sita = "ABZPD"
row_event = data.iloc[12, :]        
df = pd.DataFrame({'date': row_date,
                           'sita': row_sita,
                           'event': row_event
                           }, index=None)
df = df[4:]
df = df.fillna("")
print(df)

My question is how do I insert this dictionary into a SQL table now?

我的问题是如何将此字典插入到SQL表中?

Also, as a side note, this code is part of a loop which needs to go through several excel files one by one, insert the data into dictionary then into SQL then delete the data in the dictionary and start again with the next excel file.

另外,作为旁注,此代码是循环的一部分,需要逐个浏览多个excel文件,将数据插入字典然后插入SQL,然后删除字典中的数据并再次使用下一个excel文件。

1 个解决方案

#1

You could try something like this:

你可以尝试这样的事情:

import MySQLdb
# connect
conn = MySQLdb.connect("127.0.0.1","username","passwore","table")
x = conn.cursor()

# write
x.execute('INSERT into table (row_date, sita, event) values ("%d", "%d", "%d")' % (row_date, sita, event))

# close
conn.commit()
conn.close()

You might have to change it a little based on your SQL restrictions, but should give you a good start anyway.

您可能必须根据SQL限制稍微更改它,但无论如何都应该给您一个良好的开端。

#1