Since I tried all the possible variations with different codes and csv.reader dialects, my csv file has always problems with double quoted rows. I could not find the reason. If the rows has just standard commas and a string with spaces inside, it works but if there is comma or single quote inside a double quoted strings, it says that row has one item, not for as it should be. The expected list items for each row: isbn, title, author and year. I have read all the github solutions, all the codes nearly same and they all used simple csv.reader(f) without any special dialects but my code does not work. At what point I am wrong?
import csv
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine("postgresql://goga:0000@localhost/mydb")
db = scoped_session(sessionmaker(bind=engine))
def main():
db.execute("CREATE TABLE books (id SERIAL PRIMARY KEY, isbn VARCHAR NOT NULL, title VARCHAR NOT NULL, author VARCHAR NOT NULL, year VARCHAR NOT NULL)")
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})
print(f"Added book {isbn} : {title} : {author} : {year}.")
db.commit()
if __name__ == "__main__":
main()
error part:
Added book 0006161413 : Ice Station Zebra : Alistair MacLean : 1963.
Added book 0385491069 : The Edible Woman : Margaret Atwood : 1969.
Added book 0451208188 : The English Assassin : Daniel Silva : 2002.
Added book 0312942303 : Born of the Night : Sherrilyn Kenyon : 1996.
Added book 0385366434 : Two Ravens and One Crow : Kevin Hearne : 2012.
Added book 0345438310 : Nicholas and Alexandra : Robert K. Massie : 1967.
Added book 0060652381 : A Grief Observed : C.S. Lewis : 1961.
Traceback (most recent call last):
File "lucky.py", line 33, in <module>
main()
File "lucky.py", line 24, in main
for isbn, title, author, year in reader:
ValueError: not enough values to unpack (expected 4, got 1)
C:\Users\user\PycharmProjects\whichbook>
A sample of the CSV:
312349513,Fearless Fourteen,Janet Evanovich,2008
1561797464,A Christmas Carol,Charles Dickens,1843
767915593,Straight Talking,Jane Green,1997
1400078776,Never Let Me Go,Kazuo Ishiguro,2005
6161413,Ice Station Zebra,Alistair MacLean,1963
385491069,The Edible Woman,Margaret Atwood,1969
451208188,The English Assassin,Daniel Silva,2002
312942303,Born of the Night,Sherrilyn Kenyon,1996
385366434,Two Ravens and One Crow,Kevin Hearne,2012
345438310,Nicholas and Alexandra,Robert K. Massie,1967
60652381,A Grief Observed,C.S. Lewis,1961
140690483X,"Right Ho, Jeeves",P.G. Wodehouse,1934
743482883,Mr. Perfect,Linda Howard,2000
