Python MS Access Database Connection using Pyodbc

Pyodbc is an open-source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience. 

In this tutorial, we will learn how to connect Python to an MS Access database using the Pyodbc library in this step-by-step tutorial.

• Step 1: Install the Pyodbc package
• Step 2: Create the database and table in Access
• Step 3: Connect Python to Access
• Step 4: Run the code in Python

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:

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)

Thanks for reading!!!

#python