RE: ERROR when inserting csv values into sql table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

RE: ERROR when inserting csv values into sql table

Cravan

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

Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Mark Wallace
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Cravan

Hi Mark,

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

Thanks,

Cravan

 

From: Mark Wallace <[hidden email]>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "[hidden email]" <[hidden email]>
Cc: cool kid <[hidden email]>
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 <[hidden email]> 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

 


import.py (1K) Download Attachment
summative3.sql (272 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Adarsh Jaiswal
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 <[hidden email]> wrote:

Hi Mark,

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

Thanks,

Cravan

 

From: Mark Wallace <[hidden email]>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "[hidden email]" <[hidden email]>
Cc: cool kid <[hidden email]>
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 <[hidden email]> 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

 

Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Cravan

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 <[hidden email]>
Date: Thursday, 13 June 2019 at 8:03 PM
To: cool kid <[hidden email]>
Cc: Mark Wallace <[hidden email]>, "[hidden email]" <[hidden email]>
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 <[hidden email]> wrote:

Hi Mark,

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

Thanks,

Cravan

 

From: Mark Wallace <[hidden email]>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "[hidden email]" <[hidden email]>
Cc: cool kid <[hidden email]>
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 <[hidden email]> 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

 


summative3.sql (272 bytes) Download Attachment
import.py (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Bzzzz
On Thu, 13 Jun 2019 20:05:55 +0800
Cravan <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: ERROR when inserting csv values into sql table

David Raymond
In reply to this post by Cravan
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)')
Reply | Threaded
Open this post in threaded view
|

Re: ERROR when inserting csv values into sql table

Anthony E. Greene
In reply to this post by Bzzzz
On 13-Jun-2019/14:19 +0200, Bzzzz <[hidden email]> wrote:

>On Thu, 13 Jun 2019 20:05:55 +0800
>Cravan <[hidden email]> 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:[hidden email]>