[DB-SIG] Which db mapping tool?

[Gerhard Häring]

* Federico Di Gregorio <fog@initd.org> [2002-07-16 12:09 +0200]:
> psycopg 1.0.9 has lastoid(), 1.1 series lastrowid()

The 1.1 name is perhaps slightly misleading, as it still returns the
OID.

> don't know if this is what you need but you *can* use the oid returned
> by lastrowid() to access a newly inserted row.

Of course, but it's probably inefficient, as you'll need a seperate
select like SELECT id FROM mytable WHERE oid={value_of_oid}. I suspect
that this kind of SELECT is slow.

> and if you want to use a
> "serial" type, yes, you need a two step process to get the value but i
> don't see any problem with:
> 
>     curs.execute("INSERT INTO ...")
>     curs.execute("SELECT id FROM ... WHERE oid = %d", [curs.lastrowid()])

This returns the OID, not the value of the SERIAL primary key field.

> if you use psycopg 1.1 you can even do:
> 
>     curs.execute("INSERT INTO ... ; SELECT curval(...)") 
>     curs.fetchone()

Yep, that's what I wanted. It would require changes to MiddleKit to add
a sequence at table creation time and set the default value of the
primary key accordingly. And you'll also have to know the name of the
sequence, which IIRC isn't that easy in the MiddleKit code, either.
Certainly doable, but more effort than I was willing to put in just to
try out MiddleKit.

Currently there seem to be some PostgreSQL patches floating around on
its mailing list, but I haven't checked these out.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))