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 in to “Short Text”, because the SQL statement is formatted as a string. Uploading as other data types will require some additional formatting. To edit the data types of your table, open the table and select “Design View” under the “Home” tab.

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
"""
connection_string = r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Add_Path\To_DB\Here\Your_DB.mdb;' 
conn = pyodbc.connect(connection_string)
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.

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.