python charting data from mysql. Best approach?

Question

I'm building a web app that shows the number of enquiries in a given date range. For example, the user will select "Last 14 days" and it will display a chart of the enquiries

The chart will then have to update itself as the enquiries grow that day.

The data is stored in MySQL on a database server. The web app is written in python.

What would be the best approach to tackle this in your opinion? with two questions in mind:

1. Would you handle the querying the data on the SQL side? (so python calls a specific mysql query based on the user input - SELECT * from TABLE where date = x (where x = user input for example) or would you download the whole table and do the manipulation within python? Or a combination of both given that number of enquiries for all previous days won't change after that day has finished?

2. Would you set a loop in python to download the database every 5mins or so, check for changes and update the chart? or handle it another way?

Answer 1

If I do understand your questions correctly, you want advice about what would be the "best" approach to your problem.

As we all know there is no best way, but only solution that fit well in solving a give problem.

1. I would avoid loading all the data, because it will become a bottle neck in your application as the amount of data will grow. Querying the database with your date parameters gather from a web form is the right way to go. By the way since you seems to need these to lives for some times as your inquiries output could be consider à database report, you may consider store the parameters the user input with a name for the report in a table, so your users can retrieve these report without having to input the parameters again. Also, you seems to ignore what an ORM (http://www.sqlalchemy.org/) or a DAL (https://github.com/web2py/pydal) are. I would suggest you to use such a kind of tool to access your database instead of writing SQL query.

2. Why don't you just tell your user to press F5 or CTRL+R? In most browser these command will make the page reload and since the parameters to generate your report, which should be present in the URL, the report will be regenerate with the new inserted data available into the database.

If this solution is to old school, I will suggest you to read about "Long Polling", WebSocket, etc.

Here a SO question about that with a good answer : What are Long-Polling, Websockets, Server-Sent Events (SSE) and Comet?

This represent much more work and skill to master than simply the regular HTTP, that why I suggest you to start with the basic...