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.

Does this answer your question? [Insert pandas data frame into Postgres](https://stackoverflow.com/questions/59613267/insert-pandas-data-frame-into-postgres) — itprorh66, Nov 05 '21 at 23:51
Well. I'm sure that solution works. I was trying to avoid using another library to manage SQL statements. In that solution, it seems that SQLAlchemy was used. I was specifically looking for a psycopg solution. I could use some other library with simpler functionality, but feel like that's kind of sloppy coding. — Caritas, Nov 05 '21 at 23:59
See here [COPY](https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy), the 'Copying block-by-block' example. — Adrian Klaver, Nov 06 '21 at 00:11
I had that exact page open when I wrote the question, but I don't fully understand it. My python-fu isn't that strong. The statement with open('data', 'r') seems to open the file based on the filename. I see that. And the while loop seems to write values iteratively to the database until the document is finished being read. But this line : `with cursor.copy("COPY data FROM STDIN") as copy` gives me trouble. it seems like the entire file is being converted into an object that is then stored and (somehow) broken down for column-based storage in the db. Is that correct? — Caritas, Nov 06 '21 at 01:01
See my answer for examples. I'm still figuring out `psycopg3` so the answer probably needs refining, but I think it will serve as a start. — Adrian Klaver, Nov 06 '21 at 04:31

Adrian Klaver · Accepted Answer · 2021-11-06T04:26:06.290

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+

Perfect response. Psycopg3 docuement is not clear about being able to add WITH part. That's why I was in trouble too. Thank you Adrian. What is the unit of that 100? Byte, Char? — Ahmad, Sep 21 '22 at 17:42
For `read(size)` see [7.2. Reading and Writing Files](https://docs.python.org/3/tutorial/inputoutput.html#tut-files): "...call f.read(size), which reads some quantity of data .." [...] "Otherwise, at most size characters (in text mode) or size bytes (in binary mode) are read ..." — Adrian Klaver, Sep 21 '22 at 19:50

score -1 · Answer 2 · answered Nov 06 '21 at 00:22

-1

I do not see that you make a commit to persist the data in your data base after the input. Try to add this:

conn.commit()

answered Nov 06 '21 at 00:22

Thomas Kutsch

11
1
2

1

If I recall correctly, when used with the `with` keyword as context, `conn.commit()` is automatically executed upon close. But I tried it anyway, and that did not affect the output. – Caritas Nov 06 '21 at 00:51
can you explain what this does ? – Ahmed Sbai Feb 19 '23 at 00:04

score -1 · Answer 3 · answered Nov 06 '21 at 20:12

-1

You probably should include the with (format csv) clause (see https://www.postgresql.org/docs/current/sql-copy.html) or explicitly specify the quote and delimiter characters.

answered Nov 06 '21 at 20:12

rd_nielsen

2,407
2
11
18

1

The psycopg3 documentation specifically says to leave those clauses alone. It crossed my mind. I think psycopg3 has a unique way of doing things. – Caritas Nov 06 '21 at 23:18
When copying block-by-block, as you are with a file, the psycopg3 documentation says "In this case you can use any COPY option and format, as long as the input data is compatible." – rd_nielsen Nov 06 '21 at 23:38
When writing/reading row by row you cannot use the additional clauses. As was pointed out when writing/reading by block you can use them. – Adrian Klaver Nov 07 '21 at 16:45

Copying CSV to PostgreSQL database using Psycopg3 in Python

3 Answers3