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.