如何使用python处理.mdb访问文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3620539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to deal with .mdb access files with python
提问by Richard
Can someone point me in the right direction on how to open a .mdb file in python? I normally like including some code to start off a discussion, but I don't know where to start. I work with mysql a fair bit with python. I was wondering if there is a way to work with .mdb files in a similar way?
有人能指出我如何在 python 中打开 .mdb 文件的正确方向吗？我通常喜欢包含一些代码来开始讨论，但我不知道从哪里开始。我使用 mysql 和 python 一起工作。我想知道是否有办法以类似的方式处理 .mdb 文件？
采纳答案by mechanical_meat
Below is some code I wrote for another SO question.
It requires the 3rd-party pyodbc module.
下面是我为另一个 SO 问题编写的一些代码。
它需要第 3 方pyodbc 模块。
This very simple example will connect to a table and export the results to a file.
Feel free to expand upon your question with any more specific needs you might have.
这个非常简单的示例将连接到一个表并将结果导出到一个文件。
如果您有任何更具体的需求，请随意扩展您的问题。
import csv, pyodbc
# set up some constants
MDB = 'c:/path/to/my.mdb'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'pw'
# connect to db
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()
# run a query and get the results
SQL = 'SELECT * FROM mytable;' # your query goes here
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()
# you could change the mode from 'w' to 'a' (append) for any subsequent queries
with open('mytable.csv', 'wb') as fou:
csv_writer = csv.writer(fou) # default field-delimiter is ","
csv_writer.writerows(rows)
回答by pyfunc
This looks similar to a previous question:
这看起来类似于上一个问题：
- What do I need to read Microsoft Access databases using Python?
- http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/
- 使用 Python 读取 Microsoft Access 数据库需要什么？
- http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/
Answer there should be useful.
回答那里应该有用。
回答by mmicoski
In addition to bernie's response, I would add that it is possible to recover the schema of the database. The code below lists the tables (b[2] contains the name of the table).
除了伯尼的回应之外，我还要补充一点，可以恢复数据库的架构。下面的代码列出了表（b[2] 包含表的名称）。
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()
tables = list(cur.tables())
print 'tables'
for b in tables:
print b
The code below lists all the columns from all the tables:
下面的代码列出了所有表中的所有列：
colDesc = list(cur.columns())
回答by Gord Thompson
For a solution that works on any platform that can run Java, consider using Jythonor JayDeBeApialong with the UCanAccessJDBC driver. For details, see the related question
对于适用于任何可以运行 Java 的平台的解决方案，请考虑将Jython或JayDeBeApi与UCanAccessJDBC 驱动程序一起使用。有关详细信息，请参阅相关问题
Read an Access database in Python on non-Windows platform (Linux or Mac)
回答by jnns
There's the meza library by Reuben Cummingswhich can read Microsoft Access databases through mdbtools.
还有的由鲁本卡明斯梅萨库可通过读取Microsoft Access数据库mdbtools。
Installation
安装
# The mdbtools package for Python deals with MongoDB, not MS Access.
# So install the package through `apt` if you're on Debian/Ubuntu
$ sudo apt install mdbtools
$ pip install meza
Usage
用法
>>> from meza import io
>>> records = io.read('database.mdb') # only file path, no file objects
>>> print(next(records))
Table1
Table2
…
回答by mussabaheen
This code will convert all the tables to CSV.
此代码会将所有表转换为 CSV。
Happy Coding
快乐编码
for tbl in mdb.list_tables("file_name.MDB"):
df = mdb.read_table("file_name.MDB", tbl)
df.to_csv(tbl+'.csv')
