(psycopg2.DataError) invalid input syntax for integer: importing from csv file?

Question

the data in my csv file is like this:

081299289X,China Dolls,Lisa See,2014
0345498127,Starter for Ten,David Nicholls,2003
0061053716,Imajica,Clive Barker,1991
0553262149,Emily Climbs,L.M. Montgomery,1925

my import.py is like this:

import csv
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('postgres://pnrvnavoxvnlgw:....')
db = scoped_session(sessionmaker(bind=engine))

def main():
    f = open("books.csv")
    reader = csv.reader(f)
    for isbn, title, author, year in reader:
        db.execute(
            "INSERT INTO books (isbn, title, author, publication_year)
               VALUES (:isbn, :title, :author, :publication_year)",
            {"isbn": isbn, "title": title, "author": author, "publication_year": year}
        )
    db.commit()

if __name__ == "__main__":
    main()

For some reason I cannot seem to see what's wrong with this code. this is the error:

sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for integer: "year"
LINE 1: ...publication_year) VALUES ('isbn', 'title', 'author', 'year')

Help?

Ilja Everilä · Accepted Answer · 2018-09-21 14:57:52Z

5

From the looks of it your CSV contains a header as the first row. Skip it with for example

next(reader, None)

before your for-loop.

answered Sep 21, 2018 at 14:57

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rohde Fischer · Accepted Answer · 2020-05-25 08:34:20Z

0

As answered on a similar question. I cannot see the header from Ilja Everilä's answer, but of course if there are headers, Everilä is completely right, you need to skip it (or perhaps make it into a map).

My answer propose a general design improvement, since ISBN should probably not be a number that said if there is a header, be aware that this change will hide that error until you inspect the data.

Change database schema (probably preferred)

Change the ISBN type to varchar(255)

Reasoning for preference: Is ISBN actually an integer? I.e. would you ever perform integer operations on it as addition? Or is is an ID that just happens to currently be formatted as numbers? Also ISBN is an ID format outside your control. Do you really want you application to be tightly coupled to the current formatting? What will happen if ISBN changes to include letters in the future?

Alternative, convert the string

Alternatively you can just convert isbn:

isbnAsNumber = int(isbn.strip().replace("-", ""))
db.execute("INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)",
            {"isbn": isbnAsNumber, "title": title, "author": author, "year": year})

But I would consider this a dirty solution compared to changing the logical type, as suggested in the first solution.

answered May 25, 2020 at 8:34

Rohde Fischer

1,5023 gold badges12 silver badges34 bronze badges

1 Comment

Ilja Everilä Over a year ago

Noteworthy that in this particular question the ISBN likely is a text column already, since the sample CSV contains an obvious non-integer 081299289X, and it is the year column that fails here. If by "cannot see the header" you're referring to the sample data, it's not there, but the VALUES clause in the error message gave it up.

Collectives™ on Stack Overflow

(psycopg2.DataError) invalid input syntax for integer: importing from csv file?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related