Question 291004 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to fetch newly added rows in postgres tables in faster way using python

We have a postgres database table where every second 50 rows will be added to that table continously from a realtime application. Now we have read every newly added row(one row at a time) using python script. Is it possible read all the rows without missing any?

If possible, please help me with python script to read realtime updated rows(50 rows/second) from postgres database.

Tried below script.

    print('nConnecting to the PostgreSQL database...n')
    conn = psycopg2.connect(**params)
    
    # create a cursor
    cur = conn.cursor()
    
    while TRUE:
        cur.execute('SELECT * from postgrestable ORDER BY TIMESTAMP DESC LIMIT 1')
        result = cur.fetchone();  
        print(result)

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

Response : Database access is working but i am able get a row every to seconds. Every 2 seconds my database id getting 100 rows. But i am able to fetch only 1 row out of 100.

Is this because of cur.execute() function? Will this function takes time to execute?

Tags:

2

Answers


  1. 0

    Try this updated code, in this code I replaced OrderBy: [asc(document.columns.latestUpdatedAt)] with orderBy: [desc(document.columns.latestUpdatedAt)] . You’ll get the latest chat rooms first because this will sort the result set according to the latestUpdatedAt column in descending order.

    import psycopg2
import time

# Connection parameters
params = {
    'dbname': 'your_database_name',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'port': 'your_port'
}

# Connect to the PostgreSQL database
print('nConnecting to the PostgreSQL database...n')
conn = psycopg2.connect(**params)

# Create a cursor
cur = conn.cursor()

# Initial value for the last processed primary key or timestamp
last_processed_id = 0  # Assuming the primary key is an integer

while True:
    # Fetch new rows based on the primary key or timestamp
    cur.execute('SELECT * FROM postgrestable WHERE id > %s ORDER BY id', (last_processed_id,))
    
    # Fetch all new rows
    new_rows = cur.fetchall()
    
    # Process the new rows
    for row in new_rows:
        print(row)
        # Update the last processed primary key or timestamp
        last_processed_id = row[0]  # Assuming the primary key is the first column
    
    # Sleep for a short duration before checking for new rows again
    time.sleep(1/50)

# Close the cursor and connection (not reached in this example due to the infinite loop)
cur.close()
conn.close()

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    You can set your python client to listen to pg_notify() broadcasted from a trigger...after insert or update on the table collecting your signals.

    create table table_all_signals (
    source_id int not null,
    signal_id uuid default gen_random_uuid() primary key,
    signal_timestamp timestamp not null,
    value numeric not null );

create function f_signal_collector()returns trigger as $f$
begin perform pg_notify('incoming_signals_feed',to_jsonb(new)::text);
      return new;
end $f$ language plpgsql;

create trigger t_signal_collector after insert or update on table_all_signals
for each row execute function f_signal_collector();

    A sample insert will trigger a message to be relayed to your listening client:

    Asynchronous notification "incoming_signals_feed" with payload "{"value": 830.912026524988, "signal_id": "ce2d72e0-a707-4ab5-8162-b37c0063d471", "source_id": 1, "signal_timestamp": "2023-11-23T09:01:09.308965"}" received from server process with PID 1209744.

    To work around the fact you can send only text payload via notify you can instead relay only the unique identifier for your client

    create or replace function f_signal_collector()returns trigger as $f$
begin perform pg_notify('incoming_signals_feed',new.signal_id::text);
      return new;
end $f$ language plpgsql;

    Asynchronous notification "incoming_signals_feed" with payload "2a20ce9b-f077-45ec-a85a-b88fb54d6d0f" received from server process with PID 1209744.

    Then let it simply go pick it up:

    select * from table_all_signals where signal_id='2a20ce9b-f077-45ec-a85a-b88fb54d6d0f';

    And let psycopg2 handle the binding.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search