How to Make API Calls at Prescheduled Timestamps Based on a SQL Table in Python?

  Kiến thức lập trình
Context

I have a table in an SQL database looking like this.

The goal is to call an api endpoint with the Item id, at the scheduled time, to get the price of the item at that point (the prices are volatile, hence the timestamp is important).

Technology Stack
  • Python 3.8
  • Ubuntu
  • Polars and SQL alchemy for database interfacing
Constraints
  • If you look at the timestamp, they are specified only until a granularity of minute. The second, microsecond components etc. are zero.
  • Each minute may have any number (at most usually 50) of jobs
  • The API call itself has about 2-3s latency before it gives the response
  • The API call does not have to be exactly at the point in time. Generally we can accept a delay of about 90 seconds from the instant specified in the table. That is, if the table says we have to query the price of Item id 8asdgsbas at 12:55:00 am, it is okay if I call it at 12:56:10 am.
  • It is also acceptable if I miss like a small (~1%) of the calls.
  • Best to also ensure the database itself is not queried very often (like every minute). We can keep it once every hour

So the architecture does not have to support extreme QoS.

My Initial, But Incomplete Idea

Run a cronjob every hour.

The job will fetch all rows for that hour by running a simple SQL select where.

So, let’s say, at 12:00:02 am (letting that 2s latency for script trigger up, and querying the database), I have the rows that needs to be called before 1 am.

After the above step, I am thinking of running a loop to sleep for a second and check whether any job to be done in the specific minute. If so, then I will make the corresponding API calls.

Question

But the latency of the API calls make it a bit complicated, as if I may not be able to finish all the API calls. So is there any established pattern or library to handle this scenario in Python?

I don’t need specific code implementation, but general ideas or rough flowcharts would be enough.

LEAVE A COMMENT