CODEWITHSUNDEEP
pythonsqlpostgresqlpython-2.7psycopg2
D. Miranda
D. Miranda

Reputation: 11

How can I update rows obtained by a SELECT query in PostgreSQL with Python? Python 2.7 psycopg2

I have the following code to calculate a value in specific rows of my table:

cursor.execute("SELECT * FROM restaurants WHERE license_type_code='20' ORDER BY general_score DESC;")
group_size = cursor.rowcount
for record in cursor:
   index = cursor.rownumber
   percentile = 100*(index - 0.5)/group_size
   print percentile

What I need to do is to add the percentile result to the respective column score_percentile of each record I got with the SELECT query.

I thought about an UPDATE query like this:

 cursor.execute("UPDATE restaurants SET score_percentile="+str(percentile)+" WHERE license_type_code IN (SELECT * FROM restaurants WHERE license_type_code='20' ORDER BY general_score DESC)")

But I don't know if that query is correct or if there's a more efficient and less silly way to do that (I'm sure there has to be). Could you help me, please?

I'm new with SQL so any help or advice is highly appreciated. Thanks!

Upvotes: 1

Views: 1858

Answers (2)

D. Miranda
D. Miranda

Reputation: 11

As Thomas said, I just needed an update query with the following syntax:

cursor.execute("UPDATE restaurants f SET score_percentile = ROUND(100*(f2.rownumber - 0.5)/"+str(group_size)+",3) FROM (SELECT f2.*,row_number() OVER (ORDER BY general_score DESC) as rownumber FROM restaurants f2 WHERE license_type_code='20') f2 WHERE f.license_type_code='20' AND f2.license_number=f.license_number;")

And I got the group_size by:

cursor.execute("SELECT COUNT(*) FROM restaurants WHERE license_type_code='20'")
group_size = cursor.fetchone()
group_size = group_size[0]

That worked perfect for my case

Upvotes: 0

Thomas
Thomas

Reputation: 1066

You don't need the loop at all. Just one update query

cursor.execute("UPDATE restaurants SET score_percentile = 100*(rownumber - 0.5)/group_size  FROM (SELECT COUNT (*) as group_size FROM restaurants WHERE license_type_code='20') as t WHERE restaurants.license_type_code='20'")

Upvotes: 1

Related Questions