I need to affected row count in PostgreSQL Python or Django when run same query 2 or more time

Question

I need to get all affected row count in PostgreSQL . If i run update query then got right row count but if i run again same query then i also get same row count but i think second time no row affected so in this case row count will be 0. Here is my simple code

import psycopg2

#db connection
conn = psycopg2.connect(
host="localhost",
database="test_db",
user="postgres",
password="123456"
)


#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = "UPDATE test_table SET balance = 11 WHERE last_block = '14529343'"
cursor.execute(sql)
print("Rows returned = ",cursor.rowcount)
conn.commit()

conn.close()

#Closing the connection

After run this python script my current row affected is:

Rows returned = 70

second time also same output

Rows returned = 70 but it is not correct, result will be 0

but if i run same update query then same result but in this case output will be 0. This is screenshot

You are running an update that does not change anything used in the where clause. Since your where clause references only a specific last block but does not change that value any subsequent run of the same query will process exactly the same rows, giving the same count every time. Perhaps you need to add the predicate and balance <> .... — Belayer
– Belayer, Commented Jun 29, 2022 at 22:41
@Belayer but how can i get this result? any idea? it will be grateful help. Mainly main target, when i will run same query then no data will be updated so i will get affected row 0 — Shafiqul Islam
– Shafiqul Islam, Commented Jun 30, 2022 at 6:26

Belayer · Accepted Answer · 2022-06-30 18:27:08Z

2

Your statement no data will be updated is wrong in fact the exact same rows will be updated again, even though it is to the same value. The value of column balance will not change but the rows will be updated. The update statement does not look at existing values, to see that they have changed. It determines which rows are processed by conditions in the where clause only. Since you did not update the only criteria in the where and then ran again with the same where the same rows will be processed. You can see this by also selecting the CTID

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL.

(Emphasis mine)

See demo here. Updated is not the same a value changed.

answered Jun 30, 2022 at 18:27

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I need to affected row count in PostgreSQL Python or Django when run same query 2 or more time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related