RE: Error when doing sql - Mailing list pgsql-novice
From | David Raymond |
---|---|
Subject | RE: Error when doing sql |
Date | |
Msg-id | VI1PR07MB5792BD2FEC7A11EB0B37CAA887EC0@VI1PR07MB5792.eurprd07.prod.outlook.com Whole thread Raw |
In response to | Re: Error when doing sql (Cravan <savageapple850@gmail.com>) |
List | pgsql-novice |
I _think_ your problem is now a Python problem where you're not correctly telling the execute method to bind the parameters. Your statement has INSERT INTO movies("Title", ..... VALUES ((title), (year), (runtime), (imdbID), (imdbRating)) and then you give it the dictionary of values. But what method does sqlachemy use to specify what is supposed to receivea bound parameter? I know using psycopg2 for example you'd do ... values (%s, %s, %s...) to specify where the valuesyou provide get bound. So I think it's not doing any binding, but literally just passing in the sql as-given, which is why "values ((title)..."has a problem, because it didn't get replaced by the Python library, and Postgres now thinks it's a column name. For the capitalization, Postgres is case-specific, but anything not in quotes gets turned into lowercase by the server beforeit gets run. (Someone will correct me if I'm not quite right here) So "create table FOO...;" becomes "create table foo...;" before it gets run by the server. "SELECT * FrOm fOO;" gets turned into "select * from foo;" by the server before it gets run, which matches the lowercasefoo that got created. So as long as table names are lowercase then it's "effectively case-insensitive" because you can tell it foo or Foo or FOOand it'll wind up matching. Some programs (like pgAdmin I've seen) don't just take your table name and send it unquoted when doing a create table viathe GUI. They go "ohh, you said you wanted Foo, so I'll quote it for you as "Foo" so it keeps the capital". While thisis sort of helpful, it winds up destroying the "effectively case-insensitive" nature of the lower-casing system, andleaves you forever needing to put things in quotes and making sure you have the right casing. This is why you get things like "I told it to create Foo, I see Foo in the table list, but select from Foo says there's notable with that name? What?" (Hopefully this wasn't too confusing) From: Cravan <savageapple850@gmail.com> Sent: Wednesday, June 12, 2019 9:15 AM To: Gubba, Aruna <AGubba@eprod.com>; Adarsh Jaiswal <adarshjaiswal1989@gmail.com> Cc: pgsql-novice@postgresql.org Subject: Re: Error when doing sql Hi Aruna, I have done what you suggested, and got a similar error with a “hint” this time ############################### 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: ...ear", "Runtime", "imdbID", "imdbRating") VALUES ((title), (y... ^ HINT: Perhaps you meant to reference the column "movies.Title". The above exception was the direct cause of the following exception: Traceback (most recent call last): File "import.py", line 22, in <module> main() File "import.py", line 18, in main {"title": title, "year": year, "runtime": runtime, "imdbID": imdbID, "im dbRating:": imdbRating }) # substitute values from CSV line into SQL command , as per this dict 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 "t itle" does not exist LINE 1: ...ear", "Runtime", "imdbID", "imdbRating") VALUES ((title), (y... ^ HINT: Perhaps you meant to reference the column "movies.Title". [SQL: INSERT INTO movies("Title", "Year", "Runtime", "imdbID", "imdbRating") VALUES ((title), (year), (runtime), (imdbID), (imdbRating))] [parameters: {'title': 'Title', 'year': 'Year', 'runtime': 'Runtime', 'imdbI D': 'imdbID', 'imdbRating:': 'imdbRating\n'}] (Background on this error at: http://sqlalche.me/e/f405) Thanks, Cravan From: "Gubba, Aruna" <mailto:AGubba@eprod.com> Date: Wednesday, 12 June 2019 at 9:10 PM To: cool kid <mailto:savageapple850@gmail.com>, Adarsh Jaiswal <mailto:adarshjaiswal1989@gmail.com> Cc: "mailto:pgsql-novice@postgresql.org" <mailto:pgsql-novice@postgresql.org> Subject: RE: Error when doing sql Cravan, Postgres is case-sensitive, and the columns names in your insert statement should exactly match the table definition. Your insert into statement should look like this, (surround the exact column name around double quotes) Insert into movies(“Title”,”Year”, “Runtime”,”imdbID”, “imdbRating”) Thank you, Aruna Gubba From: Cravan <mailto:savageapple850@gmail.com> Sent: Wednesday, June 12, 2019 8:07 AM To: Adarsh Jaiswal <mailto:adarshjaiswal1989@gmail.com> Cc: mailto:pgsql-novice@postgresql.org Subject: Re: Error when doing sql Hi, here is the table: From: Adarsh Jaiswal <mailto:adarshjaiswal1989@gmail.com> Date: Wednesday, 12 June 2019 at 8:36 PM To: cool kid <mailto:savageapple850@gmail.com> Cc: "mailto:pgsql-novice@postgresql.org" <mailto:pgsql-novice@postgresql.org> Subject: Re: Error when doing sql \d+ tablename ________________________________________ This message (including any attachments) is confidential and intended for a specific individual and purpose. If you are notthe intended recipient, please notify the sender immediately and delete this message.
pgsql-novice by date: