目录
1.序言
2.逻辑思路
3.代码技术要点
4.完整代码实现
1.序言
在数据采集时，可能会从别的部门得到的一堆的Excel文档，这些文档零零散散，不好做数据分析，需要把这些数据存入数据库中，方便管理和分析。
文档数量较少时，手工把Excel数据表存入数据库，工作量还不是很大，但是当有成百上千个文档时，一个一个录入，这就需要费很多时间了。
这时候，使用Python进行数据批量导入就省时省力得多，而且这个程序保存下来后，可以长期利用，一劳永逸。
2.逻辑思路
实现的逻辑图
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文件不能缺失第一行标题，标准的格式如下：
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 在运行。
281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
