Python sqlite3 – INSERT Multiple Rows to TABLE

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 to a table are:

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

Example 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 cursor.rowcount property.

Output

We have inserted 3 records to the table.

Summary

In this tutorial of Python Examples, we learned to insert multiple records into sqlite3 table in a single query with the help of well detailed examples.