How to Delete Records from MS Access using Python

Looking to delete records from MS Access using Python?

If so, I’ll show you an example with the steps to delete two records from an existing MS Access table using Python.

Generally speaking, this is the syntax that you can apply to delete records from MS Access table:

 

DELETE FROM Table_Name 
WHERE Condition

 

Before you can apply the above syntax to delete your records, you’ll need to establish a connection between MS Access and Python.

Let’s now review an example with the steps needed to delete given records.

Steps to Delete Records from MS Access using Python

Step 1: Create a new MS Access database

To start, you can create a simple MS Access database, where:

  • The database name is: test_database
  • This database has only one table called: names_table
  • The names_table contains the following data and fields:

 

First_NameLast_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27
MikeJordan55
MiaMogran66

 

This is how the table would look like in MS Access:

 

Insert Values into MS Access Table using Python

 

Step 2: Connect Python to Access

Next, you’ll need to connect Python to MS Access. You can use the pyodbc module in Python to establish such a connection.

The following guide explains the full steps to connect Python to MS Access.

Below you’ll find the Python code that you can use to connect Python to MS Access. Note that you’ll need to modify the connection string to refer to the location where your Access database is stored on your computer.

In my case, the Access database is stored under this path:

C:\Users\Doron E\Desktop\test_database.accdb

And the Python code to connect to Access would look like this:

 

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Doron E\Desktop\test_database.accdb;')
cursor = conn.cursor()
cursor.execute('select * from names_table')
    
for row in cursor.fetchall():
    print (row)

 

Once you run the above code, you’ll get the same values as we saw in step-1:

 

How to Delete Records from MS Access using Python

Step 3: Delete records from the Access table

Now let’s delete 2 records from the Access table using Python.

For example, let’s say that you want to delete these two records:

 

First_NameLast_NameAge
MikeJordan55
MiaMogran66

 

Recall that at the beginning of this tutorial we saw the general syntax to delete records from MS Access table:

 

DELETE FROM Table_Name 
WHERE Condition

 

You can then apply this syntax to delete the two records from the names_table as follows:

 

import pyodbc
  
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Doron E\Desktop\test_database.accdb;')
cursor = conn.cursor()

cursor.execute('''
                DELETE FROM names_table 
                WHERE First_Name in ('Mike','Mia')
               ''')

conn.commit()

 

Don’t forget to add conn.commit() at the end to ensure that the deletion of the two records would get executed.

Step 4: Confirm that the records were deleted

You may want to confirm that the 2 records were indeed deleted from the Access table.

To do so, simply open the names_table in Access. You will no longer see those 2 records:

 

Insert Values into MS Access Table using Python

 

Looking to insert values into Access using Python?

No problem! Check the following tutorial that explains the steps to insert values into MS Access using Python.