Retrieving New Record of PostgreSQL Database in Python

Question

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.sstatic.net/u06W7.png

You do know that time.sleep(1) sleeps for 1 second, not 1 minute, right? And you do not need to commit a database unless you have made changes. — Tim Roberts
– Tim Roberts, Commented Nov 4, 2022 at 6:33
That snippet is not checking the data. It's checking the number of rows, and you're checking the number of rows in the cursor BEFORE you have executed the query. If that's what you want, why not do SELECT count(*) FROM today;? — Tim Roberts
– Tim Roberts, Commented Nov 4, 2022 at 18:24

Faisal Nazik · Accepted Answer · 2022-11-04 11:16:12Z

1

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)

edited Nov 4, 2022 at 11:16

answered Nov 4, 2022 at 6:40

Faisal Nazik

2,9635 gold badges25 silver badges46 bronze badges

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

8 Comments

v015h3bn1k Over a year ago

Well explained, don't forget to tell him that time.sleep(n) for one minute is 60

Irina Lea Over a year ago

It didn't work.

Irina Lea Over a year ago

Please see this link: i.sstatic.net/u06W7.png

Faisal Nazik Over a year ago

Can you post some data in the question that you receive as results? What does that look like?

Irina Lea Over a year ago

Warning! The number has dropped below 105. (First column: 4234 , Second column: just some name, Third column: 170, date:1997.9.9, Country: Canada) So, python is checking the third column wrong. It has not droppwed below 105. It's 170.

|

Collectives™ on Stack Overflow

Retrieving New Record of PostgreSQL Database in Python

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related