Re: lastrowid not correct?

On Fri, Jul 25, 2008 at 9:50 AM, Nicholas Bastin <[email protected]> wrote:
> I searched the archives and didn't find anything about this as a known
> problem, so maybe I'm doing something wrong.  I have the following
> code:
>
>>>> import psycopg2
>>>> conn = psycopg2.connect("dbname=RS user=nbastin")
>>>> curs = conn.cursor()
>>>> curs.execute("INSERT INTO Reports (uuid, path) VALUES (%s, %s)", ("foo", "bar",))
>>>> curs.lastrowid
> 0
>
> The row gets inserted into the database properly (after I commit), but
> lastrowid never has a value other than 0.  Am I supposed to be using
> some other API for inserts such that lastrowid gets set?

The psycopg2 code looks like it is supposed to set the lastrowid
attribute to the result of PQoidValue() here.  This will be different
to SERIAL sequence ID, so is probably not what you are after.

In Storm, we do the following:

1. if PostgreSQL >= 8.2 is in use, use a query like the following:
    INSERT INTO Reports (uuid, path) VALUES (x, y) RETURNING Reports.id

This way the INSERT statement generates a result set with column
values from the row that has just been inserted.

2. For older PostgreSQL versions, issue a query like:
    SELECT currval(Reports_id_seq);

To find out what the current value of the sequence is right after the insert.


If you know you won't be using an old version of PostgreSQL, then the
first version is preferable, since it involves only a single query.

James.