Insert Values into MS Access Table using Python

In this short tutorial, I’ll show you how to insert values into MS Access table using Python.

I’ll use a simple example to add two records to an existing table in MS Access.

But before we begin, here is the general syntax to insert a record into an Access table:

INSERT INTO Table_Name (Column1_Name, Column2_Name,...)

VALUES('Value1 for Column1', 'Value2 for Column2',...)

Let’s now review an example with the steps needed to insert values into MS Access table using Python.

Steps to Insert Values into MS Access Table using Python

Step 1: Create a new MS Access database and table

Let’s say that you created a simple MS Access database, where:

  • The database name is test_database
  • This database has only one table called the names_table
  • The names_table contains the following data and fields:
First_NameLast_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27

This is how the names_table would look like in Access:

Insert Values into MS Access Table using Python

You’ll later see how to insert two records into that table.

Step 2: Connect Python to Access

Next, you’ll need to connect Python to Access using the pyodbc module.

You may want to check the following tutorial that explains how to establish a connection between Python and MS Access from scratch!

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 reflect the location where your Access database is stored on your computer.

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

C:\Users\Ron\Desktop\test_database.accdb

And the Python code that I used to connect to Access looks like this:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\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 (adjusted to your path), you’ll get the same values as in step-1:

How to Insert Values into MS Access Table using Python

Step 3: Insert records into the Access table

Let’s say that you want to insert the following 2 new records into the names_table:

First_NameLast_NameAge
MikeJordan55
MiaMogran66

Recall that the general syntax to insert a record into an Access table is:

INSERT INTO Table_Name (Column1_Name, Column2_Name,...)

VALUES('Value1 for Column1', 'Value2 for Column2',...)

You can then apply the following code in Python (adjusted to your path) in order to insert the 2 records into the MS Access table:

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

  
cursor.execute('''
                    INSERT INTO names_table (First_Name, Last_Name, Age)
                    VALUES('Mike', 'Jordan',55)

                  ''')


cursor.execute('''
                    INSERT INTO names_table (First_Name, Last_Name, Age)
                    VALUES('Mia', 'Mogran',66)
                    
                  ''')

conn.commit()

Don’t forget to add conn.commit() at the end of the Python code to ensure that the Insert command would be applied.

Note that if you try to insert multiple records within the same cursor.execute(”’ ”’) block, you’ll get the following error in Python:

pyodbc.ProgrammingError: (‘42000’, ‘[42000] [Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement. (-3517) (SQLExecDirectW)’)

As a workaround, you may insert a single record per cursor.execute(”’ ”’) block.

Step 4: Confirm that the records were inserted into the Access table

To confirm that the two records were actually inserted into the Access table, reopen/refresh the names_table in Access.

You would then see the new records at the bottom of the table:

Insert Values into MS Access Table using Python

 

Looking to delete records from MS Access table using Python?

No problem! Check the following tutorial that explains the steps to delete records from MS Access using Python.