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).

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. Commented Apr 4, 2021 at 21:16
  • consider adding an updated column to your table such as here and query for rows that have changed since you last checked. Commented Apr 5, 2021 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. Commented Apr 5, 2021 at 11:57
  • @SteveBannon what part of your application makes inserts to your database? How often does it happen? Commented Apr 5, 2021 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. Commented Apr 5, 2021 at 19:50

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.