How to find bad row with db api executemany()?

[Roy Smith]

I'm inserting a gazillion rows into a MySQL database using MySQLdb and cursor.executemany() for efficiency. Every once in a while, I get a row which violates some kind of database constraint and raises Error.

I can catch the exception, but don't see any way to tell which row caused the problem. Is this information obtainable, short of retrying each row one by one?

 

[Miki Tebeka]

I can catch the exception, but don't see any way to tell which row caused the problem. Is this information obtainable, short of retrying each row one by one?
One way to debug this is to wrap the iterable passed to executemany with one that remembers the last line. Something like:

class LastIterator(object):
def __init__(self, coll):
self.it = iter(coll)
self.last = None

def __iter__(self):
return self

def next(self):
self.last = next(self.it)
return self.last

...
li = ListIterator(items)
try:
cursor.executemany(sql, li)
except SQLError, e:
print('Error: {}, row was {}'.format(e, li.last))

 

[Roy Smith]

I can catch the exception, but don't see any way to tell which row caused
the problem. Is this information obtainable, short of retrying each row
one by one?

One way to debug this is to wrap the iterable passed to executemany with one
that remembers the last line. Something like:

class LastIterator(object):
def __init__(self, coll):
self.it = iter(coll)
self.last = None

def __iter__(self):
return self

def next(self):
self.last = next(self.it)
return self.last

...
li = ListIterator(items)
try:
cursor.executemany(sql, li)
except SQLError, e:
print('Error: {}, row was {}'.format(e, li.last))[/QUOTE]

This assumes that the exception is raised synchronously with iterating
over the input. The whole idea of executemany() is to batch up rows and
send them to the database as a single unit, so this would almost
certainly not be a good assumption.