Inserting only unique rows into SQLite (python)

Question

I'm using cursor.executemany to insert batches of rows from CSV files into a SQLite table, some of which are expected to be duplicates based on the primary key field. When I execute the command, I predictably get an Integrity Error and nothing gets inserted.

How do I selectively insert only non-duplicate rows without having to manually filter them out ahead of time? I know in just pure Python you could simply create an error exception and skip the duplicate row--is there something similar I can implement in this use case?

Answer 1

Simply use INSERT OR IGNORE to ignore the duplicates.

http://sqlite.org/lang_insert.html

Answer 2

One option is simply writing out the loop manually with an error catch instead of using executemany.

Pseudocode:

for row in csvfile:
   try:
       cursor.execute('INSERT INTO X (Y) VALUES (%s)' % row[rowdatapoint])
   except IntegrityError:
       pass

Probably not as efficient as executemany, but it will catch your error short of getting into more complicated SQL changes that would possibly involve you pregenerating a giant INSERT SQL string.