Psycopg2 copy_from throws DataError: invalid input syntax for integer

Question

I have a table with some integer columns. I am using psycopg2's copy_from

conn = psycopg2.connect(database=the_database,
                            user="postgres",
                            password=PASSWORD,
                            host="",
                            port="")

print "Putting data in the table: Opened database successfully"
cur = conn.cursor()
with open(the_file, 'r') as f:
    cur.copy_from(file=f, table = the_table, sep=the_delimiter)
    conn.commit()
print "Successfully copied all data to the database!"
conn.close()

The error says that it expects the 8th column to be an integer and not a string. But, Python's write method can only read strings to the file. So, how would you import a file full of string representation of number to postgres table with columns that expect integer when your file can only have character representation of the integer (e.g. str(your_number)).

You either have to write numbers in integer format to the file (which Python's write method disallows) or psycopg2 should be smart enough to the conversion as part of copy_from procedure, which it apparently is not. Any idea is appreciated.

Answer 1

I ended up using copy_expert command. Note that on Windows, you have to set the permission of the file. This post is very useful setting permission.

with open(the_file, 'r') as f:            
        sql_copy_statement = "copy {table} FROM '"'{from_file}'"' DELIMITER '"'{deli}'"' {file_type} HEADER;".format(table = the_table,
                                                                                                                     from_file = the_file,
                                                                                                                     deli = the_delimiter,
                                                                                                                     file_type = the_file_type                                                                                                                                         
                                                                                                                    )
        print sql_copy_statement
        cur.copy_expert(sql_copy_statement, f)
        conn.commit()