Python sqlite3 - INSERT Multiple Rows to TABLE - Python Examples

Python - Insert Multiple Rows to sqlite3 Table

In our previous article - insert row into sqlite3 table, we have seen how to insert a single record to a table. In this tutorial, we will learn how to insert multiple rows using a single INSERT INTO query.

Steps to insert multiple rows into sqlite3 table

The steps to insert multiple records into a table are:

  1. Prepare the connection to the database and then get a cursor.
  2. Get all your records into a list.
  3. Use executemany() method and pass the query and the records list as arguments to the method.

Examples

1. Insert multiple rows into sqlite table

In the following example, we will insert three records into a table.

Python Program

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

#records or rows in a list
records = [(1, 'Glen', 8),
			(2, 'Elliot', 9),
			(3, 'Bob', 7)]

#insert multiple records in a single query
c.executemany('INSERT INTO students VALUES(?,?,?);',records);

print('We have inserted', c.rowcount, 'records to the table.')

#commit the changes to db			
conn.commit()
#close the connection
conn.close()

To check if the query is executed successfully and inserted all the three records, we have printed the cursor.rowcount property.

Output

We have inserted 3 records to the table.

Explanation of the Code:

  1. Creating Connection and Cursor: The connection to the database is created using sqlite3.connect('mysqlite.db'), and a cursor object is obtained with conn.cursor().
  2. Preparing Records List: We prepare a list of tuples records, where each tuple represents a row to be inserted into the table. Each tuple corresponds to the rollno, name, and class fields of the table.
  3. Executing the Query: The executemany() function is used to execute the INSERT INTO query for multiple rows at once. The query contains placeholders (?,?,?) for each of the three fields in the tuple.
  4. Commit the Changes: After the execution, conn.commit() is used to save the changes to the database.
  5. Closing the Connection: Finally, the connection to the database is closed using conn.close().

2. Insert multiple rows and handle errors

In this example, we will insert multiple rows into the table and handle any potential errors during the insertion.

Python Program

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

#records or rows in a list
records = [(4, 'Anna', 10),
			(5, 'Steve', 11),
			(6, 'Alice', 12)]

#try to insert multiple records
try:
    c.executemany('INSERT INTO students VALUES(?,?,?);', records)
    print(f'We have inserted {c.rowcount} records to the table.')
except sqlite3.Error as e:
    print(f'Error occurred: {e}')

#commit the changes to db			
conn.commit()
#close the connection
conn.close()

Output

We have inserted 3 records to the table.

Explanation of the Code:

  1. Error Handling: A try block is added around the executemany() call to catch any potential errors (such as inserting a duplicate record) that may occur during the execution.
  2. Printing Errors: If an error occurs, the exception sqlite3.Error is caught and the error message is printed.
  3. Successful Insertion: If no error occurs, the number of inserted records is printed as usual.

Summary

In this tutorial, we learned how to insert multiple records into an sqlite3 table in a single query using the executemany() method. We covered:

  • Inserting multiple rows using a list of tuples.
  • How to check the number of inserted rows using cursor.rowcount.
  • Handling errors during insertion using a try-except block.

These techniques will help in performing bulk insertions efficiently in SQLite with Python.

Python Libraries