往SQL Server数据库中插入数据/执行SQL语句,遵循以下步骤:
- 建立与数据库的连接,配置信息同上一节查询数据。
- 插入数据时记得将数据表头与数据库表头进行对应。
- 插入数据时记得将数据类型转为tuple,否则无法插入。
- 执行完sql语句后记得commit,如遇失败记得rollback
import os,sys
import pymssql
import time
import pandas as pd
import warnings
from tabulate import tabulate
warnings.filterwarnings('ignore')
FilePath = os.path.abspath(__file__)
CurrentPath = os.path.dirname(FilePath)
FatherPath = os.path.dirname(CurrentPath)
import configparser
cf = configparser.ConfigParser()
# 出于安全性考虑,配置信息从外部导入
cf.read(os.path.join(FatherPath, 'doc/config.ini'))
mssql_dict = dict(cf.items('mssql-config'))
sys.path.insert(0, CurrentPath)
today = time.strftime('%Y%m%d',time.localtime(time.time()))
class MssqlConnect(object):
def __init__(self):
self.host = mssql_dict['host']
self.user = mssql_dict['user']
self.pwd = mssql_dict['pwd']
self.db = mssql_dict['db']
def insert2table(self,tablename,df):
connect = pymssql.connect(self.host,self.user,self.pwd,self.db)
# 一次插入多条数据
cols = ','.join(df.columns)
val = (tuple(i) for i in df.values) # 这里需要转成tuple类型才能写入到数据库中
sqlstr = "INSERT INTO {}({}) VALUES ({})".format(tablename,cols,','.join(['%s']*len(df.columns)))
try:
with connect.cursor() as cursor:
# 在插入之前我们需要先删除部分数据,该部分根据需要可进行注释
# sqlstr_del = F'delete a from {tablename} a where upload_date = {today}'
# cursor.execute(sqlstr_del)
cursor.executemany(sqlstr, val)
connect.commit()
logger.info('>>> 插入数据成功,表 {} 共插入 {} 行数据'.format(tablename,len(df)))
# logger.info('插入数据详情:\n\n{}'.format(tabulate(df.head(),headers=df.columns)))
except Exception as e:
logger.info('>>> 插入数据失败', e)
connect.rollback()
finally:
connect.close()
if __name__ == '__main__':
tablename = 't_test'
df = pd.DataFrame({'col1':[1,2,3],'col2':['a','b','c']})
print(tabulate(df.head(),headers=df.columns))
report = MssqlConnect().insert2table(tablename,df)