How to Connect Python to MS Access Database using Pyodbc

August 21, 2021

Need to connect Python to MS Access database using pyodbc?

If so, you’ll see the complete steps to establish this type of connection from scratch.

Steps to Connect Python to MS Access using Pyodbc

Step 1: Install the Pyodbc package

To start, install the pyodbc package that will be used to connect Python to Access. You may use PIP to install the pyodbc package:

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, let’s create:

  • An Access database called: test_database
  • A table called: products
  • The products table would contain the following columns and data:
product_id product_name price
1 Computer 800
2 Printer 150
3 Desk 400
4 Chair 120
5 Tablet 300

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:

C:\Users\Ron\Desktop\Test\test_database.accdb

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)