How to Bulk Insert Rows into SQLite in Python

Insert many rows into an SQLite table in one call with cursor.executemany, then verify them with a SELECT query.

Easy Python 3.9+ Aug 9, 2026 Files & data 13 views 0 copies

sqlite bulk-insert database executemany

Python code

27 lines
Python 3.9+
Run it 
import sqlite3

# Create an in-memory database and a table
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE products (name TEXT, price REAL, quantity INTEGER)")

# Data to insert in bulk
products = [
    ("Laptop", 999.99, 5),
    ("Mouse", 19.99, 50),
    ("Keyboard", 49.99, 30),
    ("Monitor", 199.99, 15),
    ("USB Cable", 9.99, 100)
]

# Bulk insert with executemany
cursor.executemany("INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)", products)
conn.commit()

# Verify the inserted rows
cursor.execute("SELECT * FROM products")
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

Output

stdout 
('Laptop', 999.99, 5)
('Mouse', 19.99, 50)
('Keyboard', 49.99, 30)
('Monitor', 199.99, 15)
('USB Cable', 9.99, 100)

How it works

The executemany method takes an SQL template with ? placeholders and a list of tuples, executing the same INSERT statement once per tuple. Because the connection is in-memory, it's created fresh every run and doesn't persist after close(). The commit() call ensures the changes are written; without it, the transaction might be rolled back when the connection closes. Finally, fetchall() returns all selected rows as a list of tuples that you can iterate over to print.

Common mistakes

  • Forgetting to call conn.commit() after executemany, losing changes on close.
  • Passing a generator instead of a list or iterable that can be consumed only once.

Variations

  1. Use sqlite3.connect('mydb.db') to write to a file instead of in-memory.
  2. Use executemany with parameterized queries for other SQL like UPDATE or DELETE.

Real-world use cases

  • Seeding a development database with thousands of sample records at startup.
  • Bulk importing CSV rows into a local SQLite cache for analysis or reporting.
  • Persisting user-generated data like form submissions in batches for performance.

Sponsored

Run this sample

Open the browser IDE to tweak the example and see results without installing anything.

Open editor

More from Files & data

Keep learning

Related tutorials and quizzes for this topic.

Tutorials

Quizzes