Last Updated: February 25, 2016
·
1.461K
· datasaur

Simple bulk import automation using psql COPY

#!/usr/bin/env python
import os

filepath = '/Users/mattk/exports/Export_20100831'

srcpath = "'/home/postgres/imports"
#srcpath = ':srcpath'

files = os.listdir(filepath)
for file in files:
    table,ext = os.path.splitext(file)
    pfx,table = table.split('_')

    if pfx == 'export' and ext == '.csv':
        src = os.path.join(filepath,file)
        header = open(src,'r').readline()
        header = header.replace('"','').replace('|',',').strip()

        # For psql :variables, leading quote is purposefully left out, later added by loader shell script:
        psql = "TRUNCATE TABLE %s;\nCOPY %s (%s)\nFROM %s'\nWITH DELIMITER AS '|' CSV HEADER;\n" % (table, table, header, os.path.join(srcpath,file))

        print psql