Sling Academy
Home/Python/Python sqlite3: How to perform bulk inserts

Python sqlite3: How to perform bulk inserts

Last updated: February 06, 2024

Introduction

Handling databases efficiently is a crucial requirement for many applications. Python, with its simplicity and elegance, provides various methods to interact with databases. One such popular lightweight database is SQLite. When working with SQLite databases, a common task is to insert multiple rows of data into a table. In this tutorial, we will explore how to perform bulk inserts in SQLite databases using Python’s sqlite3 module. We’ll start with the basics and progressively cover more advanced topics, including how to enhance performance during bulk inserts.

Setting Up Your Environment

First, ensure that you have Python installed on your machine. The sqlite3 module is included with Python, so no separate installation is necessary. You can check your Python version by running python --version in your terminal or command prompt.

Creating a SQLite Database and Table

import sqlite3

# Connect to or create a SQLite database
conn = sqlite3.connect('example.db')

cur = conn.cursor()

# Create a table
cur.execute('''CREATE TABLE IF NOT EXISTS inventory
               (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER)''')

conn.commit()

Before inserting data, make sure you have a table ready. The above code creates a SQLite database named ‘example.db’ and a table named ‘inventory’.

Performing Basic Bulk Inserts

The most straightforward approach for bulk inserts involves using the executemany() method of the cursor object. It allows you to execute a database operation (like insert) multiple times with different values.

data = [(1, 'Apple', 50),
        (2, 'Banana', 100),
        (3, 'Cherry', 150)]

# Perform bulk insert
cur.executemany('INSERT INTO inventory VALUES (?,?,?)', data)

conn.commit()

This method is simple and effective for small to medium-sized data sets.

Optimizing Bulk Inserts

For larger datasets, the performance can become a bottleneck. To optimize bulk inserts, consider disabling SQLite’s auto-commit feature by using transactions manually.

# Start a transaction
conn.execute('BEGIN TRANSACTION;')

# Perform bulk inserts
for row in data:
    cur.execute('INSERT INTO inventory VALUES (?,?,?)', row)

# Commit the transaction
conn.commit()

Wrapping your inserts in a transaction significantly improves the performance by reducing the number of disk writes.

Using the Faster ‘executescript’ for Non-Dynamic Bulk Inserts

If your bulk insert doesn’t require dynamic data (the data to insert is static and known ahead of time), you can use the executescript() method. This method executes a script of multiple SQL commands, which can include insert statements, in one go.

# Prepare a long insert script
insert_script = '''
BEGIN TRANSACTION;
INSERT INTO inventory VALUES (1, 'Mango', 200);
INSERT INTO inventory VALUES (2, 'Grapes', 300);
COMMIT;
'''

cur.executescript(insert_script)

This method can be faster than executemany() but lacks the flexibility of inserting dynamic data.

Handling Large Datasets with Advanced Techniques

When dealing with very large datasets, even the transaction method may not be efficient enough. In such cases, consider employing more sophisticated techniques like using temporary files or optimizing SQLite parameters (such as journal_mode and cache_size). Advanced techniques require a deeper understanding of both SQLite and how Python interacts with it, and are beyond the scope of this beginner tutorial.

Conclusion

In conclusion, Python’s sqlite3 module provides several efficient ways to perform bulk inserts, ranging from simple commands to complex optimizations for performance. By understanding and applying these techniques appropriately, you can significantly improve the speed and efficiency of your SQLite database operations. Remember, the right method depends on your specific scenario, including the size of your data and the nature of your application.

Next Article: Python sqlite3: Define a table with auto-incrementing primary key

Previous Article: Python sqlite3: Insert a new row and get the ID

Series: Data Persistence in Python – Tutorials & Examples

Python

You May Also Like

  • Introduction to yfinance: Fetching Historical Stock Data in Python
  • Monitoring Volatility and Daily Averages Using cryptocompare
  • Advanced DOM Interactions: XPath and CSS Selectors in Playwright (Python)
  • Automating Strategy Updates and Version Control in freqtrade
  • Setting Up a freqtrade Dashboard for Real-Time Monitoring
  • Deploying freqtrade on a Cloud Server or Docker Environment
  • Optimizing Strategy Parameters with freqtrade’s Hyperopt
  • Risk Management: Setting Stop Loss, Trailing Stops, and ROI in freqtrade
  • Integrating freqtrade with TA-Lib and pandas-ta Indicators
  • Handling Multiple Pairs and Portfolios with freqtrade
  • Using freqtrade’s Backtesting and Hyperopt Modules
  • Developing Custom Trading Strategies for freqtrade
  • Debugging Common freqtrade Errors: Exchange Connectivity and More
  • Configuring freqtrade Bot Settings and Strategy Parameters
  • Installing freqtrade for Automated Crypto Trading in Python
  • Scaling cryptofeed for High-Frequency Trading Environments
  • Building a Real-Time Market Dashboard Using cryptofeed in Python
  • Customizing cryptofeed Callbacks for Advanced Market Insights
  • Integrating cryptofeed into Automated Trading Bots