upsertdb

Insert-or-update ("upsert") rows into a SQL table over any DB-API connection — dialect-aware, zero dependencies.

Give upsertdb a DB-API 2.0 connection and a list of dict rows and it writes them so that existing rows (by key) are updated and new rows inserted — using each database's native upsert syntax. No ORM, no dependencies; bring your own driver.

Dialect	Strategy
SQLite / PostgreSQL	INSERT … ON CONFLICT (keys) DO UPDATE
MySQL / MariaDB	INSERT … ON DUPLICATE KEY UPDATE
SQL Server	MERGE (single-row, or staging-table via build_merge_statement)

Install

pip install -e ".[test]"

Usage

import sqlite3
from upsertdb import upsert

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, ts INTEGER)")

upsert(conn, "events",
       rows=[{"id": 1, "name": "launch", "ts": 100}],
       keys=["id"])

# id 1 already exists -> updated; id 2 is new -> inserted
upsert(conn, "events",
       rows=[{"id": 1, "name": "launch v2", "ts": 200},
             {"id": 2, "name": "signup",    "ts": 210}],
       keys=["id"])

The dialect is auto-detected from the driver (sqlite3, psycopg2, pyodbc, …) or set explicitly with dialect="postgresql". Composite keys are supported.

Just the SQL

Every statement builder is a pure function you can use on its own:

from upsertdb import build_upsert, build_merge_statement

build_upsert("postgresql", "events", ["id", "name"], keys=["id"])
# INSERT INTO "events" ("id", "name") VALUES (%s, %s)
# ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name"

build_merge_statement("dbo", "events", keys=["id"], values=["name"], delete=True)  # T-SQL MERGE

API

Object	Purpose
upsert(conn, table, rows, keys, dialect=…, schema=…)	Execute an upsert
build_upsert(dialect, table, columns, keys)	Build the upsert SQL
build_merge_statement(schema, table, keys, values, delete)	T-SQL MERGE
detect_dialect(conn)	Infer dialect from the driver

Note on method

upsert builds one statement per call and runs it with executemany, so every row goes through the same prepared statement. The dialect picks the syntax: SQLite and PostgreSQL use INSERT … ON CONFLICT (keys) DO UPDATE, MySQL and MariaDB use INSERT … ON DUPLICATE KEY UPDATE, SQL Server uses a single-row MERGE over a VALUES source. Only the non-key columns are written on a match; when a row carries nothing but its key columns, SQLite and PostgreSQL fall back to DO NOTHING. The dialect is read from the driver's module name (sqlite3, psycopg2, pyodbc, …); pass dialect= for a driver it doesn't know, or it defaults to SQLite.

The database does the matching, so keys must already be a unique or primary key on the table. Without that constraint PostgreSQL and SQLite raise on the ON CONFLICT target and MySQL inserts a duplicate. upsert neither creates nor checks the constraint. Every row in a call has to share the same columns: the column list comes from the first row and the statement is reused for the rest. Values pass straight through as DB-API parameters, so the driver and database do the typing. For large batches a set-based path is faster, staging the rows and running one MERGE or using the database's bulk loader; build_merge_statement builds the staging-table form for SQL Server.

R version

The original R implementation (SQL Server MERGE via DBI) lives at HenrikVarmer/upsertR.

License

MIT © Henrik Varmer