Python: Error Importing data from CSV to Postgres (ERROR: invalid input syntax for integer:)

Question

I have written a piece of Python code that should copy the CSV data to the table I created to host the data. Here is the code:

def sql_copy_command(csv_file, schema, database, table, delimiter = ',', header = True):
    if header:
        sql_command = """COPY "{schema}_{tbl}"  FROM '{the_csv_file}'  DELIMITER '{dlm}' CSV HEADER;""".format(the_csv_file = csv_file, db = database, tbl = table, dlm = delimiter, schema = schema)

    else:
        sql_command = """COPY "{schema}_{tbl}"  FROM '{the_csv_file}' DELIMITER '{dlm}' CSV;""".format(the_csv_file = csv_file, db = database, tbl = table, dlm = delimiter, schema = schema)

    return sql_command

This throws the following error:

DataError: invalid input syntax for integer: "Visa"
CONTEXT:  COPY insight_transaction, line 2, column id: "Visa"

It seems to me that instead of the account_type, which is the first field in my model, postgres expects to see ID, which is the first column in the table. Given that the ID is automatically generated, I do not know how to address this in my code.

Clodoaldo Neto · Accepted Answer · 2017-07-03 17:17:23Z

1

Specify the columns to copy to:

copy my_schema.my_table (account_type, col5, col2) from 'the_csv_file' csv

https://www.postgresql.org/docs/current/static/sql-copy.html

answered Jul 3, 2017 at 17:17

Clodoaldo Neto

126k29 gold badges249 silver badges274 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Python: Error Importing data from CSV to Postgres (ERROR: invalid input syntax for integer:)

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related