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; 4 Answers
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() 
