Re: portable DBAPI auto-increment

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.