pysqlite's IntegrityError: distinguish 'NOT NULL' from 'UNIQUE' violation

Question

In pysqlite, violating a NOT NULL or a UNIQUE constraint likewise raise an IntegrityError. Unfortunately, this Exception type does not provide an error code, but only a message.

So, let's say I want to ignore unique-constraint violations, because I know this is safe on the given data, but Null values in the key columns should be reported.

I've come up with the following solution:

con = sqlite3.connect(':MEMORY:')
con.execute('''CREATE TABLE ABCD (A TEXT NOT NULL,
                                  B TEXT NOT NULL,
                                  C TEXT NOT NULL,
                                  D TEXT NOT NULL,
                                  PRIMARY KEY (A, B))''')
with con:
    for a, b, c, d in inputs:
        try:
            con.execute('INSERT INTO ABCD VALUES (?, ?, ?, ?)',
                        (a, b, c, d))
        except sqlite3.IntegrityError as e:
            # Skip 'not unique' errors, but raise others.
            if not e.message.endswith('not unique'):
                raise
con.close()

However, parsing the error message seems wrong and might be unreliable. Is there a better way to do this, maybe even using con.executemany()?

Cassilda

I don't have an answer to your question. But I wonder what use a constraint is if it can be safely ignored? Why don't just drop it? In your example code (A, B) is you PK. I think that this constraint must not be ignored. What would be a real world use case? – Muricate

The unique constraint is not ignored, the input lines are. The input is expected to contain duplicates on those fields, and I'm skipping repetitions silently. – Cassilda

You could filter the input and remove all duplicates before passing it ti SQLite. – Muricate

Yes, sure, but then I'd have to build an extra index of the primary keys first, before inserting them to the db, which was going to index them anyway. – Cassilda

It is much easier to just pre-filter your data on NULL values in the key column. Ignoring the non-unique keys is as easy as using INSERT OR IGNORE then. – Abridgment

Looking at the pysqlite and sqlite3 source code, indicates that the return error code you are seeking is #define SQLITE_CONSTRAINT 19 and is the same for all constraint violations regardless of constraint type.This along with SQLITE_MISMATCH gets mapped to a pysqlite IntegrityException.Parsing the error message associated with that exception seems like the only way to distinguish the cases without adding new functionality to both the binding and sqlite3. – Infelicity

Thanks, @MartijnPieters, that's exactly what I ended up doing. – Cassilda

Answer 1

This is what I ended up doing:

con = sqlite3.connect(':MEMORY:')
con.execute('''CREATE TABLE ABCD (A TEXT NOT NULL,
                                  B TEXT NOT NULL,
                                  C TEXT NOT NULL,
                                  D TEXT NOT NULL,
                                  PRIMARY KEY (A, B))''')
with con:
    for a, b, c, d in inputs:
        if any(elem is None for elem in (a, b, c, d)):
            raise ValueError('Fields must not be empty.')
        con.execute('INSERT OR IGNORE INTO ABCD VALUES (?, ?, ?, ?)',
                    (a, b, c, d))
con.close()

Like this, empty values are caught "manually" before executing the DB operation. If any error occurs during execute (such as a violation of the UNIQUE constraint), the entry is skipped. Please note that INSERT OR IGNORE does not mean ignoring the uniqueness constraint, but rather ignoring (ie. skipping) an input line.

The downside of this solution is that the check for empty values is done twice. I guess this is not too bad however, since it is presumably rather cheap an operation. I think, it is still cleaner than parsing the error message, and probably more robust to changes (such as a pysqlite update, which might change some detail in the error message).

Credits: The idea emerged from discussion with Lutz. It was independently also suggested by Martijn.

Cassilda

Answer 2

A more elegant solution is to rely on SQL(ite) functionality entirely. By specifying a conflict clause for the primary key (ON CONFLICT IGNORE), the desired behaviour is already achieved:

con = sqlite3.connect(':memory:')
con.execute('''CREATE TABLE ABCD (A TEXT NOT NULL,
                                  B TEXT NOT NULL,
                                  C TEXT NOT NULL,
                                  D TEXT NOT NULL,
                                  PRIMARY KEY (A, B) ON CONFLICT IGNORE)''')

Thus, duplicate lines (which violate the uniqueness constraint of the primary key) are silently skipped, while Null values cause an abort (resulting in an sqlite3 exception). This is all achieved without pre-filtering the data for Null/None values or fiddling with error messages of the sqlite3 API. We can now simply call con.executemany(), without further ado:

with con:
    con.executemany('INSERT INTO ABCD VALUES (?, ?, ?, ?)', inputs)

Cassilda

Answer 3

Below is a working code:

import sqlite3

con = sqlite3.connect(':memory:')
con.execute('''CREATE TABLE ABCD (A TEXT NOT NULL,
                                  B TEXT NOT NULL,
                                  C TEXT NOT NULL,
                                  D TEXT NOT NULL,
                                  PRIMARY KEY (A, B));''')

inputs = [('cow', 'pig', 'cat', 'dog'), ('cow', 'pig', 'quail', 'turkey')]
with con:
    for a, b, c, d in inputs:
        try:
            con.execute('INSERT INTO ABCD VALUES (?, ?, ?, ?);',
                        (a, b, c, d))
        except sqlite3.IntegrityError as e:
            if 'not null' in e.args[0].lower():
                print('There is a NULL value')
            elif 'unique constraint' in e.args[0].lower():
                print('There is unique violation')
            else:
                raise

Test:

>>> 
There is a NULL value
>>> 

Second Test result:

>>> 
There is unique violation
>>> 

Hopes, can help you.

Flora

The question asks for a way that is better than parsing the error message. – Envelope

This code does not ignore UNIQUE constraint violations. – Envelope

@Envelope I've edit my answer to catch NULL and UNIQUE violation. – Flora