Why my code python only can import some data from a file .CSV to my database PostgreSQL?

Question

Description and Objective

This is the project 1 of the CS50 Web programing's course. I need to import the content of a table from a file .csv to a table in my database PostgreSQL through Python. The table has the next format:

isbn,title,author,year
0380795272,Krondor: The Betrayal,Raymond E. Feist,1998

The columns of the table were created directly in my PostgreQSL database with the next data type:

id: Integer not null
isbn: Varchar not null
title: Text not null
author: Varchar not null
year: Integer not null

I have the next 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():
    f = open("bookspr1.csv")
    reader = csv.reader(f)
    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})
        print(f"Added the book {title}")
    db.commit()

if __name__ == "__main__":
    main()

Issue

When I run the python code to import the data table from the file .csv, the system throws an error:

    C:\xampp\htdocs\project1>python  import0_pr1A.py
Traceback (most recent call last):
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1284, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer
: "year"
LINE 1: ...le, author, year) VALUES ('isbn', 'title', 'author', 'year')
                                                                ^


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

Traceback (most recent call last):
  File "import0_pr1A.py", line 24, in <module>
    main()
  File "import0_pr1A.py", line 18, in main
    {"isbn": isbn, "title": title, "author": author, "year": year})
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\orm\scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\orm\session.py", line 1292, in execute
    clause, params or {}
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1139, in _execute_clauseelement
    distilled_params,
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1324, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1518, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\base.py", line 1284, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\Verel\AppData\Local\Programs\Python\Python37-32\lib\site-packag
es\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid in
put syntax for type integer: "year"
LINE 1: ...le, author, year) VALUES ('isbn', 'title', 'author', 'year')
                                                                ^

[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': 'yea
r'}]
(Background on this error at: http://sqlalche.me/e/9h9h)

C:\xampp\htdocs\project1>

In order to isolate the problem, I try to import the file .CSV but supriming the first row which include the names of the columns (isbn, title, author, year) and when I run the code, It starts the data transfer but It stops suddenly with another error when It try import a row where the data "title" or "author" contains Double quotes (" ") and Comma (,). For example the next row with the author "V.E. Schwab, Victoria Schwab" generate that conflict:

0765335344,Vicious,"V.E. Schwab, Victoria Schwab",2013

And the new error is like this:

C:\xampp\htdocs\project1>python  import0_pr1A.py
Added the book The Mark of Athena
Added the book Her Fearful Symmetry
Traceback (most recent call last):
  File "import0_pr1A.py", line 24, in <module>
    main()
  File "import0_pr1A.py", line 16, in main
    for isbn, title, author, year in reader:
ValueError: not enough values to unpack (expected 4, got 1)

C:\xampp\htdocs\project1>python  import0_pr1A.py

The data transfer is finished succesfully when the file .CSV is imported without the first row (isbn, title, author, year) and without data that contains Double quotes (" ") and Commas (,).

C:\xampp\htdocs\project1>python  import0_pr1A.py
Added the book The Lion's Game
Added the book The Rainmaker
Added the book Eleanor & Park

C:\xampp\htdocs\project1>python  import0_pr1A.py 



C:\xampp\htdocs\project1>python list0_pr1.py
Krondor: The Betrayal by Raymond E. Feist of 1998.
The Dark Is Rising by Susan Cooper of 1973.
The Black Unicorn  by Terry Brooks of 1987.
The Lion's Game by Nelson DeMille of 2000.
The Rainmaker by John Grisham of 1995.
Eleanor & Park by Rainbow Rowell of 2013.

C:\xampp\htdocs\project1>

Finally I tried inserting some code lines but the result was the same:

import psycopg2

reader.next

db.close()

import csv
import os
import psycopg2

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)
    reader.__next__
    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})
        print(f"Added the book {title}")
    db.commit()
    db.close()

if __name__ == "__main__":
    main()

Conclusion I need a help to modify this python code that let me import completly the file .csv including the first row and the data that contains Double Quotes (" ") and Commas (,).

jjanes · Accepted Answer · 2020-07-13 13:29:02Z

1

reader.__next__

This simply retrieves the method, it does not invoke the method. You need reader.__next__(), but I think next(reader) might be more conventional.

0765335344,Vicious,"V.E. Schwab, Victoria Schwab",2013

Works fine for me. Maybe your actual file has smart quotes or something like that rather than straight ASCII.

answered Jul 13, 2020 at 13:29

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

4 Comments

Christefs Over a year ago

Thanks for your input.The sentence next(reader) allowed me skip the first row with the names of the columns (isbn,title,author,year) but the transfer is still stopped when It finds data that contains Double quotes and comma and I need Import them to my PostgreSQL database.

Christefs Over a year ago

This is the new result: C:\xampp\htdocs\project1>python import0_pr1A.py Added the book The Mark of Athena Added the book Her Fearful Symmetry Traceback (most recent call last): File "import0_pr1A.py", line 24, in <module> main() File "import0_pr1A.py", line 16, in main for isbn, title, author, year in reader: ValueError: not enough values to unpack (expected 4, got 1) C:\xampp\htdocs\project1>

Christefs Over a year ago

The file .CSV have several data with Double Quotes and comma and It doesn't permit that all table can be imported succesfully.

Christefs Over a year ago

I have been rehearsing with some arguments for the reader() function to customize its working (skipinitialspace, quotechar, quoting, doublequote) but the result was the same. Suddenly I didn’t know how to parameterize these arguments.

Belayer · Accepted Answer · 2020-07-14 19:37:09Z

0

Try

csv.reader(lines, quotechar='"', delimiter=',', ...

see csv.reader also prior SO answer,

answered Jul 14, 2020 at 19:37

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

5 Comments

Christefs Over a year ago

Thanks for your input. I probed this on several ways but It stil doesn't works: reader = csv.reader(f, quotechar='"', delimiter=',', quoting=csv.QUOTE_ALL, skipinitialspace=True)

Belayer Over a year ago

Sorry then. My Python is weak but I thought it has a chance.

Christefs Over a year ago

It starts the import of data but It still stops suddenly due the data with double quotes and comma. C:\xampp\htdocs\project1>python import0_pr1A.py Added the book Nicholas and Alexandra Added the book A Grief Observed Traceback (most recent call last): File "import0_pr1A.py", line 25, in <module> main() File "import0_pr1A.py", line 17, in main for isbn, title, author, year in reader: ValueError: not enough values to unpack (expected 4, got 1)

Christefs Over a year ago

For example, this is the row where the transfer stops because the second data doesn't permit it: 140690483X,"Right Ho, Jeeves",P.G. Wodehouse,1934

Christefs Over a year ago

I think these arguments (quotechar, delimeter, quoting, skipinitialspace, delimeter, etc) could funtion but with another configuration.

Collectives™ on Stack Overflow

Why my code python only can import some data from a file .CSV to my database PostgreSQL?

2 Answers 2

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related