Track row affected by a plpgSQL's DML from Python code block [closed]

Question

I just wanted to know that how could I hold the value of a DML(affected rows) using python code.

Suppose a DML is affecting 10 rows so how will I print it using python.

db = psycopg2.connect(dbname=ETL_DB, host=ETL_HOST, port=ETL_PORT, user=ETL_USER, password=ETL_PASSWORD)
cursor = db.cursor()
upd_sql = "update STG01.UPDATE_MINUS_ONE_CTRL_TAB set PROCESS_END_TIME = sysdate"
log.info(upd_sql)
cursor.execute(upd_sql)

## Here I want to print how many rows are getting affected by the above execution...

Thanks in advance.

Answer 1

Write it with a RETURNING clause and then perform cursor.fetchall() to get affected rows and then their len() in the code.

upd_sql = """
    UPDATE STG01.UPDATE_MINUS_ONE_CTRL_TAB 
    SET PROCESS_END_TIME = sysdate
    RETURNING *
"""
cursor.execute(upd_sql)
affected_rows = cursor.fetchall()
log.info(len(affected_rows))

Or write the update as a Common Table Expression and then count(*) affected rows.

upd_sql = """
    WITH updated_rows AS (
        UPDATE STG01.UPDATE_MINUS_ONE_CTRL_TAB 
        SET PROCESS_END_TIME = sysdate
        RETURNING *
    )
    SELECT 
        count(*) AS updated_rows_count
    FROM updated_rows
"""
cursor.execute(upd_sql)
count = cursor.fetchone()["updated_rows_count"]
log.info(count)

Answer 2

After calling execute(), the cursor rowcount attribute contains the number or rows affected, as returned by the server. The statusmessage attribute contains the entire tag.

Reference: https://www.psycopg.org/docs/cursor.html