尝试使用 PYODBC 将 Access 数据库表读入 Pandas 时出错 [英] errors trying to read Access Database Tables into Pandas with PYODBC
问题描述
我想执行一项简单的任务，将 MS Access 数据库中的表数据以数据框的形式导入 Pandas.我最近工作得很好，现在我不知道为什么它不再工作了.我记得最初对连接进行故障排除时，我需要围绕安装具有正确位数的新 Microsoft 数据库驱动程序来做一些工作，因此我重新访问了该驱动程序并重新安装了驱动程序.以下是我用于设置的内容.
I would like to be performing a simple task of bringing table data from a MS Access database into Pandas in the form of a dataframe. I had this working great recently and now I can not figure out why it is no longer working. I remember when initially troubleshooting the connection there was work that I needed to do around installing a new microsoft database driver with the correct bitness so I have revisited that and gone through a reinstallation of the driver. Below is what I am using for a setup.
在笔记本电脑上的安装记录:
Record of install on Laptop:
- 操作系统:Windows 7 Professional 64 位(2017 年 9 月 6 日验证)
- Access 版本:Access 2016 32 位(2017 年 9 月 6 日验证)
- Python 版本:使用 >Python -V 找到的 Python 3.6.1(64 位)(已于 2017 年 9 月 11 日验证)
- 所需的 AccessDatabaseEngine 将基于上述 Python 位数
- 使用 >AccessDatabaseEngine_X64.exe/passive(已于 2017 年 9 月 11 日验证)从 2010 年发布的随 AccessDatabaseEngine_X64.exe 安装的 Windows 数据库引擎驱动程序
我正在运行以下简单的测试代码来尝试连接到测试数据库.
I am running the following simple test code to try out the connection to a test database.
import pyodbc
import pandas as pd
[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
返回:
['Microsoft Access Driver (*.mdb, *.accdb)']
设置连接字符串.
dbpath = r'Z:\1Users\myfiles\software\JupyterNotebookFiles\testDB.accdb'
conn_str = (r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};''DBQ=%s;' %(dbpath))
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
验证我已连接到数据库...
Verifying that the I am connected to the db...
for table_info in crsr.tables(tableType='TABLE'):
print(table_info.table_name)
返回:
TestTable1
尝试连接到 TestTable1 时出现以下错误.
Trying to connect to TestTable1 gives the error below.
dfTable = pd.read_sql_table(TestTable1, cnxn)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-14-a24de1550834> in <module>()
----> 1 dfTable = pd.read_sql_table(TestTable1, cnxn)
2 #dfQuery = pd.read_sql_query("SELECT FROM [TestQuery1]", cnxn)
NameError: name 'TestTable1' is not defined
使用单引号再次尝试会出现以下错误.
Trying again with single quotes gives the error below.
dfTable = pd.read_sql_table('TestTable1', cnxn)
---------------------------------------------------------------------------
NotImplementedError Traceback (most recent call last)
<ipython-input-15-1f89f9725f0a> in <module>()
----> 1 dfTable = pd.read_sql_table('TestTable1', cnxn)
2 #dfQuery = pd.read_sql_query("SELECT FROM [TestQuery1]", cnxn)
C:\Users\myfiles\Anaconda3\lib\site-packages\pandas\io\sql.py in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize)
250 con = _engine_builder(con)
251 if not _is_sqlalchemy_connectable(con):
--> 252 raise NotImplementedError("read_sql_table only supported for "
253 "SQLAlchemy connectable.")
254 import sqlalchemy
NotImplementedError: read_sql_table only supported for SQLAlchemy connectable.
我尝试回到驱动程序问题并重新安装 32 位版本，但没有任何运气.
I have tried going back to the driver issue and reinstalling a 32bit version without any luck.
有人有什么想法吗?
推荐答案
给定一个表名和一个可连接的 SQLAlchemy，返回一个 DataFrame.此函数不支持 DBAPI 连接.
Given a table name and an SQLAlchemy connectable, returns a DataFrame. This function does not support DBAPI connections.
由于pyodbc是DBAPI，使用查询方式，pandas.read_sql con 参数确实支持 DBAPI:
Since pyodbc is a DBAPI, use the query method, pandas.read_sql which the con argument does support DBAPI:
dfTable = pd.read_sql("SELECT * FROM TestTable1", cnxn)
这篇关于尝试使用 PYODBC 将 Access 数据库表读入 Pandas 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
