Connect Python to MS Access using Pyodbc

In this short guide, you’ll see how to connect Python to MS Access using pyodbc.

Here are the steps:

Steps to Connect Python to MS Access

Step 1: Install the Pyodbc package

To start, install the pyodbc package that will be used to connect Python to Access:

pip install pyodbc

Tip: Before you connect Python to Access, you may want to check that your Python Bit version matches with your MS Access Bit version (e.g., use Python 64 Bit with MS Access 64 Bit).

Step 2: Create the database and table in Access

Next, create:

• An Access database called: test_database
• A table called: products
• The products table would contain the following columns and data:

Step 3: Connect Python to Access

To connect Python to Access:

• Add the path where you stored the Access file (after the syntax DBQ=). Don’t forget to add the MS Access file extension at the end of the path (‘.accdb‘)
• Add the table name within the SELECT statement

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path where you stored the Access file\file name.accdb;')
cursor = conn.cursor()
cursor.execute('SELECT * FROM table_name')
   
for row in cursor.fetchall():
    print(row)

For example, let’s suppose that the Access database is stored under the following path:

Where “test_database” is the MS Access file name within that path, and “.accdb” is the MS Access file extension.

Before you run the code below, you’ll need to adjust the path to reflect the location where the Access file is stored on your computer (also don’t forget to specify the table name within the SELECT statement. Here, the table name is products):

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\test_database.accdb;')
cursor = conn.cursor()
cursor.execute('SELECT * FROM products')
   
for row in cursor.fetchall():
    print(row)

Step 4: Run the code in Python

Run the code in Python, and you’ll get the same records as stored in the Access table:

(1, 'Computer', 800)
(2, 'Printer', 150)
(3, 'Desk', 400)
(4, 'Chair', 120)
(5, 'Tablet', 300)