Copy Command with Psycopg2 Library

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()

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.

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)

There is one dealbreaker using copy_from: It doesn't recognize quoted fields, e.g. if you have a value with, a comma and use csv.writer then this is written as ,"with, a comma". psycopg2 doesn't recognize this (see the quotestring comment of @shrinathM).

That's why for most cases you need to fall back to the more basic copy_expert.

That said, the other difficult thing I found when using COPY was to understand how the CSV must be structured in order that postgres takes it up correctly. Here a basic code using StringIO instead of a file.

The following solution shows you both how to write the CSV with python and also how to then use copy_expert. If you have the CSV already, then you just need the last line.

import io
import csv
import datetime 

f = io.StringIO()
w = csv.writer(f)
data = [
  ['Hans', [1,2,3], True],
  ['Kurt', [4], False],
]
columns = ['name', 'ids', 'has_foo']
print('convert to csv format')
for l_in in data:
  l_out = []
  for v in l_in:
    if v == None:
      l_out.append('')
    elif type(v) in [str, int, datetime.date]:
      l_out.append(str(v))
    elif type(v) in [list, set, tuple]:
      l_out.append('{' + ','.join(str(i) for i in v) + '}')
    elif type(v) == bool:
      if v:
        l_out.append('t')
      else:
        l_out.append('f')
    else:
      print(f'unsupported type {type(v)}, writing str()')
      l_out.append(str(v))
  w.writerow(l_out)
print('actual copy')
f.seek(0)
cursor = conn.cursor()
cursor.copy_expert(f"""COPY my_table ({','.join(columns)}) FROM STDIN WITH (FORMAT CSV)""", f)
conn.commit()