0

I'm on a project1, here is the link to it: https://docs.cs50.net/ocw/web/projects/1/project1.html

I've successfully made a Table books using my Python script, however when I try to import the "books.csv" file into the table a Postgres SQL error occurs.

Here is my 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():
    f = open("books.csv")
    reader = csv.reader(f)
    next(reader, None)
    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()

The error message:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
psycopg2.DataError: invalid input syntax for integer: "080213825X"
LINE 1: ...RT INTO books (isbn, title, author, year) VALUES ('080213825...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "import.py", line 22, in <module>
    main()
  File "import.py", line 17, in main
    {"isbn": isbn, "title": title, "author": author, "year": year}
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1269, in execute
    clause, params or {}
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for integer: "080213825X"
LINE 1: ...RT INTO books (isbn, title, author, year) VALUES ('080213825...
                                                             ^

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

I've found a similar question here https://stackoverflow.com/questions/53359079/cs50-web-programming-postgres-sql-error-while-importing-books-csv-file The solution was "I remade the tables using adminer.cs50.net and I was able to fill the columns with csv data" I tried doing this but it didn't help me.

Improve this question

2 Answers 2

Reset to default
1

Interesting output: invalid input syntax for integer: "080213825X"

ISBN are not always numeric, at least not their check digit. The result of some calculation is taken mod 11, resulting in a number between 0 and 10 (both inclusive), the extra digit is the letter X. The import you wrote complains that you use an integer field to store a non-numeric ISBN. Use some kind of text field instead.

Improve this answer
2
  • Hi, its taking more than 5 minutes to insert rows using python way. It's very slow for me . Have you faced the same problem or it was fast for you ?
    – Prashant Singh
    Commented Dec 6, 2019 at 22:43
  • @PrashantSingh I've got the same problem
    – sceunazhel
    Commented Oct 17, 2020 at 7:11
0

can anyone please give a hand to regarding the issue of importing csv file into csv files into admin.cs50.net.... I really don't get it at all and have been struglling to fix it for two months::((((...

I click import button for improting my csv file on the admin.cs50.net but then the page closes or not appear at all and I refresh the page then again asks me to fill up the blanks for logging in...this loops itslef like this...

Can any volunteer show or give a tip the way how it works to import csv files into the net... Thanks really a lot...

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .