How to capture/fetch the result from a delete query to ascertain the # of rows deleted?

Question

I am trying to understand how to capture the number of records deleted from a delete command in Python code using psycopg2. Typically after a delete command is issued in PostgreSql the number of rows deleted appears (i.e., DELETE 8, where 8 represents the number of rows deleted). I would like to record this count after each delete in order to report back to the caller the number of rows deleted without needing to make a separate SELECT count(*) before the DELETE command.

Consider this function:

def qexe(conn, query, fetch_type):
    cursor = conn.cursor()
    cursor.execute(query)
    if fetch_type != None:
        if fetch_type == 'fetchall':
            query_result = cursor.fetchall()
            return query_result
        elif fetch_type == 'fetchone':
            query_result = cursor.fetchone()
            return query_result
        else:
            raise Exception(f"Invalid arg fetch_type: {fetch_type}")
    cursor.close()

After running each of the following, I keep getting the error: psycopg2.ProgrammingError: no results to fetch:

qq = """DELETE FROM INV WHERE ITEM_ID = '{}';"""

item='123abc'

resp0 = qexe(conn, qq.format(item), 'fetchone')
resp1 = qexe(conn, qq.format(item), 'fetchall')

Answer 1

You could make use of RETURNING, so you will be able to get 'something' back which can be fetched from the cursor:

qq = """DELETE FROM INV WHERE ITEM_ID = '{}' RETURNING ITEM_ID"""
resp0 = qexe(conn, qq.format(item), 'fetchone')