PyPGSQL - OID

[Manuel Huesser]

Der Python DB API supports the method oidValue on a cursor instance.
On PostgreSQL I get the internal used oid from each row, but I need
the id from the primary key. Is there a way?

Thanks

[Michael Fuhr]

Manuel Huesser <sy********@gmx.net> writes:

Der Python DB API supports the method oidValue on a cursor instance.
On PostgreSQL I get the internal used oid from each row, but I need
the id from the primary key. Is there a way?

If you're setting the primary key from a sequence then you can
use CURRVAL('sequence_name'). For example, suppose you have
this table:

CREATE TABLE person (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

PostgreSQL will create a sequence named person_id_seq that will
be used to populate the id field (the primary key). After an
INSERT, you can use CURRVAL('person_id_seq') to refer to the
primary key:

curs.execute("INSERT INTO person (name) VALUES (%s)", ["Guido"])
curs.execute("SELECT * FROM person WHERE id = CURRVAL('person_id_seq')")
print curs.fetchone()

CURRVAL() returns the most recently used sequence number in the
current connection, so you don't need to worry about the value
being incremented by other connections before you get a chance
to query it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/