Minimal Python program to upload data to Postgres using psygopg3 COPY does not appear to run the COPY operation

Question

I have written a minimal test example to try and debug some issue I have been having where COPY does not appear to do anything when used to copy data to a postgres database.

This is my Python code:

postgres_connection_string = 'user=postgres password=example host=192.168.x.y dbname=postgres port=5432'

import pandas
df = pandas.read_csv('test_file.csv', header=None)
import psycopg
import io
file = io.StringIO()
df.to_csv(file, index=False, header=False)
file.seek(0)
columns = '(string_column, int_column)'
with psycopg.connect(postgres_connection_string) as conn:
    with conn.cursor() as cur:
        cur.copy(f'COPY example_schema.test_table {columns} FROM STDIN WITH (FORMAT csv)', file)
    conn.commit()

The contents of test_file.csv:

$ cat test_file.csv 
hello world,1
goodbye world,2

The DDL for the Postgres table:

CREATE TABLE example_schema.test_table (
    test_table_id serial4 NOT NULL,
    string_column varchar NOT NULL,
    int_column int4 NOT NULL,
    CONSTRAINT test_table_pkey PRIMARY KEY (test_table_id)
);

When I run this it completes without error, however no data is uploaded to the postgres database.

For the "real" program, the quantity of data to upload is large. (Several gigabytes.) However the final block containing cur.copy completes in much less than 1 second, suggesting that it does not do anything.

Why might this be?

user2138149 · Accepted Answer · 2025-11-16 12:12:19Z

2

With psycopg3, cur.copy() returns a context manager. So the syntax required is a bit different:

with psycopg.connect(postgres_connection_string) as conn:
    with conn.cursor() as cur:
        with cur.copy(f'COPY example_schema.test_table {columns} FROM STDIN WITH (FORMAT csv)') as copy:
            copy.write(file.read())
    conn.commit()

answered Nov 16 at 12:12

user2138149

18.7k32 gold badges161 silver badges322 bronze badges

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

1 Comment

Adrian Klaver Nov 16 at 16:21

Do not use f' strings. Read Passing Parameters and sql for proper string composition. Or if you want to live on the edge see Template strings. See my answer here stackoverflow.com/questions/74209444/… for how to use sql formatting.

Collectives™ on Stack Overflow

Minimal Python program to upload data to Postgres using psygopg3 COPY does not appear to run the COPY operation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related