如何将 .accdb 文件导入 Python 并使用数据？

How do I import an .accdb file into Python and use the data?

提问by Justin

I am trying to figure out a way to create a program that allows me to find the best combination of data based on several different factors.

我试图找出一种方法来创建一个程序，该程序允许我根据几个不同的因素找到最佳的数据组合。

I have a Microsoft Access file with creature data in it. Attack, Defense, Health, Required Battle skill to use and several other bits of info.

我有一个包含生物数据的 Microsoft Access 文件。攻击、防御、健康、需要使用的战斗技能以及其他一些信息。

I am trying to import this .accdb (Access 2013) file and be able to access the stored data.

我正在尝试导入此 .accdb (Access 2013) 文件并能够访问存储的数据。

I am going to try to make a program that scans all the data and runs all possible combinations (sets of 5 creatures) to find the strongest combination of creatures for different required battle skills (ex: 100 battle skill would use creature 1, 2, 3, 4 and 5 where 125 battle skill would use creature 3, 5, 6, 8, and 10)

我将尝试制作一个程序来扫描所有数据并运行所有可能的组合（一组 5 个生物）以找到针对不同所需战斗技能的最强生物组合（例如：100 战斗技能将使用生物 1、2、 3、4 和 5，其中 125 战斗技能将使用生物 3、5、6、8 和 10）

The main thing I need help with first is being able to import the data base for easy access so I do not have to recreate the data in python and so I can use the same program for new access databases in the future.

我首先需要帮助的主要事情是能够导入数据库以便于访问，这样我就不必在 python 中重新创建数据，所以我将来可以使用相同的程序来访问新的访问数据库。

I have installed https://code.google.com/p/pypyodbc/but can't seem to figure out how to get it to load an existing file.

我已经安装了https://code.google.com/p/pypyodbc/但似乎无法弄清楚如何让它加载现有文件。

Edit

编辑

I tried to use the code from Gord's answer, modified to fit my info.

我尝试使用 Gord's answer 中的代码，修改以适合我的信息。

# -*- coding: utf-8 -*-
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\Users\Ju\Desktop\Dark Summoner.accdb;")
cur = conn.cursor()
cur.execute("SELECT Number, Name, Atk, Def, HP, BP, Species, Special FROM Impulse AA+");
while True:
    row = cur.fetchone()
    if row is None:
        break
    print (u"Creature with Number {1} is {1} ({2})".format(
        row.get("CreatureID"), row.get("Name_EN"), row.get("Name_JP")))
cur.close()
conn.close()

Was getting an error with the print line so added () around it.

打印行出错，因此在它周围添加了 ()。

I am now getting this error, similar to what I was getting in the past.

我现在收到此错误，类似于我过去遇到的错误。

Traceback (most recent call last):
  File "C:\Users\Ju\Desktop\Test.py", line 6, in <module>
    r"Dbq=C:\Users\Ju\Desktop\Dark Summoner.accdb;")
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
pypyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified')

I looked through the pypyodbc.py file at the lines mentioned in the error code, but could not figure it out. I tried to remove the "r" from the beginning of r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" and tried a space between r and "Driver because I did not know what it was for, But got a different error.

我在错误代码中提到的行中查看了 pypyodbc.py 文件，但无法弄清楚。我试图从 r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" 的开头删除“r” 并在 r 和“驱动程序之间尝试了一个空格，因为我不知道它是做什么用的，但得到了一个不同的错误。

Edit

编辑

I checked my files as suggested. I believe I am running 64bit. I checked both the 32 bit and 64 bit versions. I have Microsoft Access Driver (*.mdb, *.accdb) in the 64 bit but not in the 32 bit. I am using the 2013 version of Microsoft Visual Studios.

我按照建议检查了我的文件。我相信我正在运行 64 位。我检查了 32 位和 64 位版本。我有 64 位的 Microsoft Access 驱动程序（*.mdb、*.accdb），但没有 32 位。我使用的是 2013 版的 Microsoft Visual Studio。

Edit

编辑

Working now!

现在工作！

My final working code in case it helps anyone in the future.

我的最终工作代码，以防将来对任何人有所帮助。

# -*- coding: utf-8 -*-
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\Users\Ju\Desktop\Dark Summoner.accdb;")
cur = conn.cursor()
cur.execute("SELECT Number, ID, Name, Atk, Def, HP, BP, Species, Special FROM Impulse_AA");
while True:
    row = cur.fetchone()
    if row is None:
        break
    print (u"ID: {1} {2} Atk:{3} Def:{4} HP:{5} BP:{6} Species: {7} {8}".format(
        row.get("Number"), row.get("ID"), row.get("Name"), row.get("Atk"),
        row.get("Def"), row.get("HP"), row.get("BP"), row.get("Species"), row.get("Special") ))
cur.close()
conn.close()

采纳答案by Gord Thompson

Say you have a database file named "Database1.accdb" with a table named "Creatures" containing the following data:

假设您有一个名为“Database1.accdb”的数据库文件，其中包含一个名为“Creatures”的表，其中包含以下数据：

CreatureID  Name_EN   Name_JP
----------  --------  -------
         1  Godzilla  ゴジラ
         2  Mothra    モスラ

A minimalist Python script to read the data via pypyodbc on a Windows machine would look something like this:

在 Windows 机器上通过 pypyodbc 读取数据的简约 Python 脚本如下所示：

# -*- coding: utf-8 -*-
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\Users\Public\Database1.accdb;")
cur = conn.cursor()
cur.execute("SELECT CreatureID, Name_EN, Name_JP FROM Creatures");
while True:
    row = cur.fetchone()
    if row is None:
        break
    print(u"Creature with ID {0} is {1} ({2})".format(
        row.get("CreatureID"), row.get("Name_EN"), row.get("Name_JP")))
cur.close()
conn.close()

The resulting output is

结果输出是

Creature with ID 1 is Godzilla (ゴジラ)
Creature with ID 2 is Mothra (モスラ)

Edit

编辑

Note that to use the "Microsoft Access Driver (*.mdb, *.accdb)" driver you need to have the Access Database Engine (a.k.a "ACE") installed on your machine. You can check whether you have 32-bit or 64-bit Python by running the following script:

请注意，要使用“Microsoft Access Driver (*.mdb, *.accdb)”驱动程序，您需要在您的计算机上安装 Access Database Engine（又名“ACE”）。您可以通过运行以下脚本来检查您使用的是 32 位还是 64 位 Python：

import struct
print("running as {0}-bit".format(struct.calcsize("P") * 8))

Armed with that information you can download and install the matching (32-bit or 64-bit) version of the Access Database Engine from here

有了这些信息，您可以从这里下载并安装匹配（32 位或 64 位）版本的 Access 数据库引擎

Microsoft Access Database Engine 2010 Redistributable

Microsoft Access 数据库引擎 2010 可再分发