在非 Windows 平台(Linux 或 Mac)上使用 Python 使用 Access 数据库 [英] Working with an Access database in Python on non-Windows platform (Linux or Mac)

问题描述

我想访问 Microsoft Access 数据库中的数据.我有一些 .accdb 和 .mdb 文件，想用 Python 读取它们.

根据我的研究，pyodbc 只能在 Windows 平台上使用，但我在 Mac OS X 上工作.我是 Python 新手.

另一个选项是我是否可以将数据从数据库导出到 csv，然后在 python 中使用.

非常感谢任何帮助或开始.

解决方案

根据我的研究，pyodbc只能在Windows平台上使用"

不是真的.

(在以下示例中，我将其解压到 ~/Downloads/JDBC/UCanAccess.)

 

选项 1:JayDeBeApi

这是首选选项，因为它应该适用于您现有的 Python 设置.您可以使用 pip 安装 JayDeBeApi.

如果您还没有安装 JRE(Java 运行时环境)，那么您也需要它.(我在 Ubuntu 上使用了 sudo apt install default-jre.)

一旦所需的组件就位，您应该能够使用如下代码:

import jaydebeapidb_path = "/home/gord/test.accdb"ucanaccess_jars = ["/home/gord/Downloads/JDBC/UCanAccess/ucanaccess-5.0.0.jar","/home/gord/Downloads/JDBC/UCanAccess/lib/commons-lang3-3.8.1.jar","/home/gord/Downloads/JDBC/UCanAccess/lib/commons-logging-1.2.jar","/home/gord/Downloads/JDBC/UCanAccess/lib/hsqldb-2.5.0.jar","/home/gord/Downloads/JDBC/UCanAccess/lib/jackcess-3.0.1.jar",]classpath = ":".join(ucanaccess_jars)cnxn = jaydebeapi.connect("net.ucanaccess.jdbc.UcanaccessDriver",f"jdbc:ucanaccess://{db_path};newDatabaseVersion=V2010",["", ""],类路径)crsr = cnxn.cursor()尝试:crsr.execute("DROP TABLE table1")cnxn.commit()除了 jaydebeapi.DatabaseError 作为 de:如果在 str(de) 中出现用户缺少权限或找不到对象:TABLE1":经过别的:增加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")对于 crsr.fetchall() 中的行:打印(行)crsr.close()cnxn.close()

 

选项 2:Jython

(请注意，Jython 是 Python 的单独实现，它仅支持 Python 2.7，并且显然不再处于积极开发阶段.)

重要提示:以下说明适用于 UCanAccess 3.0.5 或更高版本.

之后...

• 安装 Jython(在 Ubuntu 上通过 sudo apt-get install jython)和
• 如上所述下载 UCanAccess 并解压它

我创建了以下名为dbTest.py"的 Jython 脚本

from com.ziclix.python.sql import zxJDBCjdbc_url = "jdbc:ucanaccess:///home/gord/Documents/test.accdb"用户名 = ""密码 = ""driver_class = "net.ucanaccess.jdbc.UcanloadDriver"cnxn = zxJDBC.connect(jdbc_url, 用户名, 密码, driver_class)crsr = cnxn.cursor()crsr.execute("SELECT AgentName FROM Agents")对于 crsr.fetchall() 中的行:打印行[0]crsr.close()cnxn.close()

并使用以下 shell 脚本运行它

#!/bin/bashexport CLASSPATH=.:/home/gord/Downloads/JDBC/UCanAccess/loader/ucanload.jarjython dbTest.py

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.

From my research, pyodbc can only be used on Windows platform, but I am working on Mac OS X. I am new to 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.

解决方案

"From my research, pyodbc can only be used on Windows platform"

Not true. The main pyodbc page says

Precompiled binary wheels are provided for most Python versions on Windows and macOS. On other operating systems [pip install pyodbc] will build from source.

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

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.

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:

(In the following examples I unpacked it to ~/Downloads/JDBC/UCanAccess.)

 

Option 1: JayDeBeApi

This is the preferred option since it should work with your existing Python setup. You can install JayDeBeApi with pip.

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-5.0.0.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-lang3-3.8.1.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-logging-1.2.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/hsqldb-2.5.0.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/jackcess-3.0.1.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()

 

Option 2: Jython

(Note that Jython is a separate implementation of Python, it only supports Python 2.7, and is apparently no longer under active development.)

Important: The following instructions are for UCanAccess version 3.0.5 or later.

After ...

• installing Jython (via sudo apt-get install jython on Ubuntu) and
• downloading UCanAccess and unpacking it as described above

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

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