Importing MDB database files into the PostgreSQL database sample using Python

The MDB format file can be exported to a CSV format file with each of the tables contained in the Mdbtools tool. Because the Access database and the PostgreSQL database format will be non-persistent, so using Python file processing, the resulting CSV file is modified to the correct, recognizable format.

Import Script Description (This script runs on Linux):

1.apt-get install Mdbtools, installing the Mdbtools tool

2. Copy the MDB file to the Linux virtual machine and modify the MDB file directory ' dir ' in the script

3. Modify server and database configuration

4. Execute the Script

The code is as follows:

#-*-Encoding:utf-8-*-
Import OS
Import re
Import PSYCOPG2
Import CSV

#mdb文件目录
dir = R '/home/kotaimen/mdb_file/'
Mdb_tbl_dic = {}

Def make_create_sql ():
If Os.path.isfile (dir + ' Create.sql '):
Os.remove (dir + ' create.sql ')

for Mdb_file in Os.walk (dir):
If Len (mdb_file[2]) >0:
for File_p in mdb_file[2]:
if file_p[-3:] = = ' MDB ':
print file_p
cmd = ' mdb-schema%s >>/home/kotaimen/mdb_file/create.sql '
cmd = cmd% (dir + file_p
Print cmd
os.system (cmd)
cmd = ' mdb-tables-1%s '% (dir + file_p)
val = os.popen (cmd). Read ()
mdb_t Bl_dic[file_p] = val.split (' \ n ')
Print mdb_tbl_dic

Def modefy_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 ')
for eachline in fobj:
#判断表名中是否含有空格
If Eachline.find (' table ') >= 0:
If Eachline.find (';') >= 0:
Start_loc = eachline.find (' table ') + 6
End_lo c = 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]
E Achline = Eachline.replace (Tbl_name, ' "' + Tbl_name + '")

If Eachline.find (' DROP TABLE ') >= 0:
Eachline = eachline.replace (' drop table ', ' drop table IF EXISTS ')
If Eachline.find (' Table ') >= 0:
Eachline = eachline.replace (' table ', ' "table ')
#create statement with no comma on the last line
If Eachline.find (' Text ') >= 0 and Eachline.find (', ') >0:
loc = Eachline.find (' Text ')
Eachline = Eachline[0:loc] + ' text,\n '
Elif eachline.find (' Text ') >= 0 and Eachline.find (', ') < 0:
loc = Eachline.find (' Text ')
Eachline = Eachline[0:loc] + ' Text \ n '
Fobj_des.writelines (Eachline)
Fobj.close ()
Fobj_des.close ()
Os.remove (Sql_file_name)
Os.rename (Sql_file_name_des, Sql_file_name)

Def make_insert_csv ():
For file_p in Mdb_tbl_dic.keys ():
For TBL in mdb_tbl_dic[file_p]:
If Len (TBL) >0:
cmd = ' mdb-export%s%s >%s.csv '% (dir + file_p, ' "' + tbl + '" ', dir + ' "' + tbl + '" ') # Tbl.replace (', ' _ '). Replac E (' & ', ' _ '))
Os.system (CMD)

Def modefy_insert_csv ():
For Sql_file in Os.walk (dir):
If Len (sql_file[2]) >0:
For file_p in sql_file[2]:
If file_p[-3:] = = ' csv ':
Sql_file_name = dir + file_p
Sql_file_name_des = sql_file_name + ' _new '
Fobj = open (Sql_file_name, ' R ')
Fobj_des = open (Sql_file_name_des, ' W ')
For (Num, Val) in Enumerate (fobj):
Eachline = Val
If num = = 0:
col_list = Eachline.split (', ')
Stat = ' COPY ' + ' ' + ' + (file_p[0:-4]) + ' ' + ' (' #+ ('%s, ' *len (line) ') [: -1]+ ') '
For Col in Col_list:
If col = = ' Table ':
Col = ' ' + ' Table ' + ' "'
If Col.find (' \ n ') >= 0:
Col.replace (' \ n ', ')
Stat = stat + col + ', '
Stat = stat[:-2] + ') ' + ' from STDIN with CSV; \ 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)

Def insert_into_database ():

cmd = ' psql-h 172.26.11.205-d ap_mapmyindia_full_sample-u postgres-f%s 2>>log.txt '% (dir + ' create.sql ')
Os.system (CMD)

For Sql_file in Os.walk (dir):
If Len (sql_file[2]) >0:
For file_p in sql_file[2]:
Print File_p
If file_p[-3:] = = ' csv ':
cmd = ' psql-h 172.26.11.205-d ap_mapmyindia_full_sample-u postgres-f%s 2>>log.txt '% (dir + ' "' + file_p + '")
Os.system (CMD)

if __name__ = = "__main__":
#1. Create script for the table contained in the MDB file
Make_create_sql ()
#2. Modify the illegal characters in the Create script
Modefy_create_sql ()
#3. Exporting the tables in the MDB to a CSV file
Make_insert_csv ()
#4. Change the first line of the CSV script to copy form
Modefy_insert_csv ()

Insert_into_database ()

