Re: ERROR when inserting csv values into sql table

Hi all,

I made another few changes, the column problem was solved, now receiving a syntax error.

##################################

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_conte

xt

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.SyntaxError: syntax error at or near "'imdbID'"

LINE 1: INSERT INTO movies(title, year, runtime, 'imdbID', 'imdbRati...

^

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

Traceback (most recent call last):

File "import.py", line 28, in <module>

main()

File "import.py", line 25, in main

engine.execute(insert_statement, title=title, year=year, runtime=ru

ntime, imdbID=imdbID, imdbRating=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 988, in execute

return meth(self, multiparams, params)

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7

/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_co

nnection

return connection._execute_clauseelement(self, multiparams, params)

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7

/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_claus

eelement

distilled_params,

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7

/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_conte

xt

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_caus

e

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_conte

xt

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.SyntaxError) syntax e

rror at or near "'imdbID'"

LINE 1: INSERT INTO movies(title, year, runtime, 'imdbID', 'imdbRati...

[SQL: INSERT INTO movies(title, year, runtime, 'imdbID', 'imdbRating')

VALUES (%(title)s, %(year)s, %(runtime)s, %(imdbID)s, %(imdbRating)s)]

[parameters: {'title': 'The Lego Movie', 'year': 2014, 'runtime': 100,

'imdbID': 'tt1490017', 'imdbRating': 7.8}]

(Background on this error at: http://sqlalche.me/e/f405)

Thanks,

Thanks,

Cravan

From: Adarsh Jaiswal <adarshjaiswal1989(at)gmail(dot)com>
Date: Thursday, 13 June 2019 at 8:03 PM
To: cool kid <savageapple850(at)gmail(dot)com>
Cc: 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

Hi,

Just a very long shot, may be column name "title" is getting conflicted somehow. Try changing the column name to say "movie_name" ?
Thanks and Regards,

Adarsh Jaiswal

On Thu, Jun 13, 2019 at 5:05 PM Cravan <savageapple850(at)gmail(dot)com> wrote:

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