Pysqlite IntegrityError: отличить "NOT NULL" от нарушения "UNIQUE"

В pysqlite, нарушающее ограничение NOT NULL или UNIQUE, также вызывает IntegrityError. К сожалению, этот тип исключения не содержит код ошибки, а только сообщение.

Итак, допустим, я хочу игнорировать нарушения уникального ограничения, потому что я знаю, что это безопасно для данных, но должны указываться значения Null в ключевых столбцах.

Я придумал следующее решение:

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()

Однако разбор сообщения об ошибке кажется неправильным и может быть ненадежным. Есть ли лучший способ сделать это, возможно, даже используя con.executemany()?

Ответ 1

Вот что я сделал:

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()

Подобно этому, пустые значения попадают "вручную" перед выполнением операции БД. Если во время execute возникает ошибка (например, нарушение ограничения UNIQUE), запись пропускается. Обратите внимание, что INSERT OR IGNORE не означает игнорирование ограничения единственности, а игнорирование (т.е. пропущение) строки ввода.

Недостатком этого решения является то, что проверка пустых значений выполняется дважды. Я думаю, это не так уж плохо, потому что это, по-видимому, довольно дешевая операция. Я думаю, он по-прежнему чище, чем анализ сообщения об ошибке, и, вероятно, более надежный для изменений (например, обновление pysqlite, которое может изменить некоторые детали сообщения об ошибке).

Кредиты: Идея возникла из обсуждения с Лутцем. Он был независимо предложен Мартином.

Ответ 2

Более элегантное решение - полностью использовать функциональность SQL (ite). Указав предложение конфликта для первичного ключа (ON CONFLICT IGNORE), желаемое поведение уже достигнуто:

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)''')

Таким образом, дублирующиеся строки (которые нарушают ограничение уникальности первичного ключа) молча пропускаются, а значения Null вызывают прерывание (в результате возникает исключение sqlite3). Все это достигается без предварительной фильтрации данных для значений Null/None или с помощью сообщений об ошибках API sqlite3. Теперь мы можем просто называть con.executemany() без дальнейших церемоний:

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

Ответ 3

Ниже приведен рабочий код:

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

Тест:

>>> 
There is a NULL value
>>>

Второй результат теста:

>>> 
There is unique violation
>>>

Надежды, могут помочь вам.