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.
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).
Next, let’s create:
product_id | product_name | price |
1 | Computer | 800 |
2 | Printer | 150 |
3 | Desk | 400 |
4 | Chair | 120 |
5 | Tablet | 300 |
To connect Python to Access:
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)
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!!!