用python将excel/csv文件导入数据库

1.初始配置

#初始配置
sql_name="orders"
db_name="test"
wj_path=r"E:\订单模板" #数据源文件夹
#用于创建库的字段表名
data_name=r"E:\订单模板\202011.csv" #获取字段的模板文件

2.打开excel/CSV文件
平台下载的数据需要掐头去尾各4行，使用header和skipfooter参数

1）excel文件

data=pd.read_excel(data_name,header=4,skipfooter = 4) #打开excel文件

2）CSV文件
对于路径中有中文的csv，需要先用open打开实例

f=open(data_name,encoding='GBK') #指定CSV的编码格式
data=pd.read_csv(f,sep=',',header=4,skipfooter = 4) 
f.close()

3.设置主键字段

PK="账务流水号" #新增主键字段，也可以设置为表内已有字段，如日期
PK_zd=["账务流水号"] #需要设置为主键的字段

4.字段集合去重，即如果PK已经存在则只保留1个

zd=set(data.columns)
zd.add(PK)
zd=list(zd)

5.创建表

def c_sql(sql_name,db_name,zd,PK):
    connent = pymysql.connect(host='localhost', user='root', passwd='******', db=db_name, charset='utf8') #连接数据库
    cursor = connent.cursor()#创建游标
    va=""
    spec=['(',"（","—","-","+"] #字段中包含的特殊字符
    for i in zd:
        for j in spec:
            i=i.replace(j,"_") #特殊字符替换为下划线
        i=i.replace(")","")
        i=i.replace("）","") #其余特殊字符直接剔除
        i=i.replace("%","_percent") #更改字段名中的百分比
        if i ==PK:
            va=va+i+" varchar(255) PRIMARY KEY," #主键的设置
        elif ('日期'in i)|('时间' in i):
            va=va+i+" date," #日期或时间需要变更字段类型
        else:
            va=va+i+" varchar(50)," #普通字段的连接
    sql="""create table if not exists  %s (%s) ;"""%(sql_name,va[:-1])
    cursor.execute(sql)
    connent.commit() #提交事务
    cursor.close()#关闭游标
    connent.close()#断开连接

6.写入表

def w_sql(sql_name,data,zd,db_name):
    connent = pymysql.connect(host='localhost', user='root', passwd='******', db=db_name, charset='utf8') #连接数据库
    cursor = connent.cursor()#创建游标
    for i in data.values:
        va=""
        for j in i:
            if pd.isnull(j):
                va=va+","+'null' #缺失值判断和转换
            else:
                va=va+","+'"'+str(j)+'"'
        sql="""insert ignore into %s (%s) values(%s)"""%(sql_name,zd[:-1],va[1:])
        cursor.execute(sql)
    connent.commit() #提交事务
    cursor.close()#关闭游标
    connent.close()#断开连接

7.循环文件写入

wjs=os.listdir(wj_path)
count=0
for wj in wjs:
    count+=1
    data_path=wj_path+'/'+wj
    f=open(data_path,encoding='GBK') 
    data=pd.read_csv(f,sep=',',header=4,skipfooter = 4 ) 
    f.close()
    
    #主键为已有字段
    if (len(PK_zd)==1)&(PK_zd[0] in data.columns): 
        pass
        
    #主键为字段组合
    else:
        data['PK']=""
        for pk in PK_zd: 
            data['PK']=data['PK']+data[pk].astype(str)

	# 表头字段拼接
    new_zd=""
    spec=['(',"（","—","-","+"]
    for i in data.columns:
        #替换掉特殊字符
        for j in spec:
            i=i.replace(j,"_")
        i=i.replace(")","")
        i=i.replace("）","")
        i=i.replace("%","_percent")
        new_zd=new_zd+i+","

	#写入表
    w_sql(sql_name,data,new_zd,db_name)
    print("第 %s 个文件 %s 写入完成 "%(count,wj))
print("所有文件写入完成")