Use INSERT OR IGNORE and inspect rowcount

By Batu · English technical notes

INSERT OR IGNORE applies SQLite’s IGNORE conflict algorithm to applicable constraint violations. In this example, name is the primary key. The first parameterized insert adds "python"; the identical second insert conflicts with that key and SQLite skips that row without raising an exception.

The cursor returned by each execute() call exposes the immediate result through rowcount: it is 1 for the inserted row and 0 for the ignored duplicate. The final SELECT and assertion also inspect the stored data, so the example does not confuse a successfully executed SQL statement with a new row. Python documents Cursor.rowcount for DML statements after execution, while SQLite documents that IGNORE omits the row containing the violation. Python cursor rowcount and SQLite conflict handling describe these details.

OR IGNORE is not a general-purpose error handler: it can suppress other applicable constraint violations too. Use it only where silently skipping those violations is the intended policy, and query the result when the stored state matters. This standard-library example needs no newer Python-specific API and uses an in-memory database. AI assistance was used to draft this article.

Example

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE tags (name TEXT PRIMARY KEY)")

first = con.execute("INSERT OR IGNORE INTO tags VALUES (?)", ("python",))
duplicate = con.execute("INSERT OR IGNORE INTO tags VALUES (?)", ("python",))
names = [row[0] for row in con.execute("SELECT name FROM tags")]

assert (first.rowcount, duplicate.rowcount, names) == (1, 0, ["python"])

print(f"inserted={first.rowcount} ignored={duplicate.rowcount}")
print(names)

Expected output:

inserted=1 ignored=0
['python']