Using psycopg2 to interact with Anvil Data Tables directly, I noticed that in the middle of a transaction using the RETURNING statement after an INSERT seems to return the rows it inserted, but not an _id for the row. Take for example the code below:
with psycopg2.connect(conn_string) as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
cur.execute(
"""
INSERT INTO table_987654 (col_1, col_2) VALUES
('a', 'b')
RETURNING *;
"""
)
print(list(cur.fetchone()))
The output looks something like this:
[None, 'a', 'b']
Where None is usually the place where you get a big row id like 1723724.
If you run a SELECT statement immediately after (even without a commit), the returned row has an _id like you’d expect. Only thing is you can’t rely on what amounts to the row’s primary key to find it.
Anyone have any thoughts or experience with this kind of thing? I have done a similar test with psycopg2 on my localhost PostgreSQL server and RETURNING * will return the whole row which includes the next valid primary key, which is the desired result.