copy command with psycopg2 library

Question

Is it possible to run the command below through psycopg2? If so, how can I do it?

COPY table_name(col1,col2) FROM 'path/to/file.csv' WITH HEADER DELIMITER ',' CSV;

Answer 1

The copy_from command is useful for basic use cases:

with open('path/to/file.csv') as f:
    cursor.copy_from(f, 'table_name', columns=('col1', 'col2'), sep=',')

Note that when copying CSV data as described in your question, various problems will prevent you from using copy_from - header rows, quoted values, values containing commas. CSVs can be imported using the copy_expert command with a simple manually composed COPY query:

with open('path/to/file.csv') as f:
    cursor.copy_expert('COPY table_name(col1, col2) FROM STDIN WITH HEADER CSV', f)

Answer 2

Yes!

You can use the copy_from method:

import psycopg2
dbname=...
user=...
password=...
host=...
port=...
con = psycopg2.connect(database=dbname,user=user,password=password,host=host,port=port)
cur = con.cursor()    
f = open('path/to/file.csv')
cur.copy_from(f, 'test', columns=('col1', 'col2'), sep=",")
con.commit()
con.close()

Answer 3

A Google search for psycopg2 copy finds, as the first hit for me, the psycopg manual, which includes instructions on using client-side COPY.

If you want server-side COPY you just run the statement like any other SQL.

As written above the command doesn't make any sense. I presume you meant to write a COPY ... TO or COPY ... FROM command and mangled it while hiding the real file name, etc.