Repeatedly select latest Data from Database using python

Question

I am trying to store the most recent row of data from a database in variables using python, however the script is storing the row of data at execution and not the latest set of data.

Below shows the code to pull the last row from the database. The database gets a new row of data every minute.

#!/usr/bin/env python

#import required modules
from time import sleep
import MySQLdb


#set MySQL Variables
host = "localhost"
user = "user"
password = "pass"
schema = "schema"

#connect to MySQL
db = MySQLdb.connect(host, user, password, schema)
curs = db.cursor()

#Define Sql Queries to Read the last Value in the Database
sqlT="SELECT temperature FROM system_info ORDER BY pi_id DESC LIMIT  1"
sqlH="SELECT humidity FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlIP="SELECT ip_address FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI1="SELECT DI1 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI2="SELECT DI2 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI3="SELECT DI3 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI4="SELECT DI4 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI5="SELECT DI5 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI6="SELECT DI6 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI7="SELECT DI7 FROM system_info ORDER BY pi_id DESC LIMIT 1"
sqlDI8="SELECT DI8 FROM system_info ORDER BY pi_id DESC LIMIT 1"

#Begin Script Infinitely
while True:
        #Gather Values from Database
        curs.execute(sqlT)
        lastT = curs.fetchone()
        for Temp in lastT:
                temp_reading = Temp
        curs.execute(sqlH)
        lastH = curs.fetchone()
        for Hum in lastH:
                hum_reading = Hum
        curs.execute(sqlDI1)
        lastDI1 = curs.fetchone()
        for DI1 in lastDI1:
                DI1_reading = DI1
        curs.execute(sqlDI2)
        lastDI2 = curs.fetchone()
        for DI2 in lastDI2:
                DI2_reading = DI2
        curs.execute(sqlDI3)
        lastDI3 = curs.fetchone()
        for DI3 in lastDI3:
                DI3_reading = DI3
        curs.execute(sqlDI4)
        lastDI4 = curs.fetchone()
        for DI4 in lastDI4:
                DI4_reading = DI4
        curs.execute(sqlDI5)
        lastDI5 = curs.fetchone()
        for DI5 in lastDI5:
                DI5_reading = DI5
        curs.execute(sqlDI1)
        lastDI6 = curs.fetchone()
        for DI6 in lastDI6:
                DI6_reading = DI6
        curs.execute(sqlDI1)
        lastDI7 = curs.fetchone()
        for DI7 in lastDI7:
                DI7_reading = DI7
       curs.execute(sqlDI8)
       lastDI8 = curs.fetchone()
       for DI8 in lastDI8:
                DI8_reading = DI8

        #Print Values
        print(temp_reading)
        print(hum_reading)
        print(DI1_reading)
        print(DI2_reading)
        print(DI3_reading)
        print(DI4_reading)
        print(DI5_reading)
        print(DI6_reading)
        print(DI7_reading)
        print(DI8_reading)

        #Rest so Values change
        sleep(61)

The results are displayed correctly for the first iteration of the while loop, however the result do not update to the latest set of data from the database, it has stored the row data in to the variable once and is not updating the variable.

What I get:

22
45
Open
Open
Open
Open
Open
Open
Open
Open
22
45
Open
Open
Open
Open
Open
Open
Open
Open

What I am looking to get:

22
45
Open
Open
Open
Open
Open
Open
Open
Open
25
56
Open
Open
Closed
Open
Open
Open
Open
Open

Thanks in advance

EDIT Adding in example of Table Entries to accompany post:

pi_id | time | ip_address | temperature | humidity | DI1 | DI2 | DI3 | DI4 | DI5 | DI6 | DI7 | DI8 |
5768  | 13:45 | 10.0.0.1 |   22   |   45   | Open | Open | Open | Open | Open | Open | Open | Open |
5769  | 13:46 | 10.0.0.1 |   25   |   56   | Open | Open | Closed | Open | Open | Open | Open | Open |

I run the script at 13:45, it gathers the row of data with pi_id: 5768, then a minute later a new row of data is input in to the table, when the While Loop in the code runs again, it prints the data from pi_id: 5768 and not the most recent data pi_id: 5769, which is what I am trying to achieve.

