Retrieving New Record of PostgreSQL Database in Python-CodePudding

In database table, Second and third columns have numbers. There will be added new rows constantly. -Each time, whenever new rows added in database table, python needs to check them constantly. *When the new row's number received in the .sql table drops below 105, the python should print a Notification message. "Warning! The number has dropped below 105.". On the other hand, whenever the number reveived from the third column is higher than 115, it needs to print "The number is higher than 115". Otherwise, python doesn't needs to give a message. I hope you got it.

Here is my code which is taking data from database constsantly. I don't know how to do the next process. Please, help me.

import psycopg2
import time

# establishing the connection
conn = psycopg2.connect(
    database="database", user='user', password='password', host='127.0.0.1', port='5432'
)

# Setting auto commit false
conn.autocommit = True

# Creating a cursor object using the cursor() method
cursor = conn.cursor()


def fetch_data():
    # Retrieving data
    cursor.execute('''SELECT * from today''')

    # Fetching 1st row from the table
    result = cursor.fetchone()
    print(result)

    # Commit your changes in the database
    conn.commit()


while True:
    fetch_data()
    print("Fetching data every one minute")
    time.sleep(1)  # every sixty sec

```
`
[![See the image][1]][1]


  [1]: https://i.stack.imgur.com/u06W7.png

CodePudding user response：

If you want to check the new row value in 3rd column then

if result[2] < 105:
    print("Warning! The number has dropped below 105.")
elif result[2] > 115:
    print("The number is higher than 115")

fetchone() returns a tuple of data from the row this is the first row.

But you have to fetch last entry to check latest value so replace that to

result = cursor.fetchall()[-1]  # returns a tuple of data from the row this is the last row

As Tim Roberts and Volshebnik commented abouttime.sleep(n), where n is the number of seconds to wait before fetching data again

time. sleep(60)  # Fetch data every minute, you can change it to any time you want

Full Snippet.

import psycopg2
import time

# establishing the connection
conn = psycopg2.connect(
    database="database", user="user", password="password", host="127.0.0.1", port="5432"
)

# Creating a cursor object using the cursor() method
conn.autocommit = True

cursor = conn.cursor()


def fetch_data():
    cursor.execute("""SELECT * from today""")
    result = cursor.fetchall()[
        -1
    ]  # returns a tuple of data from the row this is the last row

    if result[2] < 105:
        print("Warning! The number has dropped below 105.")
    elif result[2] > 115:
        print("The number is higher than 115")


while True:
    fetch_data()
    time.sleep(60)