Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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.


psycopg by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group