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

It strikes me that the quickest way to do this is probably to do three queries (within a transaction, if need be). 1) "SELECT id FROM statustable WHERE value = '%s' " %(status) 2) If no rows returned, "INSERT INTO statustable (value), '%s'" %(status). 3) "SELECT curval(pk_seq)" whatever pk_seq is called as the sequence for your primary key id. But since this isn't what you asked for, it's not in an answer. — ed.
– ed., Commented Sep 19, 2011 at 16:57
thanks for the reply @ed. Infact 2 & 3 can be merged by INSERT INTO statustable (value), '%s' RETURNING id" %(status) as this will also return the id. But I was looking for putting this all into one. — jerrymouse
– jerrymouse, Commented Sep 19, 2011 at 17:05

NPE · Accepted Answer · 2011-09-19 18:12:28Z

10

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:

SELECT id FROM statustable WHERE value = '%s'. This gives you the id if the entry exists, in which case skip step 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.

answered Sep 19, 2011 at 18:12

NPE

503k114 gold badges970 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jerrymouse Over a year ago

Thanks for the replay @aix, I did just that, I forked my query into 2. My motivation for the single query was just to test if it can be done because it appeared doable. fetchone() is used across a commit because RETURNING id doesn't return id unless committed. (I was getting a None type)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related