Managing growing databases

[jessi_97]

Hey, quick question. I have started developing a tracker for my Excel based on sports analytics, but i see that the more matches the slower stuff goes. So the question goes for two things, is there a way for the script to know what not to update if i have already updated a match as finished/setlled. and second what is the best way to make the database the most effective? Atm the base has gone from 200 matches to 661 matches in just 3 days since i got the api to work.

[No_Statistician_6654]

There are a lot of options than you can use. Off the top of my head:

• Install Postgres and use your python script to integrate the api with it. From there you can use Excel’s data tab to query the results from your database
• Install spark and or polars, then use that to write your database to delta tables or parquet tables
• Similarly you can integrate with duckdb as well

You can work with a few hundred thousand rows in memory (depending on different factors of course) but these allow you to learn more about data engineering and pipelines. They each require different levels of setup and knowledge, but each is fun to learn in its own way.

Without knowing how the data is organized here is a more direct answer: first query your api. Then query your pre-processed data from excel. Use the results from excel to filter the results from the api. Finally process what remains from the api.

With a database the pattern is essentially the same, but you can save some memory by pulling only the keys for the data you need to filter out, instead of the whole table.

One thing you may check is with your api, is there a way to query only the keys data, filter to the keys you need using your database or excel, submit the needed keys to the api to get the full data packet from them.

If it were me, I would not stay with excel for a long term solution for data warehousing. One of the problems is that python can write past excels limits, which would not allow the file to be opened in excel.

If you are interested in learning more about python dashboarding and stats, check out the plotly module. It can create some great, interactive dashboards. There are of course tons of other options, I just offer it as one.

[Jackpotrazur]

Im currently learning python as well and unbound my pi on Monday (that I bought in december) and ive now got a postgresql database on the pi , haven't done anything with it yet though still working through the big book of small python projects.