Copy (from) csv with heades in postgres with python

Question

I'm trying to fill a table from CSV files in a python script.

The SQL statement, which follows, runs without error:

COPY registro
FROM '/home/pablo/Escritorio/puntos/20140227.csv'
DELIMITER ','
CSV header;

CSV has headers, and using header parameter, it imports without error.

The problem comes when I execute it from my python script. The only way I've found not to try to import the headers is with copy_expert() method. I get no error message but the table is still empty after I run the Python script below.

Any possible clue? Or maybe any other way to copy a table from CSV with headers?

Thanks.

#/usr/bin/env python
# -*- coding: utf-8 -*-
import psycopg2
import os
import glob
DSN = "dbname=gps user=postgres host=localhost"
con = psycopg2.connect(DSN)
cur = con.cursor()
my_file = open('/home/pablo/Escritorio/puntos/20140227.csv')
#This is only a test file, not all the directory
sql = "COPY registro FROM stdin DELIMITER \',\' CSV header;"
cur.copy_expert(sql, my_file)
cur.close()
con.close()

Answer 1

I'd try con.commit() after cur.copy_expert().

Also I would avoid preprocessing and uploading the file row by row as Sam P. pointed out above if the dataset is large. cur.copy_expert() is significantly faster.

conn = psycopg2.connect('postgresql://scott:tiger@localhost:5432/database')
cur = conn.cursor()
copy_sql = """
           COPY table_name FROM stdin WITH CSV HEADER
           DELIMITER as ','
           """
with open(path, 'r') as f:
    cur.copy_expert(sql=copy_sql, file=f)
    conn.commit()
    cur.close()