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 a simple 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

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: names_table
  • The names_table contains the following data and fields:

 

First_NameLast_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27

 

The names_table in Access should look like this:

 

Insert Values into MS Access Table using Python

 

We’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 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 Insert Values into MS Access Table using Python

 

Step 3: Insert records into the Access table

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

 

First_NameLast_NameAge
MikeJordan55
MiaMogran66

 

Using the generic ‘Insert’ syntax that we saw at the beginning of this post, you can then apply the following code in Python to insert the 2 records into the MS Access table.

Do not forget to add db.commit() at the end of the Python code to ensure that the Insert command would be applied.

 

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

 

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 should see the new records at the bottom of the table:

 

Insert Values into MS Access Table using Python