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

Question

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!

Thomas · Accepted Answer · 2018-01-20 04:01:30Z

1

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'")

answered Jan 20, 2018 at 4:01

Thomas

1,0669 silver badges19 bronze badges

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

1 Comment

D. Miranda Over a year ago

Although the syntax of your query does not work, you gave me the insight of how to solve it. Thanks!

D. Miranda · Accepted Answer · 2018-02-07 03:40:10Z

0

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

answered Feb 7, 2018 at 3:40

D. Miranda

113 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related