Write a Python program to create a list of 5 items and print the second item three times on the output screen.
Answers
Answer:
Skip to content
Data to Fish
Main Menu
HomePythonInsert Values into MS Access Table using Python
Insert Values into MS Access Table using Python
Python / May 15, 2019
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_Name Last_Name Age
Jon Snow 22
Maria Smith 34
Emma Jones 51
Bill Yu 63
Jack Green 27
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_Name Last_Name Age
Mike Jordan 55
Mia Mogran 66
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:
Answer:
a Python program to create a list of 5 items and print the second item three times on the output screen