How to get Inserted or selected row id in postgres using python

Question

My postgres query is:

query = """INSERT INTO statustable(value) SELECT '%s' 
                WHERE NOT EXISTS (SELECT id, value FROM statustable
                WHERE value = '%s') RETURNING id""" %  (status, status)
    cursor_postgres.execute(query)
    conn_postgres.commit()
    statusId = cursor_postgres.fetchone()[0]
    print "statusId" + str(statusId)

I need to get the freshly inserted status value id if it doesnt exist, or select its id if already exist. RETURNING id choked this query entirely so I had to remove it to atleast get the selective insertion working.

Any clues how to get the statusId here? In another instance I am doing an Upsert.(Insert if not exist, update otherwise) Here again, I need the inserted or updated row id. (No, I am not using stored procedures, if that was your first question...)

Thanks in advance

Answer 1

I can't say I fully understand your motivation for insisting on a single query. I think your best bet is to have two simple queries:

1. SELECT id FROM statustable WHERE value = '%s'. This gives you the id if the entry exists, in which case skip step 2;
2. INSERT INTO statustable(value) VALUES('%s') RETURNING id. This'll give you the id of the newly created entry.

Lastly -- although I haven't verified whether this is a problem -- fetchone() across a commit looks slightly suspect.