linuxpostgispsycopg2python-3.10postgresql-16

How to print the RAISE NOTICE output , if I am using it with psycopg2?


I'm trying to print during the loop:

WHILE (batch_offset < total_records) LOOP

-- query code

-- Get and display the number of rows processed
    GET DIAGNOSTICS rows_processed = ROW_COUNT;
    current_record := current_record + rows_processed;
    -- RAISE NOTICE 'Current record: %', current_record;

    -- Calculate and print percent complete
    percentage_complete := (current_record::NUMERIC / total_records::NUMERIC) * 100;
    RAISE NOTICE 'Completion percentage: %', ROUND(percentage_complete, 2);

    -- Increment the offset for the next batch
    batch_offset := batch_offset + batch_size;

END LOOP;

using this code:

with psycopg2.connect(**kargs) as conn:
            with conn.cursor() as cur:
            cur.execute(loop_query)

what code should I add, after or before execute, to intercept the notice that is produced at each cycle?

i try, after execute, this:

while conn.notices:
        notice = conn.notices.pop(0)
        print(notice)

But it doesn't give me what I want.


Solution

  • A simple example.

    import psycopg2
    
    loop_qry = """DO
    $$
    DECLARE
        ct integer := 0;
    BEGIN
        WHILE ct < 5 LOOP
            RAISE NOTICE 'Loop counter = %', ct;
            ct = ct + 1;
        END LOOP;
    END;
    
    $$
    """
    with psycopg2.connect("dbname=test user=aklaver port=5432") as con:
        cur = con.cursor()
        cur.execute(loop_qry)
        for notice in con.notices:
            print(notice)
    NOTICE:  Loop counter = 0
    
    NOTICE:  Loop counter = 1
    
    NOTICE:  Loop counter = 2
    
    NOTICE:  Loop counter = 3
    
    NOTICE:  Loop counter = 4