在非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屋！