Hi,
I have a portability issue that I'm not sure what to do with: I need an
automatically unique column, but I don't see a portable way to ask for
it in my code.
For my example, I'm going to use column k as the key and v as the value,
though in real life there are another 10 or so columns.
In sqlite and mysql, I can do
c.execute("insert into foo ( v ) values ( 'xyz' )")
k = c.lastrowid
In psycopg2, I find that lastrowid is always 0, even though it is making
rows in the table with k=1, k=2, etc.
I did some searching and found that the value of lastrowid is not taken
from the autoincrementing column, but is some non-unique entity called
an OID. The recommended workaround seems to be
c.execute("insert into foo ( v ) values ( 'xyz' ) returning k")
To do this, I would need to make a special case for inserting into
tables that have an autoincrement key. For example, I might use
k = my_db_hack.insert_with_autoinc(c, " insert into foo ( v )
values ('xyz')")
where insert_with_autoinc would recognize the postgres db interface and
add the "returning" and read the results out of the cursor. It seems
kind of klunky, though.
So, my question is: Is there a more portable way to do this that I'm
not thinking of?
Secondarily, I see the the postgres documentation recommends not using
the OID in your own tables. What use is the OID? Why doesn't
c.lastrowid to return the value of the serial column, or None if there
isn't one?
Mark S.
