Python往SQL Server中插入数据

往SQL Server数据库中插入数据/执行SQL语句，遵循以下步骤：

1. 建立与数据库的连接，配置信息同上一节查询数据。
2. 插入数据时记得将数据表头与数据库表头进行对应。
3. 插入数据时记得将数据类型转为tuple，否则无法插入。
4. 执行完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)