如何使用 64 位 python 和 odbc 驱动程序从 32 位 .mdb 读取 [英] How to read from a 32 bit .mdb with 64 bit python and odbc driver

问题描述

你好社区，

我正在寻找解决 32 位 .mdb 与我的 64 位环境冲突的解决方案:64 位 Windows 7，带有运行 64 位 python 的 64 位 MS Access 数据库驱动程序.我想将这些 .mdb 中的数据写入 64 位 PostgreSQL 数据库，也可能写入 .csv 文件.但是，我希望这个工具不仅可以在我的机器上运行，还可以在同事的机器上运行，以便理想情况下也能够处理其他环境.

I'm looking for a solution to 32 bit .mdb's conflicting with my 64 bit environment: 64 bit Windows 7, with a 64 bit MS Access Database driver running 64 bit python. I want to write data from these .mdb's into a 64 bit PostgreSQL database and possibly also to .csv files. However, I would like this tool to not only work on my machine but also those of colleagues so it would ideally be able to handle other environments as well.

最初我有一个脚本写入 .csv，使用 32 位版本的 MS Access 数据库驱动程序和 python.但是，我想创建一个 QGIS 插件，我也有 64 位版本，我不想将所有内容更改为 32 位，只是为了能够偶尔读取 32 位 .mdb 文件.

Initially I had a script writing to .csv with 32 bit versions of MS Access Database driver and python. However, I would like to create a QGIS plugin which I also had in 64 bit and I don't feel like changing everything to 32 bit just to be able to read the occasional 32 bit .mdb file.

我对编码完全陌生，但我已经阅读了这个问题，并明白在 32 位数据库和 64 位脚本和驱动程序之间建立直接连接基本上是不可能的.尽管如此，我还是觉得很难相信这个问题没有解决方案.网络应用程序不经常处理这些情况吗?例如，是否可以使用 Django 来创建这种功能?或者使用声称支持 32 位和 64 位的昂贵驱动程序是一种解决方案吗?创建一个独立的工具会更容易吗?或者是否已经存在这样的工具，但我还没有找到?

I am completely new to coding, but I have read up on the issue and understand it's basically not possible to make a direct connection between a 32 bit database and 64 bit script and driver. Still, I find it difficult to believe that there is no solution to this issue. Do web apps not regularly deal with these kind of situations? Would it be possible to create this kind of functionality using Django for example instead? Or would using a costly driver claiming to support both 32- and 64 bit be a solution? Would it be easier to create a standalone tool? Or does such a tool already exist which I just haven't yet been able to find?

总结一下:我希望将数据从 32 位 .mdb 写入 64 位 PostgreSQL 数据库，最好用 QGIS 插件编码.

So to summarize: I'm looking to write data from a 32 bit .mdb to 64 bit PostgreSQL database preferably coded in a QGIS plugin.

我之前工作的初始代码是:

The initial code I had working before is:

import csv, pyodbc
from tkinter import Tk
from tkinter.filedialog import askopenfilename

Tk().withdraw()
filename = askopenfilename()filenameEdit = filename.replace(":/", "://")

print(filename)
print(filenameEdit)

MDB = filename
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
DBQ = filename
PWD = 'pw'

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

SQL = 'SELECT * FROM strips'  # This query would need to be expanded
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()

mytable = input("Save file as: ")

def writeCSV():
    with open((mytable+'.csv'), 'w') as newDB:
        csv_writer = csv.writer(newDB, lineterminator='
')
        for row in rows:
            csv_writer.writerow(row)

writeCSV()

# Another function should be added to write to a PostreSQL database

现在运行 64 位的错误是:

The error this gives now running 64 bit is:

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open a database created with a previous version of your application. (-1019); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open a database created with a previous version of your application. (-1019)")`

任何想法将不胜感激，非常感谢！

Any ideas will be very much appreciated, Many thanks!

推荐答案

Access 数据库文件(.mdb、.accdb)不是特定于架构的；没有32 位 .mdb 文件"或64 位 .accdb 文件"之类的东西.Access 数据库引擎(和驱动程序等)有 32 位和 64 位版本，但它们生成的数据库文件是相同的.

Access database files (.mdb, .accdb) are not architecture-specific; there is no such thing as a "32-bit .mdb file" or a "64-bit .accdb file". There are 32-bit and 64-bit versions of the Access Database Engine (and drivers, etc.) but the database files they produce are identical.

您收到了

无法打开使用以前版本的应用程序创建的数据库.

Cannot open a database created with a previous version of your application.

错误，因为您的 Access 数据库引擎版本不再支持您尝试使用的数据库文件的版本(不是位数").如果您使用的是 Access_2013 或更高版本的 Access 数据库引擎，则您的文件格式必须是 Access_97 或更高版本.如果您使用的是 Access_2010 中的 Access 数据库引擎，那么您尝试使用的文件格式是超旧的(可能是 Access_2.0).

error because your version of the Access Database Engine no longer supports the version (not "bitness") of the database file you are trying to use. If you are using the Access Database Engine for Access_2013 or newer then your file format must be for Access_97 or older. If you are using the Access Database Engine from Access_2010 then the format of the file you are trying to use is super-old (likely Access_2.0).

您似乎已经注意到，我们仍然可以使用 Windows 附带的旧版Jet"数据库引擎(Microsoft Access Driver (*.mdb))处理较旧的数据库文件，但它是一个 32 位驱动程序，没有 64 位版本.

As you seem to have noticed, we can still work with older database files using the legacy "Jet" database engine that ships with Windows (Microsoft Access Driver (*.mdb)) but it is a 32-bit driver and there is no 64-bit version.

这篇关于如何使用 64 位 python 和 odbc 驱动程序从 32 位 .mdb 读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！