python导入多级目录 python批量导入

转载

mob64ca14031c97 2023-10-14 03:13:00

文章标签 python导入多级目录字段建表数据库 文章分类 Python 后端开发

目录
1.序言
2.逻辑思路
3.代码技术要点
4.完整代码实现

1.序言

在数据采集时，可能会从别的部门得到的一堆的Excel文档，这些文档零零散散，不好做数据分析，需要把这些数据存入数据库中，方便管理和分析。

文档数量较少时，手工把Excel数据表存入数据库，工作量还不是很大，但是当有成百上千个文档时，一个一个录入，这就需要费很多时间了。

这时候，使用Python进行数据批量导入就省时省力得多，而且这个程序保存下来后，可以长期利用，一劳永逸。

2.逻辑思路

实现的逻辑图

python导入多级目录 python批量导入_字段

3.代码技术要点

定义一个汉字转拼音的函数，获取汉字的首字母小写

def get_fist_name(long_str):
    return pinyin.get_initial(long_str, delimiter="").lower()

根据postgressql语法构造SQL语句，包括建表语句和字段注释语句。

如果只处理一张表还好，因为表的字段数量是固定的，但是当批量处理不同字段数量的表时，这个难度有很大提升，方法是计算总字段数N，N-1个(%s,)相乘，后面再加一个(%s)。

print("insert into %s values(%s%s)"%(a,"%s,"*(len(N)-1),'%s'))

out: insert into table_name values(%s,%s,%s,%s,%s)

为了满足SQL语句要求，要去掉括号。读取第一行，用split去掉中文或英文括号后面的内容，只保留括号前的内容（单位(元)–>单位），再构造SQL语句。

file_str=""
for i  in range(len(data.loc[0])):
    column_English=get_fist_name(data.loc[0][i].split('（')[0].split('(')[0])
    column_English_name.append(column_English +' '+ 'VARCHAR')  #去除中文括号
    file_title_English='gn'+'_'+get_fist_name(root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0])

    column_name=get_fist_name(data.values.tolist()[0][i].split('（')[0].split('(')[0])
    file_str=file_str+"comment on column %s.%s is '%s';"%(file_title_English,column_name,data.values.tolist()[0][i])

构造一个标准化的标题：广西_单位名称_文件名称

file_title='广西'+'_'+root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0]

使用try----except来执行SQL语句

try:          
    #建表
    cursor.execute("CREATE TABLE {}(%s)".format(file_title_English)%",".join(column_English_name))
    #添加表注释
    cursor.execute("comment on table {} is '{}';".format(file_title_English,file_title) )
    #添加字段注释
    cursor.execute( file_str )

    #读取表的内容转换成list格式，便于批量存入数据库
    data_list=data.values.tolist()[1:]

    #插入数据              
    sql = "insert into %s values(%s%s)"%(file_title_English,"%s,"*(len(data.loc[0])-1),'%s')
    cursor.executemany(sql, data_list)  

    #提交SQL作业
    conn.commit()  

except Exception as e:
    print (e)
    print(name)
    pass

对于需要导入的Excel文件不能缺失第一行标题，标准的格式如下：

python导入多级目录 python批量导入_建表_02

4.完整代码

1.单文件导入模式

在以下代码中修改file_name和root就可以实现一个个文件导入数据库，这样做的好处是在批量导入报错的情况下，方便修改错误的地方。

import psycopg2
import pandas as pd
import pinyin
import shutil 
import os

#获取汉字首字母
def get_fist_name(long_str):
    return pinyin.get_initial(long_str, delimiter="").lower()

#创建游标，链接数据库
conn=psycopg2.connect(database='gndsj',user='postgres',password='postgres',host='172.17.5.99',port='5432')
cursor=conn.cursor()

file_name='信息汇总表'
root=r'F:\大数据\数据清洗入库\正在入库数据1\单位名称'

path=os.path.join(root,file_name+'.xlsx')
data=pd.read_excel(path)       #读取文件内容
data.fillna('')       #去掉空格单元格

#读取表的字段名称
column_English_name=[]  #获取中文名称，转换成英文，用于建表

#判断两列的拼音是否相同，相同则要改成+1，因为建表时不能有相同字段
for i in range(len(data.loc[0])):
    for j in range(1,len(data.loc[0])):
        if i!=j and get_fist_name(data.values.tolist()[0][i].split('（')[0].split('(')[0])==get_fist_name(data.values.tolist()[0][j].split('（')[0].split('(')[0]):                    
            data.loc[0][j].split('（')[0].split('(')[0]=data.loc[0][j].split('（')[0].split('(')[0]+'1'
            data.loc[0][i].split('（')[0].split('(')[0]=data.loc[0][i].split('（')[0].split('(')[0]
        break

file_str=""
for i  in range(len(data.loc[0])):
    column_English=get_fist_name(data.loc[0][i].split('（')[0].split('(')[0])
    column_English_name.append(column_English +' '+ 'VARCHAR')  #去除中文括号
    file_title_English='gn'+'_'+get_fist_name(root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0])

    column_name=get_fist_name(data.values.tolist()[0][i].split('（')[0].split('(')[0])
    file_str=file_str+"comment on column %s.%s is '%s';"%(file_title_English,column_name,data.values.tolist()[0][i]) 
    
