Thread: RE: ERROR when inserting csv values into sql table

RE: ERROR when inserting csv values into sql table

From

Cravan

Date:

13 June 2019, 10:40:56

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

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:

Thanks,

Cravan

Attachment

image001.png

Re: ERROR when inserting csv values into sql table

From

Mark Wallace

Date:

13 June 2019, 14:31:13

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@gmail.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

Re: ERROR when inserting csv values into sql table

From

Cravan

Date:

13 June 2019, 14:35:26

Hi Mark,

It didn’t work, and I made some minor changes to my code as well.

Thanks,

Cravan

From: Mark Wallace <mwallace@dataxdesign.com>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Cc: cool kid <savageapple850@gmail.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@gmail.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

Re: ERROR when inserting csv values into sql table

From

Adarsh Jaiswal

Date:

13 June 2019, 15:03:21

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@gmail.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@dataxdesign.com>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Cc: cool kid <savageapple850@gmail.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@gmail.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

Re: ERROR when inserting csv values into sql table

From

Cravan

Date:

13 June 2019, 15:05:55

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

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

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

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

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,

Cravan

From: Adarsh Jaiswal <adarshjaiswal1989@gmail.com>
Date: Thursday, 13 June 2019 at 8:03 PM
To: cool kid <savageapple850@gmail.com>
Cc: Mark Wallace <mwallace@dataxdesign.com>, "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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@gmail.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@dataxdesign.com>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Cc: cool kid <savageapple850@gmail.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@gmail.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

Re: ERROR when inserting csv values into sql table

From

Bzzzz

Date:

13 June 2019, 15:19:06

On Thu, 13 Jun 2019 20:05:55 +0800
Cravan <savageapple850@gmail.com> wrote:

> Hi all,

Hi Cravan,

first things first : do you _really_ (I mean really, really, really)
*need* to use columns names with capital letters ?
'cos this is the kinda thing that can easily turn to troubles
comparing to put all names in lowercase.

I did not use sqlalchemy for a long time but I remember of this kind of
quirks (don't remember the solving, though, may be inverting single &
double quotes &| put all names in uppercase.)

Jean-Yves

RE: ERROR when inserting csv values into sql table

From

David Raymond

Date:

13 June 2019, 17:08:32

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

You used single quotes for the part with column names. Double quotes are for identifies, single quotes are string
literals.So it looks like it need to be
 

insert_statement = sqlalchemy.text('INSERT INTO movies(title, year, runtime, "imdbID", "imdbRating") VALUES (:title,
:year,:runtime, :imdbID, :imdbRating)')

Re: ERROR when inserting csv values into sql table

From

"Anthony E. Greene"

Date:

14 June 2019, 23:20:26

On 13-Jun-2019/14:19 +0200, Bzzzz <lazyvirus@gmx.com> wrote:
>On Thu, 13 Jun 2019 20:05:55 +0800
>Cravan <savageapple850@gmail.com> wrote:
>
>> Hi all,
>
>Hi Cravan,
>
>first things first : do you _really_ (I mean really, really, really)
>*need* to use columns names with capital letters ?
>'cos this is the kinda thing that can easily turn to troubles
>comparing to put all names in lowercase.
>

Cravan,

I use Perl, not Python, so your experience may differ. But I've been using
PostgreSQL for a few years and found that life is simpler if you
consistently use lowercase identifiers (e.g. table and column names). It
reduces the potential for quoting problems when troubleshooting.

-- 
Anthony E. Greene <mailto:agreene@pobox.com>