errors trying to read Access Database Tables into Pandas with PYODBC

Question

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:

OS: Windows 7 Professional 64-bit (verified 9/6/2017)
Access version: Access 2016 32bit (verified 9/6/2017)
Python version: Python 3.6.1 (64-bit) found using >Python -V (verified 9/11/2017)
the AccessDatabaseEngine needed will be based on the Python bitness above
Windows database engine driver installed with AccessDatabaseEngine_X64.exe from 2010 release using >AccessDatabaseEngine_X64.exe /passive (verified 9/11/2017)

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')]

returns:

['Microsoft Access Driver (*.mdb, *.accdb)']

Setting the connection string.

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)

returns:

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.

I have tried going back to the driver issue and reinstalling a 32bit version without any luck.

Anybody have any ideas?

Parfait · Accepted Answer · 2017-09-12 02:42:29Z

5

Per the docs of pandas.read_sql_table:

Given a table name and an SQLAlchemy connectable, returns a DataFrame. This function does not support DBAPI connections.

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)

answered Sep 12, 2017 at 2:42

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aseem · Accepted Answer · 2018-09-11 08:58:18Z

-1

Reading db table with just table_name

import pandas
from sqlalchemy import create_engine

engine=create_engine('postgresql+psycopg2://user:password@localhost/db_name')
df=pandas.read_sql_table("table_name",engine)

answered Sep 11, 2018 at 8:58

Aseem

6,9578 gold badges57 silver badges96 bronze badges

1 Comment

Mike Williamson Over a year ago

The question was asking about MS Access, not PostgreSQL

Collectives™ on Stack Overflow

errors trying to read Access Database Tables into Pandas with PYODBC

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related