Thread: DELETE FROM statement not working

Single quotes for a string.  Double quotes are used in SQL to indicate a table name with capitalization, spaces, etc.

On Mar 14, 2016, at 9:56 AM, Shaan Repswal <shaun.reapswaal@gmail.com> wrote:

> I was trying to delete a record I got inside my database and so I first typed all of this inside the interpreter...
Itwas followed by the error mentioned at the bottom. I can't figure this out. Can anyone help me out? 
>
> >>> conn = psycopg2.connect("dbname=prototypedb user=postgres password=superman123")
> >>> cur = conn.cursor()
> >>> cur.execute("DELETE FROM inventory WHERE name = \"Polyester Direct High Gloss\";")
> Traceback (most recent call last):
>   File "<pyshell#84>", line 1, in <module>
>     cur.execute("DELETE FROM inventory WHERE name = \"Polyester Direct High Gloss\";")
> psycopg2.ProgrammingError: column "Polyester Direct High Gloss" does not exist
> LINE 1: DELETE FROM inventory WHERE name = "Polyester Direct High Gl...
>                                            ^
>
> >>> name = "DELETE FROM inventory WHERE name = \"Polyester Direct High Gloss\";"
> >>> cur.execute(name)
> Traceback (most recent call last):
>   File "<pyshell#86>", line 1, in <module>
>     cur.execute(name)
> psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

--
-- Christophe Pettus
   xof@thebuild.com

On 03/14/2016 09:56 AM, Shaan Repswal wrote:
> I was trying to delete a record I got inside my database and so I first
> typed all of this inside the interpreter... It was followed by the error
> mentioned at the bottom. I can't figure this out. Can anyone help me out?
>
>  >>> conn = psycopg2.connect("dbname=prototypedb user=postgres
> password=superman123")
>  >>> cur = conn.cursor()
>  >>> cur.execute("DELETE FROM inventory WHERE name = \"Polyester Direct
> High Gloss\";")
> Traceback (most recent call last):
>    File "<pyshell#84>", line 1, in <module>
>      cur.execute("DELETE FROM inventory WHERE name = \"Polyester Direct
> High Gloss\";")
> psycopg2.ProgrammingError: column "Polyester Direct High Gloss" does not
> exist
> LINE 1: DELETE FROM inventory WHERE name = "Polyester Direct High Gl...
>                                             ^

Double quotes are for identifiers not string values.  So your statement
should be:

"DELETE FROM inventory WHERE name = 'Polyester Direct High Gloss' "


>
>  >>> name = "DELETE FROM inventory WHERE name = \"Polyester Direct High
> Gloss\";"
>  >>> cur.execute(name)
> Traceback (most recent call last):
>    File "<pyshell#86>", line 1, in <module>
>      cur.execute(name)
> psycopg2.InternalError: current transaction is aborted, commands ignored
> until end of transaction block


--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/14/2016 10:02 AM, Shaan Repswal wrote:

Ccing list
> Traceback (most recent call last):
>    File "<pyshell#88>", line 1, in <module>
>      cur.execute("DELETE FROM inventory WHERE name = 'Polyester Direct
> High Gloss';")
> psycopg2.InternalError: current transaction is aborted, commands ignored
> until end of transaction block

Rollback the failed transaction.:

conn.rollback()

Then retry the query.

FYI, the ';' is not necessary psycopg2 takes care of terminating the query.

>
> On Mon, Mar 14, 2016 at 10:32 PM, Shaan Repswal
> <shaun.reapswaal@gmail.com <mailto:shaun.reapswaal@gmail.com>> wrote:
>
>     I tried this
>
>     cur.execute("DELETE FROM inventory WHERE name = 'Polyester Direct
>     High Gloss'")
>
>     But I got this
>
>     Traceback (most recent call last):
>        File "<pyshell#87>", line 1, in <module>
>          cur.execute("DELETE FROM inventory WHERE name = 'Polyester
>     Direct High Gloss'")
>     psycopg2.InternalError: current transaction is aborted, commands
>     ignored until end of transaction block
>
>     On Mon, Mar 14, 2016 at 10:28 PM, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>         On 03/14/2016 09:56 AM, Shaan Repswal wrote:
>
>             I was trying to delete a record I got inside my database and
>             so I first
>             typed all of this inside the interpreter... It was followed
>             by the error
>             mentioned at the bottom. I can't figure this out. Can anyone
>             help me out?
>
>               >>> conn = psycopg2.connect("dbname=prototypedb user=postgres
>             password=superman123")
>               >>> cur = conn.cursor()
>               >>> cur.execute("DELETE FROM inventory WHERE name =
>             \"Polyester Direct
>             High Gloss\";")
>             Traceback (most recent call last):
>                 File "<pyshell#84>", line 1, in <module>
>                   cur.execute("DELETE FROM inventory WHERE name =
>             \"Polyester Direct
>             High Gloss\";")
>             psycopg2.ProgrammingError: column "Polyester Direct High
>             Gloss" does not
>             exist
>             LINE 1: DELETE FROM inventory WHERE name = "Polyester Direct
>             High Gl...
>                                                          ^
>
>
>         Double quotes are for identifiers not string values.  So your
>         statement should be:
>
>         "DELETE FROM inventory WHERE name = 'Polyester Direct High Gloss' "
>
>
>
>
>               >>> name = "DELETE FROM inventory WHERE name = \"Polyester
>             Direct High
>             Gloss\";"
>               >>> cur.execute(name)
>             Traceback (most recent call last):
>                 File "<pyshell#86>", line 1, in <module>
>                   cur.execute(name)
>             psycopg2.InternalError: current transaction is aborted,
>             commands ignored
>             until end of transaction block
>
>
>
>         --
>         Adrian Klaver
>         adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com