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

Discussion in 'Python' started by Roy Smith, Mar 29, 2013.

  1. Roy Smith

    Roy Smith Guest

    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?

    ---
    Roy Smith
    Roy Smith, Mar 29, 2013
    #1
    1. Advertising

  2. Roy Smith

    Miki Tebeka Guest

    > 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))
    Miki Tebeka, Mar 30, 2013
    #2
    1. Advertising

  3. Roy Smith

    Roy Smith Guest

    In article <>,
    Miki Tebeka <> wrote:

    > > 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))


    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.
    Roy Smith, Mar 30, 2013
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. rantingrick
    Replies:
    44
    Views:
    1,165
    Peter Pearson
    Jul 13, 2010
  2. Roy Smith
    Replies:
    24
    Views:
    902
    Martin Gregorie
    Oct 17, 2010
  3. Roy Smith
    Replies:
    1
    Views:
    133
    Dennis Lee Bieber
    Mar 1, 2013
  4. Dave Angel
    Replies:
    0
    Views:
    113
    Dave Angel
    Mar 29, 2013
  5. Dennis Lee Bieber
    Replies:
    23
    Views:
    354
    Roy Smith
    Mar 30, 2013
Loading...

Share This Page