pygresql - insert and return serial

Question

I'm using PyGreSQL to access my DB. In the use-case I'm currently working on; I am trying to insert a record into a table and return the last rowid... aka the value that the DB created for my ID field:

create table job_runners (
    id           SERIAL PRIMARY KEY,
    hostname     varchar(100) not null,
    is_available boolean default FALSE
    );

sql = "insert into job_runners (hostname) values ('localhost')"

When I used the db.insert(), which made the most sense, I received an "AttributeError". And when I tried db.query(sql) I get nothing but an OID.

Q: Using PyGreSQL what is the best way to insert records and return the value of the ID field without doing any additional reads or queries?

Answer 1

INSERT INTO job_runners
    (hostname,is_available) VALUES ('localhost',true)
    RETURNING id

That said, I have no idea about pygresql, but by what you've already written, I guess it's db.query() that you want to use here.

Answer 2

The documentation in PyGreSQL says that if you call dbconn.query() with and insert/update statement that it will return the OID. It goes on to say something about lists of OIDs when there are multiple rows involved.

First of all; I found that the OID features did not work. I suppose knowing the version numbers of the libs and tools would have helped, however, I was not trying to return the OID.

Finally; by appending "returning id", as suggested by @hacker, pygresql simply did the right thing and returned a record-set with the ID in the resulting dictionary (see code below).

sql = "insert into job_runners (hostname) values ('localhost') returning id"
rv = dbconn.query(sql)
id = rv.dictresult()[0]['id']

Answer 3

Assuming you have a cursor object cur:

cur.execute("INSERT INTO job_runners (hostname) VALUES (%(hostname)s) RETURNING id",
            {'hostname': 'localhost'})
id = cur.fetchone()[0]

This ensures PyGreSQL correctly escapes the input string, preventing SQL injection.