Re: portable DBAPI auto-increment

On Thu, Apr 7, 2011 at 9:40 PM, Mark Sienkiewicz <sienkiew(at)stsci(dot)edu> wrote:

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

I would say no, "returning" is the best option, "select
currval(seq_name)" a second -vastly inferior- one.

PostgreSQL doesn't give any special value to a field of type serial,
nor to the primary key of a table. It's not possible for psycopg to
add back such value without a) parsing the query, b) querying the
system catalog, 3) add policies of its own on top of the database and
4) rewrite the query. None of these things are likely to happen in
psycopg, albeit you may find some of those in some higher level tool
using psycopg as its driver.

Looks like you are hoping to write "portable SQL queries". I don't
believe this approach will bring you very far: if you want to write
portable _queries_ you need a tool that generates the _SQL_ for you,
or you will have problems in any query but the simplest ones. Hard?
Yes, it is. Take a look at SQLAlchemy if you want portable queries, or
be prepared to work at a level higher than the SQL.

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

The use of the OID is mostly historical and currently only limited to
the system catalog for the limitations you have probably read in the
docs.

c.lastrowid is not set to the value of the serial column because
PostgreSQL doesn't tell it. psycopg doesn't even know the query you
have sent is an insert, let alone that this insert was performed on a
table that happened to have exactly one serial column (that by the way
is not even a type: it's only a value for the default and its type may
actually be anything).

-- Daniele