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.
CodePudding user response:
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()
CodePudding user response:
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