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

Re: portable DBAPI auto-increment

From: Mark Sienkiewicz <sienkiew(at)stsci(dot)edu>
To:
Cc: psycopg(at)postgresql(dot)org
Subject: Re: portable DBAPI auto-increment
Date: 2011-04-08 16:23:30
Message-ID: 4D9F3682.5020601@stsci.edu (view raw or flat)
Thread:
Lists: psycopg
Karsten Hilbert wrote:
>> In sqlite and mysql, I can do
>>    c.execute("insert into foo ( v ) values ( 'xyz' )")
>>    k = c.lastrowid
>>     
>
> .lastrowid is an implementation detail depending on the
> database to keep a unique column on your rows behind your back.
>   

Yes.  From the available documentation, this looks like the obvious 
purpose for .lastrowid existing at all.

But,

Joe Abbate wrote:
 >
 > As I recall, ROWID --which is only defined tautologically in PEP-249 as
 > "the 'Row ID' column"-- is an Oracle feature. There it's a pseudo column
 > that is normally hidden (does not appear in SELECT *).
 > ...
 >
 > If MySQL and SQLite have interpreted ROWID as the value of an
 > auto-increment column, that makes using it non-standard and therefore by
 > definition non-portable.


That is totally non-obvious from the PEP, but _extremely_ useful to know.

( And, yes, both sqlite3 and MySQLdb return the value of the 
autoincrement column in lastrowid. )

So,

Karsten Hilbert wrote:
> Maybe second thougth needs to be given to whether .lastrowid
> is the right tool for the job.
>   


Evidently, you are right.  Currently, I expect the database to create a 
unique identifier for the record on insert.  Instead, I need to make a 
separate UID generator and insert the record with a previously generated 
UID.


Federico Di Gregorio wrote:
> Declare k as "serial" and then do the following:
>
> c.execute("INSERT INTO foo (v) VALUES ('xyz') RETURNING k")
> k = c.fetchone()[0]
>   

That basic code could be the core of the UID generation.  It would also 
need to deal with possibly non-unique numbers after it wraps (in 
postgres, the value after 2147483647 is 1), but I probably have at least 
5 years to figure that out.

(For comparison, mysql uses an unsigned 64 bit value for auto increment 
and chokes when it runs out.  sqlite wraps, but it automatically finds a 
new key value that is not used.)


Thanks to all of you for the help!

Mark S.


In response to

Responses

psycopg by date

Next:From: Daniele VarrazzoDate: 2011-04-08 16:42:57
Subject: Re: portable DBAPI auto-increment
Previous:From: Federico Di GregorioDate: 2011-04-08 13:14:00
Subject: Re: client encoding name normalization in psycopg 2.4

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