Continue loading after IntegrityError

Question

In python, I populate the SQLITE database with importmany, so I can import tens of thousands of rows of data right away. My data is contained in a list of tuples. I had a database configured with primary keys where I wanted them.

The problem I ran into was that primary key errors caused IntegrityError. If I handle the exception, my script stops importing in the primary key conflict.
try:

try: self.curs.executemany("INSERT into towers values (NULL,?,?,?,?)",self.insertList) except IntegrityError: print "Primary key error" conn.commit() 

So my questions are: in python using importmany I can:

1. Capturing values ​​that violate the primary key?
2. Continue to download data after receiving my primary errors.

I understand why it does not continue to load, because after the exception, I commit the data in the database. I do not know how to continue where I left off. Unforutnley I can not copy and paste all the code on this network, any help would be greatly appreciated. Right now I don’t have PCs that work like work ...

Answer 1

To answer (2), first, if you want to continue loading after receiving the error, this is a simple fix on the SQL side:

 INSERT OR IGNORE INTO towers VALUES (NULL,?,?,?,?) 

This will successfully insert all rows that have no violations, and gracefully ignore the conflicts. However, please note that the IGNORE clause will still not be executed in case of foreign key violations.

Another version of the conflict resolution clause in your case: INSERT OR REPLACE INTO ... I highly recommend SQLite docs for more information on conflicts and their resolution.

As far as I know, you cannot do both (1) and (2) at the same time in an efficient way. You could create a trigger that will fire before inserts that can capture conflicting lines, but this will impose a lot of unnecessary overhead on all your inserts. (Someone please let me know if you can do this in a more reasonable way.) Therefore, I would recommend that you consider whether you really need to fix the values ​​of conflicting strings or whether you need to redesign your schema if possible / applicable.

Answer 2

You can use lastrowid to get the point where you left off:

http://docs.python.org/library/sqlite3.html#sqlite3.Cursor.lastrowid

If you use it, you cannot use executemany .

Answer 3

Use a for loop to iterate through the list and use execute instead of executeemany. Surround the for loop with try and continue after the exception. Something like that:

 for it in self.insertList: try: self.curs.execute("INSERT into towers values (NULL,?,?,?,?)",it) except IntegrityError: #here you could insert the itens that were rejected in a temporary table #without constraints for later use (question 1) pass conn.commit() 

You can even count how many items in the list have been inserted.