portable DBAPI auto-increment

From: Mark Sienkiewicz <sienkiew(at)stsci(dot)edu>
To: psycopg(at)postgresql(dot)org
Subject: portable DBAPI auto-increment
Date: 2011-04-07 20:40:29
Message-ID: 4D9E213D.7070804@stsci.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: psycopg


I have a portability issue that I'm not sure what to do with: I need an
automatically unique column, but I don't see a portable way to ask for
it in my code.

For my example, I'm going to use column k as the key and v as the value,
though in real life there are another 10 or so columns.

In sqlite and mysql, I can do
c.execute("insert into foo ( v ) values ( 'xyz' )")
k = c.lastrowid

In psycopg2, I find that lastrowid is always 0, even though it is making
rows in the table with k=1, k=2, etc.

I did some searching and found that the value of lastrowid is not taken
from the autoincrementing column, but is some non-unique entity called
an OID. The recommended workaround seems to be
c.execute("insert into foo ( v ) values ( 'xyz' ) returning k")

To do this, I would need to make a special case for inserting into
tables that have an autoincrement key. For example, I might use
k = my_db_hack.insert_with_autoinc(c, " insert into foo ( v )
values ('xyz')")

where insert_with_autoinc would recognize the postgres db interface and
add the "returning" and read the results out of the cursor. It seems
kind of klunky, though.

So, my question is: Is there a more portable way to do this that I'm
not thinking of?

Secondarily, I see the the postgres documentation recommends not using
the OID in your own tables. What use is the OID? Why doesn't
c.lastrowid to return the value of the serial column, or None if there
isn't one?

Mark S.


Browse psycopg by date

  From Date Subject
Next Message Federico Di Gregorio 2011-04-08 07:51:20 Re: portable DBAPI auto-increment
Previous Message Peter Eisentraut 2011-04-07 19:46:56 client encoding name normalization in psycopg 2.4