CODEWITHSUNDEEP
pythondjangopostgresql
Shafiqul Islam
Shafiqul Islam

Reputation: 5690

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

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 enter image description here

Upvotes: 1

Views: 1019

Answers (1)

Belayer
Belayer

Reputation: 14934

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.

Upvotes: 2

Related Questions