使用python将mdb数据库文件导入postgresql数据库示例

Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]

          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span>

include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice: file_put_contents() [function.file-put-contents]: Write of 2422 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140

Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice: file_put_contents() [function.file-put-contents]: Write of 2446 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
">

Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

本文是一个python实现的可以将mdb数据库文件导入到postgresql数据库的示例代码,非常实现的一个功能,感兴趣的同学参考下. 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 =

本文是一个python实现的可以将mdb 数据库文件导入到postgresql数据库的示例代码,非常实现的一个功能,感兴趣的同学参考下.

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(',') < 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(' ', '_').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()

标签: 数据库使用 python mdb 文件导入 postgresql 示例

声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: 网上订火车票昨调整起售时间

上一篇: Nexus 10仍是最好的原生Android平板吗？

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

新闻媒体

美女明星/人物

考试/招生

主持人

企业服务

体育院校

建筑/装饰

天气预报

人口学

中国传统艺术

出版

保健/养生

使用python将mdb数据库文件导入postgresql数据库示例

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

使用python将mdb数据库文件导入postgresql数据库示例

猜您喜欢

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索