How to Connect Python with MS Access Database

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.

 

 

Installation

OK now we want to install pyodbc, you can simply use pip for the installation.

 

 

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.

How to Connect Python with MS Access Database
How to Connect Python with MS Access Database

 

 

 

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.

 

 

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.

MS Access Drivers
MS Access Drivers

 

 

 

 

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.

 

 

 

First you need to import pyodbc library.

 

 

After that you need you need to specify the Driver for MS Access, also the path of the database.

 

 

Now you need to create connection between MS Access and Python code.

 

 

Run the complete code and this is the result.

Python MS Access Database Connection
Python MS Access Database Connection

 

 

 

 

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.

 

 

First you need to create Cursor object.

 

 

 

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().

 

 

 

Run the code and check your MS Access, you will see the three new data, make sure that

you have refreshed the database.

Python MS Access Inserting Data
Python MS Access Inserting Data

 

 

 

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.

 

 

 

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.

 

 

 

Now we want to iterate over that data, and we want to fetch all of our data using fetchall()

method.

 

 

 

Run the code and this is he result.

Python MS Access Selecting Data
Python MS Access Selecting Data

 

 

 

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.

 

 

We want to update the name of our second id.

 

 

Create Cursor object and execute your query, make sure that you have committed

the database.

 

 

 

Run the code and you will see the updated data.

Python MS Access Updating Data
Python MS Access Updating 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.

 

 

Create Cursor object and execute your query, after that commit your database.

 

 

 

Run the code and you can see that 2 id data is deleted.

Python MS Access Deleting Data
Python MS Access Deleting Data

 

Thank you for visiting my website! If you enjoyed the free courses, please consider supporting my works on Patreon.

2 thoughts on “How to Connect Python with MS Access Database”

Leave a Comment