Python sqlite3 upsert: Update if exists, else insert

Introduction

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Python’s sqlite3 module allows you to interact with SQLite databases using a simplified Python API. This tutorial presents a deep dive into the ‘upsert’ operation using the sqlite3 module in Python, a technique to either update a row if it already exists, or insert it if it does not.

Getting a grip on upserts is crucial for data integrity and efficiency, especially when dealing with large datasets or applications where the database state can change rapidly. Here, we will walk through multiple code examples, evolving from basic to advanced use cases, to master the upsert operation in SQLite through Python.

Getting Started with sqlite3

Before diving into upserts, you need to have Python and SQLite set up on your system. Python comes with the sqlite3 module by default, so there’s no need for additional installations if you’ve got Python. Verify the SQLite version with the following code snippet:

import sqlite3
print(sqlite3.sqlite_version)

This should print the version of the SQLite library that comes bundled with Python.

Basic Upsert Operation

The term ‘upsert’ is a portmanteau of ‘update’ and ‘insert’. In SQLite, the upsert operation is achieved using the ON CONFLICT clause along with INSERT. Here’s a simple example:

import sqlite3

def create_connection(db_file):
    """Create a database connection to a SQLite database."""
    try:
        conn = sqlite3.connect(db_file)
        print("Connection established.")
        return conn
    except Exception as e:
        print(e)
        return None

def upsert_example(conn):
    sql = '''
    INSERT INTO students (id, name) VALUES (?, ?)
    ON CONFLICT(id) DO UPDATE SET name = excluded.name;
    '''
    cur = conn.cursor()
    cur.execute(sql, (1, 'John Doe'))
    conn.commit()

if __name__ == '__main__':
    conn = create_connection('example.db')
    if conn:
        upsert_example(conn)
        print("Upsert completed.")
        conn.close()

In the example above, if a row with the ID 1 already exists in the students table, its name will be updated to ‘John Doe’. If it doesn’t, a new row with these values will be inserted.

Working with Composite Keys

In some databases, you might have composite keys (two or more columns that uniquely identify rows). Here’s how you can modify the upsert query to handle composite keys:

import sqlite3

def upsert_with_composite_keys(conn):
    sql = '''
    INSERT INTO courses (course_id, student_id, grade) VALUES (?, ?, ?)
    ON CONFLICT(course_id, student_id) DO UPDATE SET grade = excluded.grade;
    '''
    cur = conn.cursor()
    cur.execute(sql, ('CS101', 1, 'A'))
    conn.commit()

This will update the grade for a specific course and student if that combination already exists, or insert a new row otherwise.

Error Handling and Rollbacks

When working with databases, errors can occur due to various reasons such as constraints violations or incorrect SQL syntax. It’s essential to handle these errors gracefully and, if necessary, rollback changes to maintain data integrity. An example of handling errors and performing a rollback looks like this:

import sqlite3
from sqlite3 import Error

def upsert_with_error_handling(conn):
    try:
        sql = '''
        INSERT INTO employees (id, name, role) VALUES (?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET name=excluded.name, role=excluded.role;
        '''
        cur = conn.cursor()
        cur.execute(sql, (2, 'Jane Doe', 'Developer'))
        conn.commit()
    except Error as e:
        print("Error: ", e)
        conn.rollback()

Here, if an error occurs during the upsert operation, the transaction is rolled back, and the database remains unchanged. Error handling is crucial for maintaining the database’s integrity.

Advanced Usage: Conditional Upserts

In more complex scenarios, you might want to upsert based on certain conditions beyond the primary or unique keys. SQLite supports this through subqueries and conditional expressions:

import sqlite3

def conditional_upsert(conn):
    sql = '''
    INSERT INTO inventory (product_id, quantity) VALUES (?, ?)
    WHERE NOT EXISTS (
        SELECT 1 FROM inventory WHERE product_id = ? AND quantity >= ?
    );
    '''
    cur = conn.cursor()
    cur.execute(sql, (3, 50, 3, 50))
    conn.commit()

This query tries to insert a new inventory record but only if there isn’t already a record with the same product ID and a quantity greater than or equal to 50.

Conclusion

Mastering the upsert operation within SQLite using Python’s sqlite3 module can significantly enhance data management efficiency and integrity in your applications. Through understanding and implementing basic to advanced upsert techniques, developers can ensure that their database interactions are both powerful and precise. This guide has provided various examples to solidify your grasp on upsert operations, equipping you with the knowledge to effectively manage data in your next Python project.