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.

Examples

1. Insert multiple rows into sqlite table

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

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.