Menu
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()?
325
If you want SQL to IGNORE that error and continue adding other records , then do this : INSERT or IGNORE into tablename VALUES (value1,value2 , so on ); If you want to replace the values in the table whenever the entry already exists , then do this: INSERT or REPLACE into tablename VALUES (value1,value2 , so on );
In SQLite, Not Null Constraint is used to indicates that the column will not allow storing NULL values. Generally, in SQLite by default columns will allow NULL values in case if you have requirement like not to allow NULL values in column means then we need to add NOT NULL constraint on column.
Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With