Geek Answers Handbook

Insert only unique rows in SQLite (python)

I use cursor.executemany to insert batches of rows from CSV files into an SQLite table, some of which are expected to be duplicated based on the primary key field. When I execute the command, I predictably get an integrity error and inserted nothing.

How can I selectively insert only non-duplicated rows without having to manually filter them ahead of time? I know that in just pure Python you can just throw an error exception and skip the repeating line - is there something similar that I can implement in this case?

+7
source share
2 answers

Just use INSERT OR IGNORE to ignore duplicates.

http://sqlite.org/lang_insert.html

+17
source

One option is to simply write the loop manually with an error, instead of using executemany .

Psuedocode:

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

It is probably not as efficient as executemany , but it will catch your error without delving into the more complex SQL changes that may be associated with creating a giant INSERT SQL string.

+1
source

More articles:


All Articles