Do I have to commit after an exception has been raised? My database generates a lot of orphan pages, and I'm trying to figure out why.

I often do this in my python code, using the fdb driver:

cursor = connection.cursor()
for item in list:
    try:
        cursor.execute(sql_insert_statement, (item,))
    except fdb.DatabaseError:
        log(f"Exception: duplicate found in database for {item}")
        continue

connection.commit()


I generate a lot of exceptions due to primary key unique constraint violations (which is fine), and I only commit the "main" transaction once, when all items in the list have each executed their INSERT statement.

Is this the right way of doing things? Or do I have to commit after each  exception that occurred instead (with a finally clause which would trigger after every try regardless of its outcome)? 

At the very least, I doubt I need to commit after each cursor.execute, judging from the documentation.
You always need to either commit or rollback a transaction when the relevant unit of work is complete. Firebird transactions are not automatically aborted on errors (but usually the effects of the statement causing the error are undone). If you don't commit or rollback and the connections gets closed, open transactions will be rolled back eventually, but this is inefficient and can cause performance degradation (depending on how long that transaction remains open).

So, no, you don't have to commit (or rollback) immediately after an error, you commit when your work is complete, or rollback when your unit of work needs to be aborted.

However, very long running transactions can be problematic for other transactions, so make sure that your unit of work (transaction) is as short as possible (but not shorter than necessary for the work to be done).

However, failure to commit or rollback cannot cause errors about orphan pages. Orphan pages are a result of Firebird allocating database pages, but failing to add/register them to an object (table, index, etc), which means they exist and take space, but are unusable because nothing points to them.

This is usually the result of Firebird crashing or being killed in the middle of a write, or allocation bugs. You may want to check your logs for errors.