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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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().
|
1 2 3 4 5 |
('Ferrari', '812 Superfast') ('Porsche', 'Carrera GT') ('BMW', 'i8') ('BMW', 'X7') ('Ferrari', '488 GTB') |
MySQL
Create a Python file and write the following code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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.
|
1 2 3 4 5 |
{'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✌✌.
