Fetch new data from SQL in python every few seconds

Question

I want to make a python script that consistently checks the Mysql database and prints out new updated records every few seconds.

import time
sql_conn = connectSQLServer('ODBC Driver 13 for SQL Server', 'D\SQLEXPRESS', 'display')

mycursor = sql_conn.cursor()

a = 0
while True:
    try:
        time.sleep(1)
        a=a+1
        print(a)
        sql = "SELECT id, val FROM dbo.LiveStatsFromSQLServer"    
        mycursor.execute(sql)

        myresult = mycursor.fetchall()

        for x in myresult:
            print(x)


    except Exception as error:
        print("Nothing in database\n")
        print(error)
sql_conn.commit()

Expected output: The columns are (id, value)

1second
(1625, 3)
(1626, 0)
(1627, 10)
2second
(1625, 3)
(1626, 0)
(1627, 10)
3second
(1625, 3)
(1626, 0)
(1627, 10)
(1628,20)

The code is generating all the outputs, where I want new sql outputs — uiu808
– uiu808, Commented Feb 23, 2020 at 5:03

Grismar · Accepted Answer · 2020-02-23 05:09:48Z

1

Since your records appear to start with an auto-incrementing identifier, you could do:

sql = "SELECT id, val FROM dbo.LiveStatsFromSQLServer WHERE id > %s"
mycursor.execute(sql, prev_id)

And after the print(x) just add something like prev_id = x[0]:

    for x in myresult:
        print(x)
        prev_id = x[0]

Note: I don't know what the actual first column of your result is called, just replace id with the actual column name in the script if you want to select * instead of naming the columns (and assuming there's 2).

answered Feb 23, 2020 at 5:09

Grismar

32.4k6 gold badges43 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

uiu808 Over a year ago

Hi Grismar, thank for your quick reply, i got a error saying that prev_id is not defined

Grismar Over a year ago

You'll need to initialise prev_id before the loop starts, e.g. prev_id = 0, to something less than the smallest possible identifier. (could be 0, could be -1, could be the maximum negative integer, but 0 is most likely - have a look at your database to decide what a sensible start value is)

Collectives™ on Stack Overflow

Fetch new data from SQL in python every few seconds

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related