Copying CSV to PostgreSQL database using Psycopg3 in Python

Question

I'm having a little difficulty understanding appropriate syntax for the psycopg3 library in Python. I'm trying to copy the contents of a .csv file into my database. The PostgreSQL documentation indicates copy should be written as follows:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

so I wrote my python statement as follows:

import psycopg


with psycopg.connect('dbname=ideatest user=postgres password=password') as conn: 
        with conn.cursor() as cur:
            mock_idea_info = open(r'C:\dir\filename.csv')
            cur.copy('public.ideastorage FROM C:\dir\filename.csv;')

print('Copy successful.')

The problem is that the script prints 'Copy successful,' but does not insert the data into the db. No error messages are generated. I've duplicated the \ characters in the file path, so that isn't the issue. I've been looking around for solutions and possible troubleshooting methods, but have yet to find anything I understand that seems relevant.

Additionally, is there any way I might be able to pass mock_idea_info directly into the copy statement?

Any assistance would be immensely appreciated.

Answer 1

See Copy from:

cat data.out 
1       2
2       1

\d csv_test 
              Table "public.csv_test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col1   | integer |           |          | 
 col2   | integer |           |          | 


with open("data.out", "r") as f:
     with cur.copy("COPY csv_test FROM STDIN") as copy:
         while data := f.read(100):
            copy.write(data)
con.commit()

select * from csv_test ;
 col1 | col2 
------+------
    1 |    2
    2 |    1

--Add format options
cat data.out 
1,2
2,1
with open("data.out", "r") as f:
     with cur.copy("COPY csv_test FROM STDIN WITH (FORMAT CSV)" ) as copy:
         while data := f.read(100):
            copy.write(data)
con.commit()

select * from csv_test ;
 col1 | col2 
------+------
    1 |    2
    2 |    1
    1 |    2
    2 |    1

Above adapted from examples in link. This while data := f.read(100) uses the walrus(:=) only available in Python 3.8+