Thread

1. RE: ERROR when inserting csv values into sql table

   Cravan <savageapple850@gmail.com> — 2019-06-13T07:40:56Z

   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
   
   

2. Re: ERROR when inserting csv values into sql table

   Mark Wallace <mwallace@dataxdesign.com> — 2019-06-13T11:31:13Z

   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: <http://sqlalche.me/e/f405>//sqlalche.me/e/f405 <http://sqlalche.me/e/f405>)
   > Here’s a pic of the new table:
   > <image001.png>
   > Thanks,
   > Cravan
   
   

3. Re: ERROR when inserting csv values into sql table

   Cravan <savageapple850@gmail.com> — 2019-06-13T11:35:26Z

   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
   
    
   
   

4. Re: ERROR when inserting csv values into sql table

   Adarsh Jaiswal <adarshjaiswal1989@gmail.com> — 2019-06-13T12:03:21Z

   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: <http://sqlalche.me/e/f405>
   > //sqlalche.me/e/f405 <http://sqlalche.me/e/f405>)
   >
   > Here’s a pic of the new table:
   >
   > <image001.png>
   >
   > Thanks,
   >
   > Cravan
   >
   >
   >
   

5. Re: ERROR when inserting csv values into sql table

   Cravan <savageapple850@gmail.com> — 2019-06-13T12:05:55Z

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

6. Re: ERROR when inserting csv values into sql table

   Bzzzz <lazyvirus@gmx.com> — 2019-06-13T12:19:06Z

   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
   
   
   
   

7. RE: ERROR when inserting csv values into sql table

   David Raymond <david.raymond@tomtom.com> — 2019-06-13T14:08:32Z

   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)')
   

8. Re: ERROR when inserting csv values into sql table

   Anthony E . Greene <agreene@pobox.com> — 2019-06-14T20:20:26Z

   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>