在非Windows平台(Linux或Mac)上使用Python使用Access数据库 [英] Working with an Access database in Python on non-Windows platform (Linux or Mac)
问题描述
我想访问Microsoft Access数据库中的数据.我有一些.accdb和.mdb文件,想用Python读取它们.
I want to access the data in a Microsoft Access database. I have some .accdb and .mdb files and want to read them in Python.
根据我的研究,pyodbc只能在Windows平台上使用,但是我在Mac OS X上工作. 我是Python的新手.
From my research, pyodbc can only be used on Windows platform, but I am working on Mac OS X. I am new to Python.
另一个选择是,如果我可以将数据从数据库导出到csv,然后在python中使用.
The other option is if I could export the data from the database to a csv and then use in python.
我们将不胜感激任何帮助或开始.
Any help or starting would be highly appreciated.
推荐答案
根据我的研究,pyodbc只能在Windows平台上使用"
不正确. pyodbc主页表示
在Windows和macOS上,大多数Python版本都提供了预编译的二进制轮子.在其他操作系统上[pip install pyodbc]将从源代码构建.
Precompiled binary wheels are provided for most Python versions on Windows and macOS. On other operating systems [pip install pyodbc] will build from source.
但是,可以肯定的是,使用ODBC操纵 Access数据库主要是在Windows上完成的.人们经常提到"MDB工具"以及"unixODBC"是在非Windows平台上使用Access数据库的一种方法,但是根据我的有限经验,我发现它确实不能很好地工作(当它可以工作时)完全没有.)
However, it is certainly true that using ODBC to manipulate an Access database is mainly done on Windows. "MDB Tools", along with "unixODBC", is often mentioned as a way to work with Access databases on non-Windows platforms, but in my limited experience I have found that it really just doesn't work very well (when it works at all).
当然,您始终可以为非Windows平台购买第三方MS Access ODBC驱动程序,但是如果您想要免费的开源解决方案,则可以使用 JayDeBeApi 和
Of course, you can always purchase a third-party MS Access ODBC driver for your non-Windows platform, but if you want a free open-source solution you can use the UCanAccess JDBC driver. There are two ways to accomplish that: JayDeBeApi, and Jython.
在两种情况下,您都需要下载UCanAccess的最新版本(可在此处)并将"bin.zip"文件解压缩到一个方便的位置,请确保保留文件夹结构:
In both cases you will need to download the latest version of UCanAccess (available for download here) and unpack the "bin.zip" file to a convenient location, making sure to preserve the folder structure:
(在以下示例中,我将其解压缩到~/Downloads/JDBC/UCanAccess
.)
(In the following examples I unpacked it to ~/Downloads/JDBC/UCanAccess
.)
这是首选选项,因为它可以与现有的Python设置一起使用.您可以使用pip
安装JayDeBeApi.当前(2019年7月)JPype1的更高版本存在问题,因此您应该安装特定版本
This is the preferred option since it should work with your existing Python setup. You can install JayDeBeApi with pip
. Currently (July 2019) there are issues with later versions of JPype1 so you should install the specific versions
pip install JPype1==0.6.3 JayDeBeApi==1.1.1
如果尚未安装JRE(Java运行时环境),那么您也将需要它. (我在Ubuntu上使用sudo apt install default-jre
.)
If you don't already have a JRE (Java Runtime Environment) installed then you'll need that, too. (I used sudo apt install default-jre
on Ubuntu.)
一旦所需的组件就位,您应该可以使用如下代码:
Once the required components are in place you should be able to use code like this:
import jaydebeapi
db_path = "/home/gord/test.accdb"
ucanaccess_jars = [
"/home/gord/Downloads/JDBC/UCanAccess/ucanaccess-4.0.4.jar",
"/home/gord/Downloads/JDBC/UCanAccess/lib/commons-lang-2.6.jar",
"/home/gord/Downloads/JDBC/UCanAccess/lib/commons-logging-1.1.3.jar",
"/home/gord/Downloads/JDBC/UCanAccess/lib/hsqldb.jar",
"/home/gord/Downloads/JDBC/UCanAccess/lib/jackcess-2.1.11.jar",
]
classpath = ":".join(ucanaccess_jars)
cnxn = jaydebeapi.connect(
"net.ucanaccess.jdbc.UcanaccessDriver",
f"jdbc:ucanaccess://{db_path};newDatabaseVersion=V2010",
["", ""],
classpath
)
crsr = cnxn.cursor()
try:
crsr.execute("DROP TABLE table1")
cnxn.commit()
except jaydebeapi.DatabaseError as de:
if "user lacks privilege or object not found: TABLE1" in str(de):
pass
else:
raise
crsr.execute("CREATE TABLE table1 (id COUNTER PRIMARY KEY, fname TEXT(50))")
cnxn.commit()
crsr.execute("INSERT INTO table1 (fname) VALUES ('Gord')")
cnxn.commit()
crsr.execute("SELECT * FROM table1")
for row in crsr.fetchall():
print(row)
crsr.close()
cnxn.close()
(请注意,Jython是Python的单独实现,它仅支持Python 2.7,并且显然不再处于主动开发状态.)
(Note that Jython is a separate implementation of Python, it only supports Python 2.7, and is apparently no longer under active development.)
重要提示:以下说明适用于UCanAccess版本 3.0.5 或更高版本.
Important: The following instructions are for UCanAccess version 3.0.5 or later.
之后...
- (在Ubuntu上通过
sudo apt-get install jython
安装Jython)和 - 如上所述下载UCanAccess并解压缩
- installing Jython (via
sudo apt-get install jython
on Ubuntu) and - downloading UCanAccess and unpacking it as described above
我创建了以下名为"dbTest.py"的Jython脚本
I created the following Jython script named "dbTest.py"
from com.ziclix.python.sql import zxJDBC
jdbc_url = "jdbc:ucanaccess:///home/gord/Documents/test.accdb"
username = ""
password = ""
driver_class = "net.ucanaccess.jdbc.UcanloadDriver"
cnxn = zxJDBC.connect(jdbc_url, username, password, driver_class)
crsr = cnxn.cursor()
crsr.execute("SELECT AgentName FROM Agents")
for row in crsr.fetchall():
print row[0]
crsr.close()
cnxn.close()
并使用以下shell脚本运行它
and ran it with the following shell script
#!/bin/bash
export CLASSPATH=.:/home/gord/Downloads/JDBC/UCanAccess/loader/ucanload.jar
jython dbTest.py
这篇关于在非Windows平台(Linux或Mac)上使用Python使用Access数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!