mdb格式数据文件导入postgresQL数据库

导入脚本说明（此脚本运行于linux）：

1.apt-get install mdbtools,安装mdbtools工具

2.将mdb 文件拷贝到linux虚拟机中，修改脚本中mdb文件目录‘dir’

3.修改服务器及数据库配置

4.执行脚本

#-*-encoding:utf-8-*-
importos
importre
importpsycopg2
importcsv

#mdb文件目录
dir=r'/home/kotaimen/mdb_file/'
mdb_tbl_dic={}


defmake_create_sql():
ifos.path.isfile(dir+'create.sql'):
os.remove(dir+'create.sql')


formdb_fileinos.walk(dir):
iflen(mdb_file[2])>0:
forfile_pinmdb_file[2]:
iffile_p[-3:]=='mdb':
printfile_p
cmd='mdb-schema%s>>/home/kotaimen/mdb_file/create.sql'
cmd=cmd%(dir+file_p)
printcmd
os.system(cmd)
cmd='mdb-tables-1%s'%(dir+file_p)
val=os.popen(cmd).read()
mdb_tbl_dic[file_p]=val.split('n')
printmdb_tbl_dic

defmodefy_create_sql():
sql_file_name=dir+'create.sql'
sql_file_name_des=sql_file_name+'_new'
fobj=open(sql_file_name,'r')
fobj_des=open(sql_file_name_des,'w')
foreachlineinfobj:
#判断表名中是否含有空格
ifeachline.find('TABLE')>=0:
ifeachline.find(';')>=0:
start_loc=eachline.find('TABLE')+6
end_loc=eachline.find(';')
tbl_name=eachline[start_loc:end_loc]
eachline=eachline.replace(tbl_name,'"'+tbl_name+'"')
else:
start_loc=eachline.find('TABLE')+6
end_loc=eachline.find('n')
tbl_name=eachline[start_loc:end_loc]
eachline=eachline.replace(tbl_name,'"'+tbl_name+'"')

ifeachline.find('DROPTABLE')>=0:
eachline=eachline.replace('DROPTABLE','DROPTABLEIFEXISTS')
ifeachline.find('Table')>=0:
eachline=eachline.replace('Table','"Table"')
#create语句，最后一行没有逗号
ifeachline.find('Text')>=0andeachline.find(',')>0:
loc=eachline.find('Text')
eachline=eachline[0:loc]+'Text,n'
elifeachline.find('Text')>=0andeachline.find(',')<0:
loc=eachline.find('Text')
eachline=eachline[0:loc]+'Textn'
fobj_des.writelines(eachline)
fobj.close()
fobj_des.close()
os.remove(sql_file_name)
os.rename(sql_file_name_des,sql_file_name)

defmake_insert_csv():
forfile_pinmdb_tbl_dic.keys():
fortblinmdb_tbl_dic[file_p]:
iflen(tbl)>0:
cmd='mdb-export%s%s>%s.csv'%(dir+file_p,'"'+tbl+'"',dir+'"'+tbl+'"')#tbl.replace('','_').replace('&','_'))
os.system(cmd)

defmodefy_insert_CSV():
forsql_fileinos.walk(dir):
iflen(sql_file[2])>0:
forfile_pinsql_file[2]:
iffile_p[-3:]=='csv':
sql_file_name=dir+file_p
sql_file_name_des=sql_file_name+'_new'
fobj=open(sql_file_name,'w')
for(num,val)inenumerate(fobj):
eachline=val
ifnum==0:
col_list=eachline.split(',')
stat='COPY'+'"'+(file_p[0:-4])+'"'+'('#+('%s,'*len(line))[:-1]+')'
forcolincol_list:
ifcol=='Table':
col='"'+'Table'+'"'
ifcol.find('n')>=0:
col.replace('n','')
stat=stat+col+','
stat=stat[:-2]+')'+'FROMSTDINWITHCSV;n'
eachline=stat

fobj_des.writelines(eachline)
fobj.close()
fobj_des.close()
os.remove(sql_file_name)
os.rename(sql_file_name_des,sql_file_name)


definsert_into_database():

cmd='psql-h172.26.11.205-dap_MapMyIndia_full_Sample-Upostgres-f%s2>>log.txt'%(dir+'create.sql')
os.system(cmd)

forsql_fileinos.walk(dir):
iflen(sql_file[2])>0:
forfile_pinsql_file[2]:
printfile_p
iffile_p[-3:]=='csv':
cmd='psql-h172.26.11.205-dap_MapMyIndia_full_Sample-Upostgres-f%s2>>log.txt'%(dir+'"'+file_p+'"')
os.system(cmd)


if__name__=="__main__":
#1.制作mdb文件中所包含TABLE的create脚本
make_create_sql()
#2.修改掉create脚本中的不合法字符
modefy_create_sql()
#3.将mdb中各表导出到csv文件中
make_insert_csv()
#4.修改csv脚本首行，改成copy形式
modefy_insert_CSV()

insert_into_database()

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!