Accessing an Access Database with Pandas
Interfacing Pandas to an Access Database¶
Introduction¶
This post is intended to be a guide for Python users, who wish to process a Microsoft (MS) Access database. The assumption is that pandas will be the primary analysis tool.
One option is to read the Access datatables into a MS Excel spreadsheet. This allows for an initial exploration, and access to Excel spreadsheets is well supported by pandas, but the two step translation loses the meta-data (column types, etc), so direct interfacing was chosen for implementation.
Background¶
Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The extensive support for ODBC in the Python ecosystem makes it the obvious choice for interfacing to MS Access.
As shown below, the appropriate drivers from MS must be installed to support the interfacing operations.
pyodbc Package¶
The pyodbc package was the first choice for an implementation of code to read the databasde
import pandas as pd
import pyodbc
# used for environment documentation
import sys
import os
import platform
import datetime
%load_ext watermark
%load_ext lab_black
Driver environment¶
As discussed above, the appropriate software drivers from MS must be installed to support the intergace to Access databases. We can determine the drivers available by the following code snippet, which create a list of the drivers available (as seen by the pyodbc package).
[x for x in pyodbc.drivers()]
In the initial run of this snippet, the only drivers available were the SQL Server drivers. Going to the link
https://www.microsoft.com/en-us/download/details.aspx?id=54920
and clicking the "Download" button will install the Microsoft Access Driver, as shown above.
Testing the Interface¶
To test the interface, we create a connection string that specifies the interfacing driver we need, and the database location (in this case, local to the machine running Python). Then we create a connection.
connStr = (
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=D:\WaterQualityDB2022\wq2022.accdb;"
)
cnxn = pyodbc.connect(connStr)
We know from the exploration via Excel, that there is a table Equipment. We attempt to read the table into pandas.
sql_qry = 'select * from Equipment'
df = pd.read_sql(sql_qry, cnxn)
df.head()
The problem here is that although this works, we have a warning message from pandas that the ODC access we are using is not supported:
UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
Now in general, application or package warning messages have to considered seriously, especially in software intended to have an extended lifetime, because subsequent updates to pandas could totally remove support for non-SQLAlchemy access methods.
SQLAlchemy Implementation¶
Investigation of integration of the SQLAlchemy package, pandas, and MS Access revealed one potential item of concern: MS Access is not a supported database that the SQLAlchemy project supports. I suspect that this is because Python (especially in the machine learning domain) is oriented towards large to very large databases, and not the relatively small databases that MS Access is typically used for.
However, there is an independent Python package sqlalchemy_access, that provides the interface between SQLAlchemy and MS Access. Note that we still have to import the pyodbc package. sqlalchemy_access did not appear to be in the Anaconda ecosystem, so I just downloaded it from GitHub GitHub link
import pandas as pd
import sqlalchemy as sa
import sqlalchemy_access
import pyodbc
Next (as before), we define a connection specification, and establish a connection to the database.
import sqlalchemy_access
connection_string = (
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=D:\WaterQualityDB2022\wq2022.accdb;"
)
connection_url = sa.engine.URL.create(
"access+pyodbc",
query={"odbc_connect": connection_string},
)
engine = sa.create_engine(connection_url)
Now we can retrieve the Equipment table, and display the contents, without generating warning messages.
qry = 'select * from Equipment;'
df = pd.read_sql_query(qry, engine)
df.head(1).T
Later SQLAlchemy¶
There is a problem: at sqlalchemy 2.0.11, the code above gives :
AttributeError: 'OptionEngine' object has no attribute 'execute'The suggested fix is
import pandas as pd
from sqlalchemy import create_engine, text
engine = create_engine(connection_url)
query = 'SELECT * FROM my_table'
df = pd.read_sql_query(sql=text(query), con=engine.connect())This fix also works fine at sqlalchemy 1.4.39
df = pd.read_sql_query(
sql=sa.text(qry),
con=engine.connect(),
)
df.head(1).T
Database Structure¶
We can confirm that SQLAlchemy 'sees' the same database tables, as can be seen via Excel.
from sqlalchemy import inspect
insp = inspect(engine)
insp.get_table_names()
Reproducibility¶
This sections contains information to support the reproducibility of the software.
import sys
import os
import platform
import datetime
# | warn: false
%reload_ext watermark
# | warn: false
%reload_ext lab_black
Python execution environment¶
theNotebook = 'InterfacingToWQDatabase.ipynb'
print(f'Notebook: {theNotebook}')
print('python version : ' + sys.version)
print('pandas version : ' + pd.__version__)
print('current wkg dir: ' + os.getcwd())
print(
'Notebook run at: '
+ str(datetime.datetime.now())
+ ' local time'
)
print(
'Notebook run at: '
+ str(datetime.datetime.utcnow())
+ ' UTC'
)
print('Notebook run on: ' + platform.platform())
watermark data¶
Execution environment as reported by the watermark utility
%watermark
%watermark -h -iv -co