Getting affected row count from psycopg2 connection.commit()

Question

Currently, I have the following method to execute INSERT/UPDATE/DELETE statements using psycopg2 in Python:

def exec_statement(_cxn, _stmt):
    try:
        db_crsr = _cxn.cursor()
        db_crsr.execute(_stmt)
        _cxn.commit()
        db_crsr.close()
        return True
    except:
        return False

But what I would really like it to do, instead of bool, is return the row count affected by the transaction or -1 if the operation fails.

Is there a way to get a number of rows affected by _cxn.commit()? E.g. for a single INSERT it would be always 1, for a DELETE or UPDATE, the number of rows affected by the statement etc.?

Do not use the bare `except` clause! There is the `psycopg2.errors` module you should make use of. There is a complete list in the [docs](https://www.psycopg.org/docs/errors.html) — Kolay.Ne, Jun 06 '21 at 12:22

khampson · Accepted Answer · 2018-07-04T04:42:49.133

114

commit() can't be used to get the row count, but you can use the cursor to get that information after each execute call. You can use its rowcount attribute to get the number of rows affected for SELECT, INSERT, UPDATE and DELETE.

i.e.

    db_crsr = _cxn.cursor()
    db_crsr.execute(_stmt)

    rowcount = db_crsr.rowcount

    _cxn.commit()
    db_crsr.close()

    return rowcount

If you want to return the number of affected rows, I would recommend not catching any exceptions, since if the operation truly failed (say the query was malformed, or there was a FK constraint violation, etc.), an exception should be raised, and in that case the caller could catch that and behave as desired. (Or, if you want to centralize the exception handling, perhaps raise a custom MyPostgresException, or similar.)

-1 can be returned in a non-failure case in certain situations (http://initd.org/psycopg/docs/cursor.html#cursor.rowcount), so I would recommend against using that value as the failure indicator. If you really want to return a numerical value in the case of failure, perhaps returning a number like -10 would work (in the except block), since rowcount shouldn't ever return that.

edited Jul 04 '18 at 04:42

answered Jun 26 '14 at 23:18

khampson

14,700
4
41
43

You give good reasons not to catch exceptions, but is there a reason that `db_crsr.close()` isn't in a `finally` block? It will fall out of scope at the end of the method. – jpmc26 Jun 27 '14 at 00:49
@jpmc26: Nope. That was from the OP's original code and I was not focused on that aspect. In this particular case, I would probably actually declare the *cursor* in a `with` block so it would auto close. – khampson Jun 27 '14 at 00:52
2

Silly me. Forgetting `with`. =) I need more Python in my life. – jpmc26 Jun 27 '14 at 00:53
@jpmc26: =) Yeah, this is just about the perfect case for it, IMO -- compact function where the cursor is short-lived and there's only one involved. – khampson Jun 27 '14 at 00:57
The weird thing with bulk insert query is that I'm getting `rowcount` as 34, but actual inserted rows are 5334 which are expected. Does anyone knows why is that? – Ali Sajjad Apr 28 '23 at 10:48

Getting affected row count from psycopg2 connection.commit()

1 Answers1

Linked

Related