Extract data from sql in python (one time per second)

Question

how do i extract data from sql in python? I have this code but it is repeating all the values which is not what i want. I wish to have new updated sql data to be in my python. I am almost struck for a few days..Any kind soul pls I am using sqlserver2012, python

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

mycursor = sql_conn.cursor()

a = 0
while True:
    try:
        time.sleep(1)
        a=a+1
        print(a)
        sql = "SELECT * 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()

The result shown is 
1
(1625, 3)
(1626, 0)
(1627, 10)
2
(1625, 3)
(1626, 0)
(1627, 10)
3
(1625, 3)
(1626, 0)
(1627, 10)
(1622, 20)
while i wish to have a constantly updating to the end of the list like:
(1625, 3)
(1626, 0)
(1627, 10)
after 1 second
(1622, 20)
after 1 second
(1612, 10)

Can you clarify what the problem is? As an aside, using `except Exception as error:` like that is bad practice, see https://stackoverflow.com/questions/54948548/what-is-wrong-with-using-a-bare-except, https://stackoverflow.com/questions/4990718/about-catching-any-exception — AMC, Feb 22 '20 at 19:26
Ok, noted. I wish to have new data records(1 per s) coming from sql to my python, which i cant do it — uiu808, Feb 22 '20 at 19:35

score 2 · Accepted Answer · answered Feb 22 '20 at 19:01

If I understand this right, you only want to see new records (those added since the previous query). You need to add some logic in your application.

Your table probably already has an auto-incremented ID (or a timestamp of some sort could do). After each query, you need to memorize the maximum value you got, and the subsequent query should include a WHERE clause (ie WHERE ID > nnnnn) so that you only get records having an ID greater than that maximum value from your previous query.

Alternatively you could use a datetime value but this could be less accurate (unless you don't mind some overlap).

This query simply returns everything.

PS: I don't know why you have a commit in your exception handling block. You are not doing any transaction here, you are not writing any data to the DB either. Variable a is not used.

Hi there, yes, i would only want to see new records. Yes, my table does have the auto-incremented id. Do you have a example of the subquery? — uiu808, Feb 22 '20 at 19:33
@uiu808 there are instructions here about how to pass parameters into SQL from python https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python — LJ01, Feb 22 '20 at 19:35

LJ01 · Answer 2 · 2020-02-22T19:28:59.100

1

If editing the table schema is not an option it may be possible to get close to what you want with the following line...

  for row in cursor.execute("""SELECT num,id ,row_number() partition by (select 1) FROM dbo.LiveStatsFromSQLServer"""):

Edit

Seeing you can change the schema, add a column for dating edited in the table you are querying.

 for row in cursor.execute("""SELECT num,id FROM dbo.LiveStatsFromSQLServer where datetime> dateadd(s,-1,getdate())"""):

edited Feb 22 '20 at 19:28

answered Feb 22 '20 at 18:55

LJ01

589
4
11

thanks for your fast reply! Ok ill try! I have been struck for days omg – uiu808 Feb 22 '20 at 18:58
Do you have any record or when the table was edited in SQL? If the SQL table has a datetime edited column would make your task much easier – LJ01 Feb 22 '20 at 18:59
I have tried both functions by printing the rows but it seemed to be unable to retrieve the data – uiu808 Feb 22 '20 at 20:23
You will have to add a 'Datetime' column to make it work. The row_number may be incorrect syntax or not supported in mssql2012. I can't really test, all this is off my phone – LJ01 Feb 22 '20 at 20:40
Yeap, i did add the datetime column as datetime to test for the second code. Really appreciate your help here! – uiu808 Feb 22 '20 at 20:58

Extract data from sql in python (one time per second)

2 Answers2