-1

I'm trying to run a program to insert data from a CSV. file into a PostgreSQL database.

db.execute("INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)",
            {"isbn": isbn, "title": title, "author": author, "year": year})

The code crashed and returned this:

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation)
   invalid input syntax for type integer: "isbn" LINE 1: ...RT INTO
   books (isbn, title, author, year) VALUES ('isbn', 't...
                                                                ^

   [SQL: INSERT INTO books (isbn, title, author, year) VALUES (%(isbn)s,
   %(title)s, %(author)s, %(year)s)] [parameters: {'isbn': 'isbn',
   'title': 'title', 'author': 'author', 'year': 'year'}] (Background on
   this error at: http://sqlalche.me/e/9h9h)

I took the syntax from the import.py source code and modified the values.

The format of my books table is:

  • id SERIAL PRIMARY KEY
  • isbn INTEGER PRIMARY KEY
  • title VARCHAR NOT NULL
  • author VARCHAR NOT NULL
  • year INTEGER NOT NULL

Can anyone tell me what is wrong with my syntax?

Improve this question
4
  • 1
    the exception occurs when you are trying to insert string into an integer field isbn Commented May 21, 2020 at 9:33
  • 1
    As @HumayunM states you are most likely trying to insert a string in an integer field. But apart from that also strongly re-consider the choice of integer for ISBN to begin with. Two points, is it 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? Commented May 21, 2020 at 9:39
  • %s is a string format operator in pythong and as per you error the ISBN is a integer field in your database table. %d is for format of integer. I would also ask you to reconsider what @RohdeF has said in his comment. Commented May 21, 2020 at 9:42
  • I considered it and it makes sense. yes it was a string and ISBN can stay as a string as well as the year as I will not need to make integer operations with any of them any time in the future, and it is possible that isbn will have letters. Commented May 21, 2020 at 9:44

1 Answer 1

Reset to default
0

As @HumayunM states it's probably because you're trying to insert a string, which leaves you with two immediate options.

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.

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

1 Comment

In addition to changing the type, it also helps if the header row of the source CSV is skipped :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.