Table of contents
In this article, we’ll learn how to insert multiple entries or data in the database using a single SQL query in Python.
Insert Multiple Entries in the Database
We’ll see how we can insert multiple entries in the two different databases SQLite and MySQL.
SQLite
Create a Python file and write the following code inside it.
import sqlite3
# Create and connect with database
connection = sqlite3.connect("cars.db")
# Create cursor to interact with database
cursor = connection.cursor()
# Create database table
cursor.execute(
"""CREATE TABLE IF NOT EXISTS car (brand text, model text)"""
)
entries = [
('Ferrari', '812 Superfast'),
('Porsche', 'Carrera GT'),
('BMW', 'i8'),
('BMW', 'X7'),
('Ferrari', '488 GTB')
]
# Insert entries in the database table
cursor.executemany(
"""INSERT INTO car (brand, model) VALUES (?, ?)""", entries
)
# Commit the changes
connection.commit()
# Fetch the result from the database table
result = cursor.execute(
"""SELECT * FROM car"""
)
# Print the entries in the table
data = result.fetchall()
for each in data:
print(each)
# Close the database connection
connection.close()
This code imports the sqlite3
library that helps create and interact with the SQLite database.
We created and connected (sqlite3.connect("cars.db")
) to the cars.db
database and then created a cursor (connection.cursor()
) to interact with the database.
Using the cursor, we executed an SQL query to create a table named car
with two columns: brand
and model
in the database.
Then, we have a list of tuples containing the car information stored in the entries
variable.
Now if you look closely, we used executemany() that will run for every parameter (entries). This single line is executed five times for five different entries.
We used question mark (?) in the SQL query as a placeholder for the values. In SQLite, question mark (?) is used as a placeholder but for MySQL or PostgreSQL, this will be different.
Next, the changes were saved using connection.commit()
.
Another SQL query was executed to select all of the data from the table, which was then retrieved using fetchall()
and printed.
Finally, the database connection was closed using connection.close()
.
('Ferrari', '812 Superfast')
('Porsche', 'Carrera GT')
('BMW', 'i8')
('BMW', 'X7')
('Ferrari', '488 GTB')
MySQL
Create a Python file and write the following code.
import pymysql
# Establish MySQL connection with Python
sql_connection = pymysql.connect(
host="localhost",
user="root",
password="xxxxxx",
database='cars',
cursorclass=pymysql.cursors.DictCursor
)
# Create cursor to interact with database
cursor = sql_connection.cursor()
# Create database table
cursor.execute(
"""CREATE TABLE IF NOT EXISTS car (brand text, model text)"""
)
# Entries to be made
entries = [
('Ferrari', '812 Superfast'),
('Porsche', 'Carrera GT'),
('BMW', 'i8'),
('BMW', 'X7'),
('Ferrari', '488 GTB')
]
# Insert entries in the database table
cursor.executemany(
"""INSERT INTO car (brand, model) VALUES (%s, %s)""", entries
)
# Save the changes in the database
sql_connection.commit()
# Fetch data from the database table
cursor.execute(
"""SELECT * FROM car"""
)
data = cursor.fetchall()
for each in data:
print(each)
# Close the connection with database
sql_connection.close()
In this code, we’ve used the PyMySQL database driver to connect with the MySQL database in Python.
We performed all the preliminary operations such as connecting to MySQL server and creating a cursor object using PyMySQL.
Then, we created a table named car
with two columns within the cars
database by executing an SQL query.
Next, we stored a list of tuples containing relevant data for the table in the entries
variable.
Then we used executemany()
to execute a single query repeatedly for each entry. This operation will insert every entry in the table stored in the entries variable.
In MySQL, we use (%s) as a placeholder for a value.
Next, the changes were saved using connection.commit()
.
Then, we selected all rows in the database table car
by executing an SQL query. The selected rows were then fetched using fetchall()
and printed.
{'brand': 'Ferrari', 'model': '812 Superfast'}
{'brand': 'Porsche', 'model': 'Carrera GT'}
{'brand': 'BMW', 'model': 'i8'}
{'brand': 'BMW', 'model': 'X7'}
{'brand': 'Ferrari', 'model': '488 GTB'}
We got the results in a dictionary format because we set the cursor class to DictCursor
.
That’s all for now.
Keep Coding✌✌.