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.

Community · Accepted Answer · 2017-05-23 12:15:39Z

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()

Collectives™ on Stack Overflow

Psycopg2 copy_from throws DataError: invalid input syntax for integer

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related