如何将.accdb文件导入到Python并使用数据? [英] How do I import an .accdb file into Python and use the data?
问题描述
我想要找出一种方法来创建一个程序,让我根据几个不同的因素找到最佳的数据组合。
我有一个Microsoft Access文件,其中包含生物数据。攻击,防御,健康,需要使用的战斗技能和其他几个信息。
我试图导入这个.accdb(Access 2013)文件,访问存储的数据。
我将尝试创建一个程序,扫描所有的数据并运行所有可能的组合(5个生物的集合),找到最强的生物组合,战斗技能(例如:100个战斗技能将使用生物1,2,3,4和5,其中125个战斗技能将使用生物3,5,6,8和10)。
我需要帮助的第一个主要的是能够导入数据库以便于访问,所以我不必重新创建数据在python中,所以我可以使用相同的程序在未来的新访问数据库。
我已安装 https://代码。 google / p / pypyodbc / ,但似乎无法弄清楚如何载入现有文件。
编辑
我试图使用Gord的答案中的代码,修改为适合我的信息。
# - * - coding:utf-8 - * -
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
rDriver = {Microsoft Access Driver(* .mdb,* .accdb)};+
rDbq = 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()
如果行为None:
break
print(u数字{1}的生物为{1} ({2})。format(
row.get(CreatureID),row.get(Name_EN),row.get(Name_JP)))
cur.close
conn.close()
与打印线这样添加()。
我现在得到这个错误,类似于我在过去得到的。
跟踪(最近一次调用):
文件C:\Users \Ju\Desktop\Test.py,第6行,位于< module>
rDbq = C:\Users\Ju\Desktop\Dark Summoner.accdb;)
文件C:\Python34\lib\site-packages\pypyodbc-
文件C:\\在文件夹中的文件名为:1.3.3-py3.4.egg\pypyodbc.py,第2434行,在__init__
中self.connect(connectString,autocommit,ansi,timeout,unicode_results,readonly) \\ python34 \lib \site-packages\pypyodbc-1.3.3-py3.4.egg \pypyodbc.py,第2483行,在连接
check_success(self,ret)
文件C:\ Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py,第988行,位于check_success
ctrl_err(SQL_HANDLE_DBC,ODBC_obj.dbc_h ,ret,ODBC_obj.ansi)
文件C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py,第964行ctrl_err
raise错误(状态,错误文本)
pypyodbc.Error:('IM002','[IM002] [Microsoft] [ODBC驱动程序管理器]未找到数据源名称,没有指定默认驱动程序')
我通过pypyodbc.py文件查看错误代码中提到的行,但无法计算出来。我试图删除r从r开头Driver = {Microsoft Access驱动程序(* .mdb,* .accdb)};并尝试r和司机之间的空格,因为我不知道是什么,但有一个不同的错误。
编辑
我建议的检查我的文件,我相信我运行64位。我检查了32位和64位版本。我有Microsoft Access驱动程序(* .mdb,* .accdb)在64位,但不是在32位。我使用的是2013年版Microsoft Visual Studios。
编辑
!
我最后的工作代码,以防它将来帮助任何人。
# - * - coding:utf-8 - * -
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
rDriver = {Microsoft Access Driver(* .mdb,* .accdb)};+
rDbq = 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()
如果row为None:
break
print(uID:{1} {2} Atk:{3} Def:{4} HP :{5} BP:{6} Species:{7} {8}。format(
row.get(Number),row.get(ID),row.get ),row.get(Atk),
row.get(Def),row.get(HP),row.get(BP),row.get ,row.get(Special)))
cur.close()
conn.close()
$ b b
假设你有一个名为Database1.accdb的数据库文件,名为Creatures的表包含以下数据:
CreatureID Name_EN Name_JP
---------- - ------ -------
1 Godzillaゴラ
2 Mothraモスラ
在Windows机器上通过pypyodbc读取数据的极简Python脚本看起来像这样:
# - * - coding:utf-8 - * -
import pypyodbc
pypyodbc.lowercase = False
conn = pypyodbc.connect(
r = {Microsoft Access Driver(* .mdb,* .accdb)};+
rDbq = C:\Users\Public\Database1.accdb;)
cur = conn.cursor ()
cur.execute(SELECT CreatureID,Name_EN,Name_JP FROM Creatures);
while True:
row = cur.fetchone()
如果行为None:
break
print(uID为{0}的生物为{1} ({2})。format(
row.get(CreatureID),row.get(Name_EN),row.get(Name_JP)))
cur.close
conn.close()
结果输出为
ID为1的生物是Godzilla(ゴジラ)
ID为2的生物是Mothra(モスラ)
编辑
使用您需要在您的计算机上安装Access数据库引擎(又名ACE)的Microsoft Access驱动程序(* .mdb,* .accdb)驱动程序。您可以通过运行以下脚本来检查是否有32位或64位Python:
import struct
print(running as {0} -bit.format(struct.calcsize(P)* 8))
使用这些信息,您可以从这里下载并安装匹配的(32位或64位)版本的Access数据库引擎
Microsoft Access Database Engine 2010 Redistributable
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.
I am trying to import this .accdb (Access 2013) file and be able to access the stored data.
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)
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.
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.
Edit
I tried to use the code from Gord's answer, modified to fit my info.
# -*- 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.
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.
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()
解决方案Say you have a database file named "Database1.accdb" with a table named "Creatures" containing the following data:
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:
# -*- 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:
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
Microsoft Access Database Engine 2010 Redistributable
这篇关于如何将.accdb文件导入到Python并使用数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!