Python sqlite3 what happens if executemany() encounters an integrity error partway through?

user_5591958 0
Viewed 302

If I run the SQL command

INSERT INTO my_table VALUES (?);

with the constraint

CREATE my_table(
    user_name VARCHAR(255) PRIMARY KEY
);

Using sqlite3's executemany() command on a list ['a', 'b', 'c', 'd', 'e', 'f', 'a', 'b', 'g', 'h', 'i'], I'm going to get a sqlite3.IntegrityError.

I have a few questions about the behavior of executemany() when it encounters this error, and I have been unable to find any documentation on the behavior.

1) Are the values inserted prior to the exception always intact?

2) Are there any cases in which values after the exception occurs might be inserted?

3) Is there any way to determine which value(s) cause an exception? (The best I can think of is to wrap the list in a generator to track the state, log the problem entries, and retry on the rest of the generator until the entire list is consumed.)

executemany python-3.x sqlite sql python
1 Answers

The test script below provides some evidence:

  1. The values inserted prior to the integrity constrain seem to be intact
  2. The values to be inserted after the exception are not inserted
  3. Not that I have found - I have used something similar to the solution you suggest.

Note that you can use a transaction if you want to prevent any values being inserted if an IntegrityError occurs. Example below. See also here for more.

import sqlite3
conn = sqlite3.connect('del.db')
tuples = ('a','b','c','a','d')

conn.execute('create table test (col text primary key)')

conn.commit()
c = conn.cursor()
try:
    c.executemany('insert into test  values (?)', tuples)
except sqlite3.IntegrityError as exc:
    print(exc)

c.close()

results = conn.execute('select * from test')
results.fetchall()

results in the following output

UNIQUE constraint failed: test.col
[('a',), ('b',), ('c',)]

Whereas if you use a transaction:

c = conn.cursor()
c.execute('begin')
try:
    c.executemany('insert into test  values (?)', tuples)
except sqlite3.IntegrityError as exc:
    c.execute('rollback')

No data is entered.

user_17791280
Related