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
  • 827
  • 4
  • 13
the_dude
  • 49
  • 5
  • 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

0 Answers0