blocks|key|1154193|text|下面是我为another+SO+question编写的一些代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1154194|它需要第三方pyodbc+module。|1154195|这个非常简单的示例将连接到一个表，并将结果导出到一个文件。|1154196|如果您有任何更具体的需求，请随时扩展您的问题。|1154197|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',+'w')+as+fou:
++++csv_writer+=+csv.writer(fou)+#+default+field-delimiter+is+","
++++csv_writer.writerows(rows)|code-block|syntax|javascript|1154198|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3064830/query-crashes-ms-access|1|https://github.com/mkleehammer/pyodbc^0|5|J|0|0|6|D|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|F|3|G|5|6|7|16|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|17|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|18|8|@]|9|@]|C|$M|N]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]]]

Below is some code I wrote for <a href="https://stackoverflow.com/questions/3064830/query-crashes-ms-access">another SO question</a>. 
It requires the 3rd-party <a href="https://github.com/mkleehammer/pyodbc" rel="noreferrer">pyodbc module</a>.

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.

<pre><code>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', 'w') as fou:
 csv_writer = csv.writer(fou) # default field-delimiter is ","
 csv_writer.writerows(rows)
</code></pre>

blocks|key|1154241|text|除了bernie的回应之外，我还想补充说，有可能恢复数据库的模式。下面的代码列出了表(b2包含表的名称)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1154242|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|code-block|syntax|javascript|1154243|下面的代码列出了所有表中的所有列：|1154244|colDesc+=+list(cur.columns())|1154245|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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).

<pre><code>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
</code></pre>

The code below lists all the columns from all the tables:

<pre><code>colDesc = list(cur.columns())
</code></pre>

blocks|key|939984|text|对于可以在任何运行Java的平台上运行的解决方案，可以考虑使用Jython或JayDeBeApi以及UCanAccess+JDBC驱动程序。有关详情，请参阅相关问题|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|939985|Read+an+Access+database+in+Python+on+non-Windows+platform+(Linux+or+Mac)|939986|entityMap|0|LINK|mutability|MUTABLE|url|http://www.jython.org/|1|https://pypi.python.org/pypi/JayDeBeApi|2|http://ucanaccess.sourceforge.net/site.html|3|https://stackoverflow.com/q/25596737/2144390^0|V|6|0|12|A|1|1E|A|2|0|0|20|3|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]|$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|F|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]|R|$5|I|J|K|C|$L|S]]]]

For a solution that works on any platform that can run Java, consider using <a href="http://www.jython.org/" rel="nofollow noreferrer">Jython</a> or <a href="https://pypi.python.org/pypi/JayDeBeApi" rel="nofollow noreferrer">JayDeBeApi</a> along with the <a href="http://ucanaccess.sourceforge.net/site.html" rel="nofollow noreferrer">UCanAccess</a> JDBC driver. For details, see the related question

<a href="https://stackoverflow.com/q/25596737/2144390">Read an Access database in Python on non-Windows platform (Linux or Mac)</a>

blocks|key|940033|text|有一个可以通过mdbtools读取Microsoft+Access数据库的meza+library+by+Reuben+Cummings。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|940034|安装|940035|#+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|code-block|syntax|javascript|940036|用法|940037|>>>+from+meza+import+io

>>>+records+=+io.read('database.mdb')+#+only+file+path,+no+file+objects
>>>+print(next(records))

Table1
Table2
…|940038|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/brianb/mdbtools|1|https://github.com/reubano/meza^0|7|8|0|11|V|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]|$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|16|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|18|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]]]

There's the <a href="https://github.com/reubano/meza" rel="noreferrer">meza library by Reuben Cummings</a> which can read Microsoft Access databases through <a href="https://github.com/brianb/mdbtools" rel="noreferrer">mdbtools</a>.

<h1>Installation</h1>

<pre><code># 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
</code></pre>

<h1>Usage</h1>

<pre><code>&gt;&gt;&gt; from meza import io

&gt;&gt;&gt; records = io.read('database.mdb') # only file path, no file objects
&gt;&gt;&gt; print(next(records))

Table1
Table2
…
</code></pre>

blocks|key|940087|text|此代码会将所有表转换为CSV。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|940088|快乐编码|940089|for+tbl+in+mdb.list_tables("file_name.MDB"):
++++df+=+mdb.read_table("file_name.MDB",+tbl)
++++df.to_csv(tbl%2B'.csv')|code-block|syntax|javascript|940090|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This code will convert all the tables to CSV.

Happy Coding

<pre><code>for tbl in mdb.list_tables("file_name.MDB"):
 df = mdb.read_table("file_name.MDB", tbl)
 df.to_csv(tbl+'.csv')
</code></pre>

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?

how to deal with .mdb access files with python

数据库

MongoDB

Python

Ubuntu

Debian

Java

谁能告诉我如何在python中打开.mdb文件的正确方向？我通常喜欢在讨论开始时加入一些代码，但我不知道从哪里开始。我经常使用mysql和python。我想知道是否有一种方法可以以类似的方式处理.mdb文件？

问如何用python处理.mdb access文件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何用python处理.mdb access文件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何用python处理.mdb access文件
EN