[DB-SIG] Portable method of determining which column wasn't unique?

[Johannes Erdfelt]

I have a table which has multiple unique columns on it. My problem is
that when I insert a row which violates the unique restraint, I can't
figure out which column caused the problem.

I'm using MySQLdb (0.9.1 on Python 2.2.1). The example is something like
this:

create table foo (
  a int not null,
  b int not null,
  unique (a),
  unique (b)
)

>>> c.execute("insert into foo (a, b) values (1, 2)")
1L
>>> c.execute("insert into foo (a, b) values (3, 4)")
1L
>>> c.execute("insert into foo (a, b) values (1, 4)")
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/MySQLdb/cursors.py", line 61,
in execute
    r = self._query(query)
  File "/usr/lib/python2.2/site-packages/MySQLdb/cursors.py", line 169,
in _query
    rowcount = self._BaseCursor__do_query(q)
  File "/usr/lib/python2.2/site-packages/MySQLdb/cursors.py", line 112,
in __do_query
    db.query(q)
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry '4' for key
1")

Now, I'd like to figure out which columns were the problem, so I can
report back to the user what they need to fix. I'd like to do this in a
portable way, but I'll settle for something specific to MySQLdb if need
be.

Ignoring the fact that the exception and error code are specific to
MySQLdb (is there any work on standardizing any of these?), I can't
retrieve much information from the exception other than parsing the
string.

The next issue is that I don't know what key "1" is programmatically.

I've done some hacking and I've figured out that I can combine the data
from cursor._result._describe() (cursor.description) and
cursor._result._field_flags() (hacked the source to make it
cursor.field_flags) to determine which columns have a unique constraint
or are a primary key.

I could then do a query on those columns with the values I tried to
change to see if any others exist. I'd need to use locks to avoid races
which would make this slow.

Is there a better way for MySQLdb? For DB API?

Or am I just expecting too much? :)

JE