Try exception handling

Question

I am trying to make an insert of update statement in Python 2.7. I am using Try and Except, but figured that sometimes the except might fail as well. Is there a way to capture this error?

What I mean is the following:

try:
    execute insert statement [example]
except: 
    execute update statement [example]
    WHAT IF THIS FAILS? (This is my question)

Seems that this is something that is not really documented. By the way, using Postgres and could not find a proper UPSERT and this was suggested somewhere on StackO.

Note that Python exception handling is not synced to the transaction state. When you catch a Python exception your transaction is still in the invalid state and won't successfully run new statements. You'd have to use savepoints alongside your try blocks. — Craig Ringer, Jan 09 '15 at 03:35

Christian Tapia · Answer 1 · 2015-01-08T15:58:43.093

3

You can nest try-except clauses:

try:
    execute insert statement [example]
except: 
    try:
        execute update statement [example]
    except:
        handle the errors

Note: You should especify the exception type in the except clause:

except InsertException: # or the one that fits better

Edit: If the update will fail if the insert fails, then it doesn't make sense to put the update statement in the first except.

edited Jan 08 '15 at 15:58

answered Jan 08 '15 at 15:31

Christian Tapia

33,620
7
56
73

1

The problem with this is that, if the insert statement has failed, the update statement will also fail, since the transaction will no longer be valid. – Bruno Jan 08 '15 at 15:43
There is where the second except is entered. You have to put the corresponding code there. – Christian Tapia Jan 08 '15 at 15:57
What I meant is that the update will always fail in this specific case, with `psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block` (see my answer). – Bruno Jan 08 '15 at 15:59
Then it doesn't make sense to put the update in the `except` body. Isn't there any other way to check whether you need to insert or update? – Christian Tapia Jan 08 '15 at 16:00

score 1 · Answer 2 · edited May 23 '17 at 11:57

In general, have another try block within your except block:

try:
    Attempt something here.
except:
    try:
        Attempt something else here.
    except:
        Handle the problem here.

However, this is probably not going to solve the problem in your example:

try:
    execute insert statement [example]
except: 
    execute update statement [example]

Failing to insert first will certainly invalidate your transaction (assuming you're using transactions): in this case, the update statement will fail too. (If you're not using transactions, both statements could fail too, anyway.)

You could instead instead have a look at other strategies for UPDATE/INSERT in PostgreSQL, for example this question.

Here is a full example illustrating the problem with this suggested try/except approach to upsert:

import psycopg2

conn = psycopg2.connect(database='test_so27843654')
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS test_table")
cursor.execute(""" CREATE TABLE test_table (
                        id INTEGER PRIMARY KEY,
                        val TEXT
                   ) """)
cursor.execute("INSERT INTO test_table (id, val) VALUES (1, 'Test 1')")

try:
    cursor.execute("""
        INSERT INTO test_table (id, val) VALUES (1, 'Other Test 1')
    """)
    print "Inserted..."
except:
    try:
        cursor.execute("""
            UPDATE test_table SET val='Other Test 1' WHERE id=1
        """)
        print "Updated..."
    except:
        raise

conn.commit()

This will always fail with: psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block.

score 0 · Answer 3 · answered Jan 08 '15 at 15:32

0

If that happens, all you really have to do is this:

try:
    execute insert statement [example]
except: 

    try:
        execute update statement [example]
    except:

It works as you'd expect.

answered Jan 08 '15 at 15:32

Eithos

2,421
13
13

Thanks Eithos, will definately give this a try. Seems that @bruno is trying to illustrate that when both insert and upsert fail the transaction will fail. That is in most cases (our case) an error with the DB or something else. So this needs to be captured in the nested except clause (so that we catch all errors). Thanks everyone for your quick response. Anyone else hates the fact that Postgres does not have native Upsert in 2015? – user2164689 Jan 08 '15 at 17:45

score -1 · Answer 4 · answered Jan 08 '15 at 15:32

You can nest try/except:

>>> try:
...   1/0
... except:
...   print "in except"
...   try:
...     "1"+[1]
...   except:
...     print "second one"
...
in except
second one

In your case:

try:
    execute insert statement [example]
except: 
    #insert failed
    try:
        execute update statement [example]
    except:
        #update failed

Try exception handling

4 Answers4