如何从具有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.
最初，我有一个脚本使用32位版本的MS Access数据库驱动程序和python编写.csv脚本.但是，我想创建一个我也有64位的QGIS插件，我不想将所有内容都更改为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位脚本和驱动程序之间建立直接连接.但是，我仍然很难相信没有解决此问题的方法. Web应用程序是否不能定期处理此类情况?例如可以使用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='\n')
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文件"之类的东西.访问数据库 Engine (和驱动程序等)有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数据库引擎用于Access_2013或更高版本，则文件格式必须为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屋！
