insert_or_merge

class sqlalchemy_upsert_kit.sqlite.insert_or_merge.InsertOrMergeExecutor(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table, values: list[dict[str, Any]], metadata: sqlalchemy.sql.schema.MetaData | None, temp_table_name: str | None, conn: sqlalchemy.engine.base.Connection | None, trans: sqlalchemy.engine.base.Transaction | None, columns: list[str] | None, _raise_on_temp_table_create: bool, _raise_on_temp_data_insert: bool, _raise_on_target_delete: bool, _raise_on_target_insert: bool, _raise_on_temp_table_drop: bool, _raise_on_merge_update: bool, _ignored_rows: int = 0, _replaced_rows: int = 0, _updated_rows: int = 0, _inserted_rows: int = 0, _temp_table_created: bool = False)

apply_strategy(conn: Connection, trans: Transaction)

Apply merge strategy: update existing records with selected columns, insert new records.

This implementation follows the merge algorithm: 1. Create temp table t3 with merged data (existing records with selective updates) 2. Delete conflicting records from target table t1 3. Insert both updated records (from t3) and new records (from t2) into t1

sqlalchemy_upsert_kit.sqlite.insert_or_merge.insert_or_merge(engine: Engine, table: Table, values: list[dict[str, Any]], columns: list[str], metadata: MetaData | None = None, temp_table_name: str | None = None, conn: Connection | None = None, trans: Transaction | None = None, _raise_on_temp_table_create: bool = False, _raise_on_temp_data_insert: bool = False, _raise_on_target_delete: bool = False, _raise_on_target_insert: bool = False, _raise_on_temp_table_drop: bool = False, _raise_on_merge_update: bool = False) → tuple[int, int]

Perform high-performance bulk INSERT-OR-MERGE operation using temporary table.

This function performs bulk merge operations: updates specific columns of existing records while preserving other columns, and inserts records that don’t exist. This is ideal for incremental data updates where only certain fields need updating.

Algorithm:

1. Creates temporary table (t2) and loads all candidate data

2. Creates merge temporary table (t3) with selective column updates

3. Generates merged records by combining existing data with new values for specified columns

4. Deletes conflicting records from target table (t1)

5. Inserts merged records and new records into target table

6. Cleans up temporary resources

This approach is ideal for:

• Selective column updates (e.g., updating timestamps while preserving descriptions)

• Incremental data synchronization

• Preserving historical data in non-updated columns

Transaction Management:

This function supports both auto-managed and user-managed transaction modes. See the module-level documentation for detailed explanations of each mode.

Parameters:

• engine – SQLAlchemy engine for database connection

• table – Target table for upsert operation

• values – Records to merge. Must include primary key values for conflict detection.

• columns – List of columns to update with new values. Other columns will remain unchanged for existing records. This is required, otherwise user should use insert_or_ignore or insert_or_replace strategy.

• metadata – Optional metadata instance for temporary table isolation. If None, a new MetaData instance is created for clean separation.

• temp_table_name – Optional custom name for temporary table. If None, generates unique name with timestamp to avoid conflicts.

• conn – Optional database connection for user-managed transaction mode. Must be provided together with trans parameter.

• trans – Optional transaction for user-managed transaction mode. Must be provided together with conn parameter.

Returns:

Tuple of (updated_rows, inserted_rows): - updated_rows: Number of existing records that were updated with new column values - inserted_rows: Number of new records that were inserted

Raises:

• ValueError – When conn and trans parameters are provided inconsistently or when columns parameter is empty

• UpsertTestError – When testing flags are enabled and corresponding operations fail

Examples:

Auto-managed transaction (default mode):

# Function manages its own transaction
updated, inserted = merge(engine, users_table, new_data, columns=["update_at"])

User-managed transaction mode:

# Operation is part of larger transaction
with engine.connect() as conn:
    with conn.begin() as trans:
        # Other operations...
        updated, inserted = merge(
            engine, users_table, new_data, columns=["update_at"],
            conn=conn, trans=trans
        )
        # More operations...

Selective column update example:

# Target table has records with id=1,2,3
new_data = [
    {'id': 2, 'name': 'Bob', 'update_at': '2024-01-02'},    # Exists - will be merged
    {'id': 4, 'name': 'Charlie', 'update_at': '2024-01-02'}, # New - will be inserted
]
updated, inserted = merge(engine, users_table, new_data, columns=["update_at"])
# Result: updated=1, inserted=1
# Record id=2 will have new update_at but original name preserved

Note

Parameters prefixed with _raise_on_ are exclusively for testing error handling and cleanup behavior. Never use these in production code.

Warning

The columns parameter must not be empty. If you want to update all columns, use insert_or_replace instead. If you want to ignore conflicts, use insert_or_ignore instead.