Automatic rollback on error, duplicate key?

I'm new to both python and the pypgsql module, but have been using
Oracle since 1996 and Postgres for the last 2 years. I've used a
number of interfaces to both including low-level C interfaces. I've
just started a project using python and pypgsql and I've run into
something I consider odd.

I have a table defined as:

create table items (
  gid        int8 UNIQUE,
  name       varchar(256),
  descr      varchar(1024)
);

Over time I'll be inserting rows into the items table, and I may have
duplicates.  I expect the insert to fail, and I don't care, I just
want to continue inserting other items, and more importantly, I want
previous inserts to this table and other tables to not disappear.

When I code this in a way that I thought should work, it appears that
the pypgsql module is doing an automatic rollback when I insert a
duplicate:

            try:
                cur.execute(sqlStmt)
            except PgSQL.Error, msg:
                print "Insert from pg_database failed\n%s" % msg
                print sqlStmt

It seems to me that, since I am trapping the error, it should be up to
me to decide if a rollback() should be performed. Why is the module
deciding for me? Or have I misunderstood what is going on?

I took a look at the DB API 2.0 spec at
http://www.python.org/peps/pep-0249.html but I can't see where this
behavior is defined (or any detailed error handling behavior,
actually).

Can someone point me in the right direction here? I basically want to
do:

            open a new transaction

            insert row into table 1

            insert multiple rows into table 2
               these inserts may be duplicates

            insert multiple rows into table 3, each of which is tied
            back to table 1 by an ID.

            commit the transaction

For all statements except the inserts into table 2, if I get an error
I would like to rollback the transaction.

I'd might as well also ask how to detect different error codes. The
inserts into table 2 may fail for other reasons, and in those cases I
should do a rollback. I only want to continue if the error is trying
to insert a duplicate key. How am I supposed to do that, perform a
regex on the error message string? Yuck. Are there error codes in the
cursor or connection to look at?

Thanks!

Bob




-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf