Here is the stack overflow link:
https://stackoverflow.com/questions/56540292/error-when-trying-to-insert-csv-values-into-a-sql-table
I'm getting a weird error code when I try to store values from a csv into an
sql table in a movie review assignment.
I have already edited my apostrophes and spacing and looked up examples from
google to try and resolve my error to no avail. I also ensured that i defined
DATABASE_URL properly. Sorry for the long traceback error at the end :P Please
note that my csv values are stored in lists in each cell. They are arranged in
a single column such as
The Lego Movie;2014;100;tt1490017;7.8
This is my main code
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from
SQLAlchemy that manages connections to the database
# DATABASE_URL is an
environment variable that indicates where the database lives
db = scoped_session(sessionmaker(bind=engine))
def main():
f = open("movies.csv","r")
reader = csv.reader(f)
for row in f: # loop gives each column a name
vals = row.split(';')
title = vals[0]
year = vals[1]
runtime = vals[2]
imdbID = vals[3]
imdbRating = vals[4]
db.execute('INSERT INTO movies (Title, Year, Runtime, imdbID,
imdbRating) VALUES (:title, :year, :runtime, :imdbID, :imdbRating)',
{'title': title, 'year': year, 'runtime': runtime,
'imdbID': imdbID, 'imdbRating': imdbRating}) # substitute values from CSV line
into SQL command, as per this dict
print(f"Added movie named {title} of {year} lasting {runtime} minutes.
Its imdbID is {imdbID} and its imdbRating is {imdbRating}.")
This is the sql
CREATE TABLE movies (
Title SERIAL PRIMARY KEY,
Year INTEGER NOT NULL,
Runtime INTEGER NOT NULL
imdbID VARCHAR NOT NULL,
imdbRating INTEGER NOT NULL
);
This is the error i got:
Traceback (most recent call last):
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/default.py", line 550, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "movies" does not exist
LINE 1: INSERT INTO movies (Title, Year, Runtime, imdbID, imdbRating...
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "import.py", line 25, in <module>
main()
File "import.py", line 21, in main
{'title': title, 'year': year, 'runtime': runtime, 'imdbID': imdbID,
'imdbRating': imd
bRating}) # substitute values from CSV line into SQL command, as per this dict
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/orm/scoping.py", line 162, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/orm/session.py", line 1268, in execute
clause, params or {}
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/orm/session.py", line 1268, in execute
clause, params or {}
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sqla
lchemy/engine/default.py", line 550, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation
"movies" does n
ot exist
LINE 1: INSERT INTO movies (Title, Year, Runtime, imdbID, imdbRating...
^
[SQL: INSERT INTO movies (Title, Year, Runtime, imdbID, imdbRating) VALUES
(%(title)s, %(y
ear)s, %(runtime)s, %(imdbID)s, %(imdbRating)s)]
[parameters: {'title': 'Title', 'year': 'Year', 'runtime': 'Runtime', 'imdbID':
'imdbID',
'imdbRating': 'imdbRating\n'}]
(Background on this error at: http://sqlalche.me/e/f405)
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
