Error when using copy_expert(sql, file) in psycopg2 to insert data from several CSV

[klllmmm]

I'm a Newby,
I tried to insert columnar data sets in several CSV files into PostgreSQL database.
I tried this using copy_expert(sql, file) in psycopg2 as explained in this website .
However i got an error stating;

 E:\directory\path\Report1.csv
Traceback (most recent call last):
  File "E:\Shared Folder Home\Python\Python3\postgreSQLDBConnect2.py", line 33, in <module>
    process_csv(connection, site, f)
  File "E:\Shared Folder Home\Python\Python3\postgreSQLDBConnect2.py", line 22, in process_csv
    cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
TypeError: file must be a readable file-like object for COPY FROM; a writable file-like object for COPY TO.

python code i used is as follows;

#!/usr/bin/python3.5
# -*- coding: utf8 -*-

import os, glob, psycopg2, shutil, sys
 
path = 'E:\\directory\\path\\'
arc_path = 'E:\\directory\\arc\path\\'
site = 'a location'
db_conn = "dbname='testdb' user='postgres' host='localhost' password='password'"
connection = psycopg2.connect(db_conn)
 
SQL_STATEMENT = """
    COPY %s FROM STDIN WITH
    CSV
    HEADER
    DELIMITER AS ','
    """
 
def process_csv(conn, table_name, file_object):
    cursor = conn.cursor()
    cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
    conn.commit()
    cursor.close()

if not os.path.exists(arc_path):
    os.makedirs(arc_path)
 
file_list = glob.glob(path + "*.csv")
 
for f in file_list:
    print(f)
    process_csv(connection, site, f)
    shutil.move(f, arc_path)
 
connection.close()

Can some help to solve this error? Thank you very much for your time & effort.

[Larz60+]

Hello,

The error trace is telling you that the file in file_object is not a f

file must be a readable file-like object for COPY FROM; a writable file-like object for COPY TO

you print out the value of f before calling process_csv,
since the error trace is complaining about the type of f, not being readable,
i'd change your print statement

# change from
for f in file_list:
    print(f)
    process_csv(connection, site, f)
    shutil.move(f, arc_path)

# To
for f in file_list:
    print(f, type(f))
    process_csv(connection, site, f)
    shutil.move(f, arc_path)

Please send new traceback and include this printout

Larz60+

[klllmmm]

@Larz60+, Thank you very much for the reply.
I made the changes to code as you suggested.

However the same error is still comes up.

[Yoriz]

It looks like the f that is being passed to the function

process_csv

is a string file path and it is expecting an actual file object instead.
Try opening the file path and sending the function the returned file object, then close the file object at the end of the loop(or better use a with statement to open the file).

Maybe something like

for file_path in file_list:
    print(file_path)
    with open(file_path) as file_obj:
        process_csv(connection, site, file_obj)
        shutil.move(file_path, arc_path)

[Ofnuts]

@Larz60+, Thank you very much for the reply.
I made the changes to code as you suggested.

However the same error is still comes up.

Implicit in Larz60+'s answer was to also check what is printed by the added code, and in particular if your variable is a file object or not.

The difference between you the beginner and more experienced users like us is that we learned 1) to read error messages (because they tell the truth) and 2) to add adequate print statements in our code to figure out where things are not what we think they are.

[klllmmm]

It looks like the f that is being passed to the function

process_csv

is a string file path and it is expecting an actual file object instead.
Try opening the file path and sending the function the returned file object, then close the file object at the end of the loop(or better use a with statement to open the file).

Maybe something like

for file_path in file_list:
    print(file_path)
    with open(file_path) as file_obj:
        process_csv(connection, site, file_obj)
        shutil.move(file_path, arc_path)

Thanks for the reply, I tried your suggestion. Now the error message changed to:

E:\directory\path\Report1.csv
Traceback (most recent call last):
  File "E:\Shared Folder Home\Python\Python3\postgresqlQues.py", line 34, in <module>
    process_csv(connection, site, file_obj)
  File "E:\Shared Folder Home\Python\Python3\postgresqlQues.py", line 22, in process_csv
    cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
psycopg2.ProgrammingError: syntax error at or near "location"
LINE 2:     COPY a location FROM STDIN WITH
                   ^