Inserting New Rows Into A Microsoft Access Database With Python and pyodbc

I recently automated the loading of data into a Microsoft Access database with pyodbc, a Python library for connecting to databases. ODBC stands for Open Database Connectivity. It can be used for a variety of Database Management Systems outside of Access also.

First, install libraries with pip. Enter in terminal or command prompt:

    1. python -m pip install pyodbc
    2. python -m pip install pandas

Next, check available Microsoft Access drivers on your computer. Enter the below statements into the Python interpreter:

python
>>> import pyodbc
>>> [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

Drivers for Access & Many Other Data Sources

The driver is the engine that allows you to connect to a specific type of database. The drivers available vary depending on your machine.

The two most common drivers for Access are Microsoft Access Driver (*.mdb) and Microsoft Access Driver (*.mdb, *.accdb). My computer only had *.mdb, which has been deprecated. My Access database was a .mdb file, so I was able to use this driver as shown below. Read more on Access drivers here.

“Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or comma-separated values (CSV) files.”  – Wikipedia

Database Data Types

I set all of the field data types to “Short Text” because I’m passing strings as SQL parameters below. Uploading as other data types may require additional formatting. To edit the data types of your table, open the table and select “Design View” under the “Home” tab. It got the job done for me!

Inserting new rows into a Microsoft Access Database:

import pandas as pd
import pyodbc

"""
pyodbc and Access DB Connection - Python Marketer
https://pythonmarketer.com/2019/11/30/inserting-new-records-into-a-microsoft-access-database-with-python/ """
"""
def format_dataframe_rows_as_list_of_tuples():
    """
    use list comprehension to format df rows as a list of tuples. 
    rows = [('email@gmail.com', '2019-12-04','Clean'),('email2@gmail.com', '2019-12-01','Junk')] 
    """
    df = pd.read_csv('Data_To_Add_To_Database.csv')
    df = df.fillna('')
    rows = [tuple(cell) for cell in df.values]
    return rows

"""
Rows are not added to DB until they are committed. 
Pass each row tuple as a SQL parameter (?,?,?). 
cursor.execute docs: https://www.mcobject.com/docs/Content/Programming/Python/Classes/Cursor/execute.htm
""" 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Add_Path\To_DB\Here\Your_DB.mdb;')
cursor = conn.cursor()
sql = ''' INSERT INTO Add_Table_Name_Here (EmailAddress, DateAdded, HygieneResult) 
          VALUES(?,?,?) '''
rows = format_dataframe_rows_as_list_of_tuples() 
for row in rows:
    cursor.execute(sql, row) 
conn.commit()

Conclusion

Running the above in command prompt uses pyodbc and SQL to add dataframe rows to a Microsoft Access DB. Passing each row as a SQL parameter has two benefits:

  1. It handles strings with single quotes (‘) and loads them to the DB.
  2. It protects against SQL injection attacks.

Access Limitation Disclaimer

Access topped out just shy of 10 million rows in my use case, when records stopped getting added to my database. So keep that in mind if you’re thinking about using Access to store your data.

Supplementary Resources

Insert Values into MS Access Table using Python

pyodbc documentation

Microsoft Documentation pyodbc example

The Python Cursor Class

Psycopg Cursor Class Documentation

2 thoughts on “Inserting New Rows Into A Microsoft Access Database With Python and pyodbc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.