file_title='广西'+'_'+root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0]   #保留原样中文
#     department=root.split('\\')[-1]

try:          
    #建表
    cursor.execute("CREATE TABLE {}(%s)".format(file_title_English)%",".join(column_English_name))
    #添加表注释
    cursor.execute("comment on table {} is '{}';".format(file_title_English,file_title) )
    #添加字段注释
    cursor.execute( file_str )

    #读取表的内容转换成list格式，便于批量存入数据库
    data_list=data.values.tolist()[1:]

    #插入数据              
    sql = "insert into %s values(%s%s)"%(file_title_English,"%s,"*(len(data.loc[0])-1),'%s')
    cursor.executemany(sql, data_list)  

    #提交SQL作业
    conn.commit()  

except Exception as e:
    print (e)
    print(name)
    pass
                   
cursor.close()
conn.close()

2.批量导入模式

在数据格式统一，满足SQL语法的前提下，使用以下这个程度可以实现数据自动化导入，剩下的时间就是到处逛逛，上个厕所、喝杯茶、撩撩妹。

import psycopg2
import pandas as pd
import pinyin
import shutil 
import os

#获取汉字首字母
def get_fist_name(long_str):
    return pinyin.get_initial(long_str, delimiter="").lower()

#建立已入库文件
def make_file(path_out,department):
    if os.path.exists(path_out+'\\'+department):
        pass
    else:
        os.mkdir(path_out+'\\'+department)
        
path_from=r'F:\大数据\数据清洗入库\正在入库数据1\单位名称'

#存入数据库的表名称存放地址
data_insert=[]  

#创建游标，链接数据库
conn=psycopg2.connect(database='gndsj',user='postgres',password='postgres',host='172.17.5.99',port='5432')
cursor=conn.cursor()

for root,dirs,files in os.walk(path_from):
    for name in files:
        path=os.path.join(root,name)   #获取文件绝对路径
        file_name=name.split('.')[0]   #获取文件表名称
        data=pd.read_excel(path)       #读取文件内容
        
        #读取表的字段名称
        column_English_name=[]  #获取中文名称，转换成英文，用于建表
#         column_Chinese_name=[]  #获取中文名称，用于注释字段

        #判断两列的拼音是否相同，相同则要改成+1，因为建表时不能有相同字段
        for i in range(len(data.loc[0])):
            for j in range(1,len(data.loc[0])):
                if get_fist_name(data.values.tolist()[0][i].split('（')[0].split('(')[0])==get_fist_name(data.values.tolist()[0][j].split('（')[0].split('(')[0]):                    
                    data.loc[0][j]=data.loc[0][j]+'1'
                break


        file_str=""
        for i  in range(len(data.loc[0])):
            column_English=get_fist_name(data.loc[0][i].split('（')[0].split('(')[0])
            column_English_name.append(column_English +' '+ 'VARCHAR')  #去除中文括号
#             column_Chinese_name.append(data.loc[1][i])   #保留原样中文
            file_title_English='gn'+'_'+get_fist_name(root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0])
    
            column_name=get_fist_name(data.values.tolist()[0][i].split('（')[0].split('(')[0])
            file_str=file_str+"comment on column %s.%s is '%s';"%(file_title_English,column_name,data.values.tolist()[0][i]) 
        
        #读取表的名称，用于建表注释  
        file_title='广西'+'_'+root.split('\\')[-1]+'_'+file_name.split('（')[0].split('(')[0]   #保留原样中文
        department=root.split('\\')[-1]
        
        try:          
            #建表
            cursor.execute("CREATE TABLE {}(%s)".format(file_title_English)%",".join(column_English_name))
            #添加表注释
            cursor.execute("comment on table {} is '{}';".format(file_title_English,file_title) )
            #添加字段注释
            cursor.execute( file_str )

            #读取表的内容转换成list格式，便于批量存入数据库
            data_list=data.values.tolist()[1:]

            #插入数据              
            sql = "insert into %s values(%s%s)"%(file_title_English,"%s,"*(len(data.loc[1])-1),'%s')
            cursor.executemany(sql, data_list)  

            #获取存入数据库的表
            data_insert.append(file_name)  
            
            #提交SQL作业
            conn.commit()  
                                                                        
        except Exception as e:
            print (e)
            print(name)
            pass
                   
cursor.close()
conn.close()

修复一个bug

在建表时，varchar没有设定字符长度时，默认长度为0，为了让它全部设定为255，需在建表时加以说明

ALTER TABLE table_name alter COLUMN column_name type varchar(255);

column_English_name.append(column_English +' '+ 'VARCHAR')  
变成
column_English_name.append(column_English +' '+ 'VARCHAR(255)')

在建表时没有这句的话，可以批量修改。

运行下列程序—>导出结果到Excel—>从Excel复制程序到数据库管理工具Navicat运行

select c.relname 表名,a.attname 字段 ,
'alter table "public"."'   ||c.relname||  '"'|| ' alter COLUMN ' ||  a.attname  || ' type varchar(255)'||';'
from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum

运行这段程序时，如果遇到文本内容超长时，仍然会报错，可以先屏蔽这一行，往后继续运行，运行完了手工修改，或修改为格式 text 在运行。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。