mdb格式文件可以通过mdbtools工具将内中包含的每张表导出到csv格式文件。由于access数据库和postgresQL数据库格式上会存在不通性,所以使用python的文件处理,将所得csv文件修改成正确、能识别的格式。
导入脚本说明(此脚本运行于linux):
1.apt-get install mdbtools,安装mdbtools工具
2.将mdb 文件拷贝到linux虚拟机中,修改脚本中mdb文件目录‘dir'
3.修改服务器及数据库配置
4.执行脚本
代码如下:
# -*- 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_tbl_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_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 + '"')
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 语句,最后一行没有逗号
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(',') 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(' ', '_').replace('&', '_'))
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.制作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()

ArraysinPython,especiallyviaNumPy,arecrucialinscientificcomputingfortheirefficiencyandversatility.1)Theyareusedfornumericaloperations,dataanalysis,andmachinelearning.2)NumPy'simplementationinCensuresfasteroperationsthanPythonlists.3)Arraysenablequick

You can manage different Python versions by using pyenv, venv and Anaconda. 1) Use pyenv to manage multiple Python versions: install pyenv, set global and local versions. 2) Use venv to create a virtual environment to isolate project dependencies. 3) Use Anaconda to manage Python versions in your data science project. 4) Keep the system Python for system-level tasks. Through these tools and strategies, you can effectively manage different versions of Python to ensure the smooth running of the project.

NumPyarrayshaveseveraladvantagesoverstandardPythonarrays:1)TheyaremuchfasterduetoC-basedimplementation,2)Theyaremorememory-efficient,especiallywithlargedatasets,and3)Theyofferoptimized,vectorizedfunctionsformathematicalandstatisticaloperations,making

The impact of homogeneity of arrays on performance is dual: 1) Homogeneity allows the compiler to optimize memory access and improve performance; 2) but limits type diversity, which may lead to inefficiency. In short, choosing the right data structure is crucial.

TocraftexecutablePythonscripts,followthesebestpractices:1)Addashebangline(#!/usr/bin/envpython3)tomakethescriptexecutable.2)Setpermissionswithchmod xyour_script.py.3)Organizewithacleardocstringanduseifname=="__main__":formainfunctionality.4

NumPyarraysarebetterfornumericaloperationsandmulti-dimensionaldata,whilethearraymoduleissuitableforbasic,memory-efficientarrays.1)NumPyexcelsinperformanceandfunctionalityforlargedatasetsandcomplexoperations.2)Thearraymoduleismorememory-efficientandfa

NumPyarraysarebetterforheavynumericalcomputing,whilethearraymoduleismoresuitableformemory-constrainedprojectswithsimpledatatypes.1)NumPyarraysofferversatilityandperformanceforlargedatasetsandcomplexoperations.2)Thearraymoduleislightweightandmemory-ef

ctypesallowscreatingandmanipulatingC-stylearraysinPython.1)UsectypestointerfacewithClibrariesforperformance.2)CreateC-stylearraysfornumericalcomputations.3)PassarraystoCfunctionsforefficientoperations.However,becautiousofmemorymanagement,performanceo


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Linux new version
SublimeText3 Linux latest version

Notepad++7.3.1
Easy-to-use and free code editor

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
