CODEWITHSUNDEEP
pythonpostgresqlpython-3.xcsv
EarlyCoder
EarlyCoder

Reputation: 1313

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

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.

Upvotes: 1

Views: 583

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125434

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

Upvotes: 1

Related Questions