Public API Reference¶

This section provides comprehensive documentation for all public APIs in sqlalchemy_upsert_kit. The library is designed with a consistent, database-agnostic interface that works seamlessly across different database systems through SQLAlchemy.

Recommended Import Pattern:

import sqlalchemy_upsert_kit.api as sauk

# Access database-specific APIs
sauk.sqlite.insert_or_ignore(...)
sauk.sqlite.insert_or_replace(...)
sauk.sqlite.insert_or_merge(...)

# Future database support will follow the same pattern
# sauk.postgres.insert_or_ignore(...)
# sauk.mysql.insert_or_ignore(...)

Database Support¶

Currently Supported Databases¶

SQLite: Full support for all three upsert strategies
PostgreSQL: Coming soon
MySQL: Coming soon

All database implementations follow the same API pattern, ensuring consistent behavior and easy migration between database systems.

Core API Pattern¶

Every database implementation provides exactly three high-performance bulk upsert functions:

insert_or_ignore: Insert only new records, ignore conflicts
insert_or_replace: Replace existing records completely, insert new ones
insert_or_merge: Selectively update specific columns, preserve others

Common Parameters:

All three functions share a consistent parameter interface:

engine: SQLAlchemy engine for database connection
table: Target table for upsert operation
values: List of dictionaries containing record data
metadata: Optional metadata instance for table isolation
temp_table_name: Optional custom temporary table name
conn & trans: Optional for user-managed transaction mode

Return Values:

Each function returns a tuple indicating the operation results:

insert_or_ignore: (ignored_rows, inserted_rows)
insert_or_replace: (replaced_rows, inserted_rows)
insert_or_merge: (updated_rows, inserted_rows)

SQLite Implementation¶

Access SQLite upsert operations through the sauk.sqlite namespace:

import sqlalchemy_upsert_kit.api as sauk

# All SQLite upsert operations
ignored, inserted = sauk.sqlite.insert_or_ignore(engine, table, data)
replaced, inserted = sauk.sqlite.insert_or_replace(engine, table, data)
updated, inserted = sauk.sqlite.insert_or_merge(engine, table, data, columns)

insert_or_ignore¶

Performs conditional bulk insertion, inserting only records that don’t conflict with existing data.

Function Signature:

insert_or_ignore()

Core Parameters:

engine (sqlalchemy.Engine): Database engine for connection management
table (sqlalchemy.Table): Target table for insertion operations
values (list[dict]): Records to insert. Must include primary key values for conflict detection
metadata (sqlalchemy.MetaData, optional): Metadata instance for temporary table isolation. Creates new instance if None
temp_table_name (str, optional): Custom temporary table name. Auto-generated with timestamp if None
conn (sqlalchemy.Connection, optional): Database connection for user-managed transaction mode
trans (sqlalchemy.Transaction, optional): Transaction for user-managed transaction mode

Returns: tuple[int, int]

ignored_rows: Number of records that were not inserted (conflicts)
inserted_rows: Number of new records successfully inserted

Example:

# Auto-managed transaction (recommended)
ignored, inserted = sauk.sqlite.insert_or_ignore(
    engine=engine,
    table=users_table,
    values=[
        {'id': 1, 'name': 'Alice'},  # May be ignored if exists
        {'id': 2, 'name': 'Bob'},    # May be ignored if exists
        {'id': 3, 'name': 'Charlie'} # Will be inserted if new
    ]
)
print(f"Ignored: {ignored}, Inserted: {inserted}")

Use Cases: - Incremental data loading where duplicates should be ignored - ETL processes requiring idempotent behavior - Data synchronization from external sources

insert_or_replace¶

Performs bulk replacement operations, completely replacing existing records with new data and inserting new records.

Function Signature:

insert_or_replace()

Core Parameters:

engine (sqlalchemy.Engine): Database engine for connection management
table (sqlalchemy.Table): Target table for upsert operations
values (list[dict]): Records to insert or replace. Must include primary key values
metadata (sqlalchemy.MetaData, optional): Metadata instance for temporary table isolation
temp_table_name (str, optional): Custom temporary table name
conn (sqlalchemy.Connection, optional): Database connection for user-managed transactions
trans (sqlalchemy.Transaction, optional): Transaction for user-managed transactions

Returns: tuple[int, int]

replaced_rows: Number of existing records that were completely replaced
inserted_rows: Number of new records that were inserted

Example:

# Replace existing records entirely, insert new ones
replaced, inserted = sauk.sqlite.insert_or_replace(
    engine=engine,
    table=users_table,
    values=[
        {'id': 1, 'name': 'Alice Updated', 'email': 'alice.new@example.com'},
        {'id': 2, 'name': 'Bob Updated', 'email': 'bob.new@example.com'},
        {'id': 4, 'name': 'David', 'email': 'david@example.com'}  # New record
    ]
)
print(f"Replaced: {replaced}, Inserted: {inserted}")

Use Cases: - Full data synchronization from authoritative sources - Complete record refresh scenarios - When new data should entirely replace existing records

Warning

This operation completely overwrites existing records. All fields of conflicting records will be replaced with new data, including historical fields like timestamps.

insert_or_merge¶

Performs selective bulk merge operations, updating only specified columns of existing records while preserving others.

Function Signature:

insert_or_merge()

Core Parameters:

engine (sqlalchemy.Engine): Database engine for connection management
table (sqlalchemy.Table): Target table for merge operations
values (list[dict]): Records to merge or insert. Must include primary key values
columns (list[str]): Required. List of column names to update with new values. Other columns are preserved
metadata (sqlalchemy.MetaData, optional): Metadata instance for temporary table isolation
temp_table_name (str, optional): Custom temporary table name
conn (sqlalchemy.Connection, optional): Database connection for user-managed transactions
trans (sqlalchemy.Transaction, optional): Transaction for user-managed transactions

Returns: tuple[int, int]

updated_rows: Number of existing records that were selectively updated
inserted_rows: Number of new records that were inserted

Example:

# Update only 'email' and 'updated_at' columns, preserve other fields
updated, inserted = sauk.sqlite.insert_or_merge(
    engine=engine,
    table=users_table,
    values=[
        {'id': 1, 'name': 'Alice', 'email': 'alice.updated@example.com', 'updated_at': datetime.now()},
        {'id': 2, 'name': 'Bob', 'email': 'bob.updated@example.com', 'updated_at': datetime.now()},
        {'id': 5, 'name': 'Eve', 'email': 'eve@example.com', 'updated_at': datetime.now()}
    ],
    columns=['email', 'updated_at']  # Only these columns will be updated
)
print(f"Updated: {updated}, Inserted: {inserted}")
# Records 1-2: email and updated_at changed, name preserved
# Record 5: completely new record inserted

Use Cases: - Selective column updates (e.g., timestamps while preserving descriptions) - Incremental data synchronization - Preserving historical or audit data in non-updated columns

Note

The columns parameter is required and cannot be empty. If you need to update all columns, use insert_or_replace instead. If you want to ignore conflicts entirely, use insert_or_ignore.

Future Database Support¶

The API is designed for seamless extension to additional database systems:

Planned Support:

# PostgreSQL (coming soon)
import sqlalchemy_upsert_kit.api as sauk
sauk.postgres.insert_or_ignore(...)
sauk.postgres.insert_or_replace(...)
sauk.postgres.insert_or_merge(...)

# MySQL (coming soon)
sauk.mysql.insert_or_ignore(...)
sauk.mysql.insert_or_replace(...)
sauk.mysql.insert_or_merge(...)

Consistent Interface: All database implementations will maintain the same function signatures and behavior patterns, ensuring easy migration and multi-database support.