In this article i want to show you How to Connect Python with MS Access Database, we will learn that how you can connect your Python code with MS Access Database, how you can insert data to MS Access Database, how you can select data from MS Access Database, how to delete and update data in MS Access Database. and for this purpose we are using Pyodbc library with MS Access.
What is MS Access ?
Microsoft Access is a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.
What is 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.
Pyodbc Installation
OK now we want to install pyodbc, you can simply use pip for the installation.
|
1 |
pip install pyodbc |
Creating Database in MS Access
All right, after installation we need to create a database in ms access, you can just open your Microsoft Access, and create blank database, give a name for your database. after that add some data to the database, and click on save, when you are going to save your database in MS Access, there will be a pop up window for giving the table name, and we want to give the table name as users.
MS Access Drivers for Python
OK now we want to check available drivers for MS Access, for this your can the drivers() function from pyodbc library and this is the code.
|
1 2 3 4 5 |
import pyodbc msa_drivers = [x for x in pyodbc.drivers() if 'ACCESS' in x.upper()] print(f'MS-Access Drivers : {msa_drivers}') |
Run the code and you will see different drivers, but we want to use [‘Microsoft Access Driver (*.mdb, *.accdb), make sure that this driver is available for you.
Checking Database Connection in MS Access
OK we have our MS Access database, and now we want to check the database connection, this is the code for checking MS Access database connection in Python.
|
1 2 3 4 5 6 7 8 9 10 11 |
import pyodbc try: con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\parwizforogh\Documents\pydb.accdb;' conn = pyodbc.connect(con_string) print("Connected To Database") except pyodbc.Error as e: print("Error in Connection", e) |
First you need to import pyodbc library.
|
1 |
import pyodbc |
After that you need you need to specify the Driver for MS Access, also the path of the database.
|
1 2 |
con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' \ r'DBQ=C:\Users\parwizforogh\Documents\msdatabase.accdb;' |
Now you need to create connection between MS Access and Python code.
|
1 |
conn = pyodbc.connect(con_string) |
Run the complete code and this is the result.
Inserting Data to MS Access Database
In this part we want to insert some data in to our MS Access using Python code, even tough if you remember i have already added some data manually, but this time we are using Python code with MS Access. so this is the complete code for inserting data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
import pyodbc try: con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' \ r'DBQ=C:\Users\parwizforogh\Documents\msdatabase.accdb;' conn = pyodbc.connect(con_string) cursor = conn.cursor() myuser = ( (6, 'data', 'data@gmail.com'), (7, 'python', 'python@gnail.com'), (8, 'java', 'java@gmail.com'), ) cursor.executemany('INSERT INTO users VALUES (?,?,?)', myuser) conn.commit() print('Data Inserted') except pyodbc.Error as e: print("Error in connection", e) |
First you need to create Cursor object.
|
1 |
cursor = conn.cursor() |
Basically in this code we want to insert multiple rows at the same time. by this reason we are using executemany(), if you want to insert one row than you can use execute().
|
1 2 3 4 5 6 7 8 9 10 |
myuser = ( (6, 'data', 'data@gmail.com'), (7, 'python', 'python@gnail.com'), (8, 'java', 'java@gmail.com'), ) cursor.executemany('INSERT INTO users VALUES (?,?,?)', myuser) conn.commit() |
Run the code and check your MS Access, you will see the three new data, make sure that you have refreshed the database.
Selecting Data from MS Access Database
Now we want to select all our data using python code, so this is the complete code for selecting data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import pyodbc try: con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' \ r'DBQ=C:\Users\parwizforogh\Documents\msdatabase.accdb;' conn = pyodbc.connect(con_string) cur = conn.cursor() cur.execute('SELECT * FROM users') for row in cur.fetchall(): print(row) except pyodbc.Error as e: print("Error in Connection") |
First you need to create cursor object, and after that execute your query for selecting the data, we want to select all the data from MS Access.
|
1 2 |
cur = conn.cursor() cur.execute('SELECT * FROM users') |
Now we want to iterate over that data, and we want to fetch all of our data using fetchall() method.
|
1 2 |
for row in cur.fetchall(): print(row) |
Run the code and this is he result.
Updating Data in MS Access Database
Now it is time to update our data, basically in this code we want to update a row in MS Access database using Python code. this is the complete code for updating the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import pyodbc try: con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' \ r'DBQ=C:\Users\parwizforogh\Documents\msdatabase.accdb;' conn = pyodbc.connect(con_string) newname = "newupdateddata" user_id = 2 cur = conn.cursor() cur.execute('UPDATE users SET name = ? WHERE id = ?', (newname, user_id)) conn.commit() print("Data updated") except pyodbc.Error as e: print("Error in connection", e) |
We want to update the name of our second id.
|
1 2 |
newname = "newupdateddata" user_id = 2 |
Create Cursor object and execute your query, make sure that you have committed the database.
|
1 2 3 |
cur = conn.cursor() cur.execute('UPDATE users SET name = ? WHERE id = ?', (newname, user_id)) conn.commit() |
Run the code and you will see the updated data.
Deleting Data in MS Access Database
OK now we want to delete our data, basically we want to delete the user id 2 from the MS Access Database using Python code. this is the complete code for deleting the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import pyodbc try: con_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' \ r'DBQ=C:\Users\parwizforogh\Documents\msdatabase.accdb;' conn = pyodbc.connect(con_string) user_id = 2 cur = conn.cursor() cur.execute('DELETE FROM users WHERE id = ?', (user_id)) conn.commit() print("Data Deleted ") except pyodbc.Error as e: print("Error in connection", e) |
Create Cursor object and execute your query, after that commit your database.
|
1 2 3 |
cur = conn.cursor() cur.execute('DELETE FROM users WHERE id = ?', (user_id)) conn.commit() |
Run the code and you can see that 2 id data is deleted.
Learn More on TKinter
- How to Create Conutdown Timer with Python & TKinter
- Create GUI Applications with Python & TKinter
- Python TKinter Layout Management
- How to Create Label in TKinter
- How to Create Buttin in Python TKinter
- Build Music Player in Python TKinter
- Python GUI Programming with TKinter
- TKinter VS PyQt, Which one is Good
- Creating Custom Widgets in TKinter