What is the correct syntax for mixed integers and letters in sqlalchemy to avoid Data Error

Question

trying to import csv file into table in postgresql database on heroku, i am getting an error message: sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "isbn". DATABASE_URL is set correctly. Here's my python code:

import csv
import os

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

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

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

if __name__ == "__main__":
    main()

my table, books, has isbn, title, author, and year. isbn is VARCHAR NOT NULL, as is title, and author. year is INTEGER. isbn numbers have a letter at the end, so what is valid input syntax?

Can you do a print(os.getenv("DATABASE_URL")) to see if it returns a value? — darcMadder

joe mac joe mac · Accepted Answer · 2020-06-25T14:57:30

so, i'm nearly falling asleep last night when it hits me that the isbn number in my table should not be serial, as the numbers aren't sequential, and being a mix of letters and integers, it's a varchar, where i had used integer. after dropping my first table and creating a new one with the right attributes, my import.py program worked perfectly. this is how i fixed it:

CREATE TABLE books (isbn VARCHAR PRIMARY KEY,title VARCHAR NOT NULL,author VARCHAR NOT NULL,year INTEGER NOT NULL);

an interesting puzzle solved.

What is the correct syntax for mixed integers and letters in sqlalchemy to avoid Data Error

1 Answers