1

On Google Cloud, I'm running an instance of Compute Engine to monitor changes in a table that's on a SQL instance. As per this thread: How to execute Python function when value in SQL table changes?

I made a loop.

My code is:

from sqlalchemy import create_engine
import pandas as pd

un_cloud="xxx"
pw_cloud="xxx"
ip_cloud="xxx"
db_n_cloud="xxx"
engine_input="mysql+pymysql://"+un_cloud+":"+pw_cloud+"@"+ip_cloud+":3306/"+db_n_cloud
engine = create_engine(engine_input)
con_cloud_local = engine.connect()
base=0
while True:
    table=pd.read_sql_table('xxx', con=con_cloud_local)
    if table.shape[0]>base:
        base=table.shape[0]
        print(base)

As time progresses the printouts are taking more and more to appear and even at the beginning they are over a second apart. (when it's running for a few minutes, I'm getting to 20s between printouts). That's horrible. I'm looking to get updates close to real-time (<1s).

Antonio Ramirez
  • 943
  • 6
  • 15
the_dude
  • 61
  • 7
  • Re-reading the table is a horrendous idea. Depending on what is it you are trying to achieve (what posts to the DB and what is supposed to happen), I'd re-think and change the architecture of this application, literally anything is better than while true: reread. – jabbson Apr 04 '21 at 21:16
  • consider adding an `updated` column to your table such as [here](https://docs.sqlalchemy.org/en/14/dialects/mysql.html?highlight=on_duplicate_key_update#rendering-on-update-current-timestamp-for-mysql-mariadb-s-explicit-defaults-for-timestamp) and query for rows that have changed since you last checked. – SuperShoot Apr 05 '21 at 11:19
  • I changed it to a "while True" continous row count ("SELECT COUNT(*) FROM xxx"), seems fast enough, what do you think, can I do better? @jabbson: the goal is to start functions that further process the data once the update arrives, turning it into metrics that go onto a dashboard. – the_dude Apr 05 '21 at 11:57
  • @SteveBannon what part of your application makes inserts to your database? How often does it happen? – jabbson Apr 05 '21 at 14:11
  • I've got an API and a function that turns the output into pandas tables, frequency can very massively since it's based on user activity. The general outline is: get data from API, save it to SQL database, detect this, run processing functions, save to another sql table, feed into dashboard. – the_dude Apr 05 '21 at 19:50
  • @the_dude - did you decide to continue with the approach you mentioned earlier or did you find something better? I'm working on something with very similar requirements and would like to hear your experience. – Dhanush Giriyan Dec 26 '22 at 18:33
  • It's been a while, but I think I switched to a loop that runs every x seconds checking for new content. Not really what I was aiming for, but I didn't have a better solution so I had to compromise. – the_dude Dec 27 '22 at 23:37

0 Answers0