It's worth pointing out that your first and last errors are database
errors from postgresql, whereas the middle syntax error was a Python
error.
In Python, single and double-quotes are interchangeable, and you can
use whichever is more convenient for you. This is not true for
postgresql. In PG, single quotes are used for strings, and
double-quotes are used for identifiers that you want to be treated
literally (eg. because they contain characters that aren't normally
allowed in identifiers, or because you want them to be
case-sensitive).
So in this case, you need imdbID and imdbRating to be surrounded by
double-quotes because you want them to be handled as case-sensitive.
The easiest thing to do is to change the quotes around the whole SQL
statement to be single quotes.
ie.
sqlalchemy.text('INSERT INTO movies(title, year, runtime, "imdbID",
"imdbRating") VALUES(...)')
Simon
On Thu, Jun 13, 2019 at 12:40 PM Cravan <cravan...@gmail.com> wrote:
>
> Alright, thanks because I created my sql column names with imdbID, which
> contains caps.
> However, after changing it to single-quotes, I got 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,
> Cravan
>
>
>
> On 13/6/19, 7:36 PM, "Simon King" <sqlalchemy@googlegroups.com on behalf of
> si...@simonking.org.uk> wrote:
>
> You've got double-quotes around "imdbID" and "imdbRating" on that
> line, and double-quotes around the SQL statement, which is a syntax
> error. I think you should be able to just remove the quotes around the
> column names, but if you really need them for some reason, replace the
> outer double-quotes with single-quotes.
>
> Simon
>
> On Thu, Jun 13, 2019 at 12:20 PM Cravan <cravan...@gmail.com> wrote:
> >
> > I got a new error after using Simon's suggestion, would someone help me?
> > ######################################
> > File "import.py", line 24
> > insert_statement = sqlalchemy.text("INSERT INTO movies(title, year,
> > runtime, "imdbID", "imdbRating") VALUES (:title, :year, :runtime, :imd
> > bID, :imdbRating)")
> >
> > ^
> > SyntaxError: invalid syntax
> > Thanks,
> > Cravan
> > On 13/6/19, 7:10 PM, "Simon King" <sqlalchemy@googlegroups.com on
> behalf of si...@simonking.org.uk> wrote:
> >
> > Your "engine.execute" statement looks wrong to me:
> >
> > 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 })
> >
> > In particular, the VALUES clause is wrong. That's not the way that
> you
> > specify parameters when executing a parametrized query, so the
> > database is interpreting "(title)" as a reference to a column,
> rather
> > than a reference to a parameter.
> >
> > You're probably best off using SQLAlchemy's text() construct:
> >
> >
> https://docs.sqlalchemy.org/en/13/core/tutorial.html#using-textual-sql
> >
> > You would put this outside the loop:
> >
> > insert_statement = sqlalchemy.text(
> > "INSERT INTO movies(title, year, runtime, imdbID, imdbRating)
> > VALUES (:title, :year, :runtime, :imdbID, :imdbRating)"
> > )
> >
> > and then inside the loop you would use:
> >
> > engine.execute(insert_statement, title=title, year=year,
> > runtime=runtime, imdbID=imdbID, imdbRating=imdbRating)
> >
> > Also, I suspect (but haven't verified) that "engine.execute()" will
> > check out a connection from the pool, run the statement, and commit
> a
> > transaction. If you want to import all the rows in a single
> > transaction, you should probably explicitly create a connection at
> the
> > beginning, use "connection.execute()" to run your SQL, and commit it
> > at the end. See
> >
> https://docs.sqlalchemy.org/en/13/core/connections.html#using-transactions
> > for examples.
> >
> > Hope that helps,
> >
> > Simon
> >
> >
> > On Thu, Jun 13, 2019 at 11:15 AM Cravan <cravan...@gmail.com> wrote:
> > >
> > > I'm getting a weird error code when I try to store values from a
> csv into an sql table in a movie review assignment.
> > >
> > > 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, delimiter=';')
> > > for i, row in enumerate(reader): # loop gives each column a
> name
> > > if i == 0:
> > > continue
> > > title = row[0]
> > > year = int(row[1])
> > > runtime = int(row[2])
> > > imdbID = row[3]
> > > imdbRating = float(row[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)
> > >
> > > --
> > > SQLAlchemy -
> > > The Python SQL Toolkit and Object Relational Mapper
> > >
> > > http://www.sqlalchemy.org/
> > >
> > > To post example code, please provide an MCVE: Minimal, Complete,
> and Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > > ---
> > > You received this message because you are subscribed to the
> Google Groups "sqlalchemy" group.
> > > To unsubscribe from this group and stop receiving emails from it,
> send an email to sqlalchemy+unsubscr...@googlegroups.com.
> > > To post to this group, send email to sqlalchemy@googlegroups.com.
> > > Visit this group at https://groups.google.com/group/sqlalchemy.
> > > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/ca1b1197-3f05-4572-a11a-fda4e08d27f3%40googlegroups.com.
> > > For more options, visit https://groups.google.com/d/optout.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete,
> and Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it,
> send an email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcqMjc_vUtNhx-dpyS6ECK5TSDeObDEGzeOFR1n35JkdQ%40mail.gmail.com.
> > For more options, visit https://groups.google.com/d/optout.
> >
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/2E8C06E7-BD2D-408A-BFE4-6657C5270CF5%40gmail.com.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexexCE%3DpS-wP6Nxbd2znhesjwtuzt1zjodPytdiKF-x7uQ%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/4E9096F6-7FD9-4AAF-A045-0DBCE352FE9F%40gmail.com.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeK05VEPLk1cLH2iq0tSzpunWViNd2DRCDLZdQi_H85xA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
