psycopg2 copy_expert() - how to copy in a gzipped csv file?

Question

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?

Here's what I'm trying:

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)

This produces an error:

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.

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.

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.

---

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

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

All of these error, at the same place. So what should come after 'FROM' ?

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
                                           ^

Answer 1

The file argument to copy_expert should be a file like object, not the file name. For a regular csv file you could use:

with open("file_to_import.csv",  'rb') as this_file:
    cur.copy_expert(this_copy, this_file)

For a gzipped file you could use the gzip module to open the file:

import gzip
with gzip.open("file_to_import.csv.gz",  'rb') as this_file:
    cur.copy_expert(this_copy, this_file)

To change the separator, you'll have to change the COPY statement. See the COPY docs for more information. It might be easier to use copy_from (which has a optional sep argument) instead of copy_expert.

with gzip.open("file_to_import.csv.gz",  'rb') as this_file:
    cur.copy_from(this_file, 'staging.tbl_testcopy_tmp', sep='|')

There isn't a command to automatically import all the files in the directory, you'll have to get a listing of the directory's contents and loop through it.