From: | Cravan <savageapple850(at)gmail(dot)com> |
---|---|
To: | Mark Wallace <mwallace(at)dataxdesign(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: ERROR when inserting csv values into sql table |
Date: | 2019-06-13 11:35:26 |
Message-ID: | DD623D41-68F1-430F-8160-4D230CE6C2CA@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi Mark,
It didn’t work, and I made some minor changes to my code as well.
Thanks,
Cravan
From: Mark Wallace <mwallace(at)dataxdesign(dot)com>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Cc: cool kid <savageapple850(at)gmail(dot)com>
Subject: Re: ERROR when inserting csv values into sql table
As Anthony E. Greene already mentioned yesterday (06/12/19 @ 08:07am EDT) in a comment on this thread, you are data typing imdbRating as INTEGER, when it should be NUMERIC.
Try again with this DDL:
CREATE TABLE movies (
"title" SERIAL PRIMARY KEY,
"year" INTEGER NOT NULL,
"runtime" INTEGER NOT NULL,
"imdbID" VARCHAR NOT NULL,
"imdbRating” NUMERIC NOT NULL
);
Mark
On Jun 13, 2019, at 03:40, Cravan <savageapple850(at)gmail(dot)com> wrote:
Hi guys,
Have edited the code, but Im still getting an error. 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 <— The last value (7.8) is of type numeric, not integer.
This is my main code
import csv
import sys
import os
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import create_engine
from flask import Flask, render_template, request, session
from flask_sqlalchemy import SQLAlchemy
from flask_session import Session
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
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]
engine.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 })
engine.commit() # transactions are assumed, so close the transaction finished
if __name__ == "__main__":
main()
SQL code:
CREATE TABLE movies (
"title" SERIAL PRIMARY KEY,
"year" INTEGER NOT NULL,
"runtime" INTEGER NOT NULL,
"imdbID" VARCHAR NOT NULL,
"imdbRating" INTEGER NOT NULL
);
New error code:
#############################
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-
packages/sqlalchemy/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/sqlalchemy/engine/default.py", line 550, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column "title" does not exist
LINE 1: ...,"year","runtime","imdbID","imdbRating") VALUES ((title), (y...
^
HINT: There is a column named "title" in table "movies", but it cannot be re
ferenced from this part of the query.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "import.py", line 26, in <module>
main()
File "import.py", line 23, in main
{"title": title, "year": year, "runtime": runtime, "imdbID": imdbID, "imd
bRating": imdbRating })
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-
packages/sqlalchemy/engine/base.py", line 2166, in execute
return connection.execute(statement, *multiparams, **params)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-
packages/sqlalchemy/engine/base.py", line 982, in execute
return self._execute_text(object_, multiparams, params)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-
packages/sqlalchemy/engine/base.py", line 1155, in _execute_text
parameters,
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-
packages/sqlalchemy/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/sqlalchemy/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/sqlalchemy/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/sqlalchemy/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/sqlalchemy/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/sqlalchemy/engine/default.py", line 550, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "ti
tle" does not exist
LINE 1: ...,"year","runtime","imdbID","imdbRating") VALUES ((title), (y...
^
HINT: There is a column named "title" in table "movies", but it cannot be re
ferenced from this part of the query.
[SQL: INSERT INTO movies("title","year","runtime","imdbID","imdbRating") VALU
ES ((title), (year), (runtime), (imdbID), (imdbRating))]
[parameters: {'title': 'Title', 'year': 'Year', 'runtime': 'Runtime', 'imdbID
': 'imdbID', 'imdbRating': 'imdbRating\n'}]
(Background on this error at: http://sqlalche.me/e/f405)
Here’s a pic of the new table:
<image001.png>
Thanks,
Cravan
Attachment | Content-Type | Size |
---|---|---|
import.py | text/x-python-script | 1.2 KB |
summative3.sql | application/octet-stream | 197 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Adarsh Jaiswal | 2019-06-13 12:03:21 | Re: ERROR when inserting csv values into sql table |
Previous Message | Mark Wallace | 2019-06-13 11:31:13 | Re: ERROR when inserting csv values into sql table |