The repetition in this code makes no sense to me at all. Something is wonky with the logic but I cab't grasp what the end result should be. You might be able to unpack each row. — roganjosh
– roganjosh, Commented Jan 18, 2019 at 15:18
Provide definition of system_info - CREATE TABLE/CREATE VIEW for start — Alex Yu
– Alex Yu, Commented Jan 18, 2019 at 15:21
You should make one call to the database and grab all columns in a single query. Then consume that single row of results in your code. — Shawn
– Shawn, Commented Jan 18, 2019 at 15:22
I suspect you cannot reuse a cursor object in this way; if I could find a definitive statement one way or the other, I'd post an answer, but reading several sources suggests this is the case, but it's still vague; Python is not my forte, but I think you ned to .close() the cursor and reopen it for each loop; certainly closing and re-establishing the database connection should resolve this issue, and if you are polling only every minute, that shouldn't be too painful — landru27
– landru27, Commented Jan 18, 2019 at 15:51
@Ingaz I have edited the post to contain an example of what the Table system_info looks like — DateMike
– DateMike, Commented Jan 18, 2019 at 16:36

DateMike · Accepted Answer · 2019-01-21 16:28:34Z

I managed to get my required results by closing the DB connection and then reconnecting at the beginning of the While loop:

#Begin Script Infinitely
while True:

    #Connect/Re-connect to database
    db = MySQLdb.connect(host, user, password, schema)

    #Define Cursor
    curs = db.cursor()

    #Gather Values from Database
    curs.execute(sqlT)
    lastT = curs.fetchone()
    for Temp in lastT:
        temp_reading = Temp
    curs.execute(sqlH)
    lastH = curs.fetchone()
    for Hum in lastH:
        hum_reading = Hum
    curs.execute(sqlDI1)
    lastDI1 = curs.fetchone()
    for DI1 in lastDI1:
        DI1_reading = DI1       
    curs.execute(sqlDI2)
    lastDI2 = curs.fetchone()
    for DI2 in lastDI2:
        DI2_reading = DI2
    curs.execute(sqlDI3)
    lastDI3 = curs.fetchone()
    for DI3 in lastDI3:
        DI3_reading = DI3
    curs.execute(sqlDI4)
    lastDI4 = curs.fetchone()
    for DI4 in lastDI4:
        DI4_reading = DI4
    curs.execute(sqlDI5)
    lastDI5 = curs.fetchone()
    for DI5 in lastDI5:
        DI5_reading = DI5
    curs.execute(sqlDI6)
    lastDI6 = curs.fetchone()
    for DI6 in lastDI6:
        DI6_reading = DI6
    curs.execute(sqlDI7)
    lastDI7 = curs.fetchone()
    for DI7 in lastDI7:
        DI7_reading = DI7
    curs.execute(sqlDI8)
    lastDI8 = curs.fetchone()
    for DI8 in lastDI8:
        DI8_reading = DI8   

    #Close Cursor
    curs.close()

    #Disconnect from Database
    db.close()

    #Print Values
    print(temp_reading)
    print(hum_reading)
    print(DI1_reading)
    print(DI2_reading)
    print(DI3_reading)
    print(DI4_reading)
    print(DI5_reading)
    print(DI6_reading)
    print(DI7_reading)
    print(DI8_reading)

    #Rest so Values change
    sleep(60)

Thanks to @Iandru27 and @Rick James for getting the idea across.

Now just to neaten it all up so it's not so convoluted/repetitive.

Thanks

roganjosh · Accepted Answer · 2019-01-18 15:27:31Z

0

I suspect this isn't a full answer but it's too long to put in comments. Create a list of dictionaries:

data = []
c.execute("""SELECT * FROM system_info ORDER BY pi_id DE0SC LIMIT 1""")
headers = [item[0] for item in c.description] # List comprehension
for row in c.fetchall():
    data.append([dict(zip(headers, row)])

This will run the query once and return a list of dictionaries that you can use to access values by key. As it stands, there's no real need to iterate the rows since you have a LIMIT 1 but this would scale.

answered Jan 18, 2019 at 15:27

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

3 Comments

DateMike Over a year ago

I understand the code is quite repetitive, ideally I would like to run something like: sqlT="SELECT * FROM system_info ORDER BY pi_id DESC LIMIT 1" which would select the last row of data and then use each column as a variable instead of doing a SELECT query each time for each row. I am trying to obtain the last row in the database each time the While Loop runs, but the Loop is always giving me the data it SELECTED the first time the Loop ran. In the above comments @landru27 seems to have an idea I'll try as well.

roganjosh Over a year ago

... did you actually try my code? I have you the last row as a dictionary that you could access by key. One query, all the columns.

DateMike Over a year ago

How would I access the data after running your code? i.e. how would I set the temperature data to a temperature variable to use elsewhere?

Collectives™ on Stack Overflow

Repeatedly select latest Data from Database using python

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related