psycopg2 copy_expert() - how to copy in a gzipped csv file?
<p>If my table is schema_one.table_five and my file name is file_to_import.csv.gz, what args do I give the copy_expert() cmd in order to copy the file contents into the table?</p>

<p>Here's what I'm trying:</p>

<pre><code>this_copy = '''COPY schema_one.table_five FROM STDIN with CSV'''
this_file = "file_to_import.csv.gz"
con = psycopg2.connect(dbname=dbname, host=host, port=port, user=user, password=password)
cur = con.cursor()

cur.copy_expert(this_copy, this_file)
</code></pre>

<p>This produces an error:</p>

<pre><code>cur.copy_expert(this_copy, this_file) 
TypeError: file must be a readable file-like object for COPY FROM; a writable file-like object for COPY TO.
</code></pre>

<p>So how do I tell the command to first uncompress the file and then specify a delimiter (in this case '|') so that it can be processed.</p>

<p>Secondary question.  If my file is in a directory called "files_to_import" i.e. /home/dir1/dir2/files_to_import/file_to_import.csv.gz, is there a way that I can specify just the directory and have the pgm copy in all the files in that dir (to the same table)?  They would all be .csv.gz files.</p>

<hr>

<p><strong>Added 12-30-16 0940 MST</strong> -- In response to comment:
Trying to get COPY statement right, but all these error ---</p>

<pre><code>this_file = "staging.tbl_testcopy.csv.gz"
this_copy_01 = '''COPY staging.tbl_testcopy_tmp FROM STDIN'''
this_copy_02 = '''COPY staging.tbl_testcopy_tmp FROM %s'''
this_copy_03 = '''COPY staging.tbl_testcopy_tmp FROM (%s)'''
this_copy_04 = '''COPY staging.tbl_testcopy_tmp FROM f'''

with gzip.open(this_file, 'rb') as f:
    try:
        cur.copy_expert(this_copy_01, f)
    except Exception, e:
        print e
    try:
        cur.copy_expert(this_copy_02, f)
    except Exception, e:
        print e
    try:
        cur.copy_expert(this_copy_03, f)
    except Exception, e:
        print e
    try:
        cur.copy_expert(this_copy_04, f)
    except Exception, e:
        print e
</code></pre>

<p>All of these error, at the same place.  So what <strong><em>should</em></strong> come after 'FROM' ?</p>

<pre><code>syntax error at or near "STDIN"
LINE 1: COPY staging.tbl_testcopy_tmp FROM STDIN
                                           ^

syntax error at or near "%"
LINE 1: COPY staging.tbl_testcopy_tmp FROM %s
                                           ^

syntax error at or near "("
LINE 1: COPY staging.tbl_testcopy_tmp FROM (%s)
                                           ^

syntax error at or near "f"
LINE 1: COPY staging.tbl_testcopy_tmp FROM f
                                           ^
</code></pre>