-1

I use PostgreSQL 12 SQLAlchemy 1.4

I have a table with multiple columns and one column is a value which is computed in the python code before it's inserted. Now the factors in this computed value may change after a couple of months.

Table
--------------------------------------
player1 | country | date | rank | computed_score

I'm looking for an efficient way to loop over all rows and recompute that column. I have already looked through their examples and can efficiently fetch millions of rows https://docs.sqlalchemy.org/en/14/_modules/examples/performance/large_resultsets.html but how do I update each row efficiently?

2

Fastest way to do it is to do it on database.

Write a database function to calculate score run:

update table set computed_Score=calculate_score();

4
  • Well that just shifts the looping from the python code to the database since I compute the score based on the fields that are present in each row. What that means is that I require the country, date and other columns to obtain all the factors which are used to compute the score. – apple212 Feb 23 at 17:37
  • It would be much faster and simpler than python. Possibly you event dont need to write a database function. You can do it with simple sql. – Burak Yurdakul Feb 23 at 17:46
  • There is a lot of logic behind getting the factors. I would have to create at least two more questions here to find out how to logically obtain the numbers. I have another table containing percentage for three continents and one is default and they are grouped by year and quarter. So using the date I have to extract the year and month then get the quarter and then query for those values – apple212 Feb 23 at 17:49
  • On second thought I think what you said is the best approach. I have now created another question for my function which I'm having issues with. – apple212 Feb 23 at 18:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.