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+

Answer 2

The following two snippets are working code examples for COPY TO and COPY FROM statements.

The next example copies the data from the database table cars into the cars.csv file. It also inclues the header row.

Note that we use the new psycopg3 module.

import psycopg

cs = "dbname='testdb' user='postgres' password='s$cret'"

with psycopg.connect(cs) as con:

    with con.cursor() as cur:

        with open('cars.csv', 'wb') as f:

            with cur.copy("COPY cars TO STDOUT WITH CSV HEADER") as copy:

                for row in copy:
                    f.write(row)

The second example copies the data from the CSV file into the database table:

import psycopg

cs = "dbname='testdb' user='postgres' password='s$cret'"
with psycopg.connect(cs) as con:

    with con.cursor() as cur:

        with open('cars.csv', 'r') as f:

            with cur.copy("COPY cars2 FROM STDIN WITH CSV HEADER") as copy:

                for line in f:
                    copy.write(line)

Here is the SQL for testing the examples:

CREATE TABLE cars(id serial PRIMARY KEY, name VARCHAR(255), price INT);
INSERT INTO cars(name, price) VALUES('Audi', 52642);
INSERT INTO cars(name, price) VALUES('Mercedes', 57127);
INSERT INTO cars(name, price) VALUES('Skoda', 9000);
INSERT INTO cars(name, price) VALUES('Volvo', 29000);
INSERT INTO cars(name, price) VALUES('Bentley', 350000);
INSERT INTO cars(name, price) VALUES('Citroen', 21000);
INSERT INTO cars(name, price) VALUES('Hummer', 41400);
INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);