Psycopg2 – Delete Returns Nothing

postgresqlpostgresql-9.3

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2.

Take the following minimal example:

def testDelete():
    db = DbInterface()
    cur = db.conn.cursor()
    cur.execute("DELETE FROM munamelist WHERE name='something'")
    print("Results = ", cur.fetchall())

Basically, the PostgreSQL documentation for DELETE states:

On successful completion, a DELETE command returns a command tag of
the form

DELETE count

The count is the number of rows deleted. Note that the number may be
less than the number of rows that matched the condition when deletes
were suppressed by a BEFORE DELETE trigger. If count is 0, no rows
were deleted by the query (this is not considered an error).

However, psycopg2 raises a error when you try to fetch the results of the query:

Traceback (most recent call last):
  File "autoOrganize.py", line 370, in <module>
    parseCommandLine()
  File "autoOrganize.py", line 363, in parseCommandLine
    testDelete()
  File "autoOrganize.py", line 247, in testDelete
    print("Results = ", cur.fetchall())
psycopg2.ProgrammingError: no results to fetch

It doesn't matter if the item exists or not, you cannot fetch the results of the query. Does psycopg2 not return "command tags" for SQL?

If not, how can I retrieve the number of altered rows as would be returned in the console interface? Never mind, apparently cursor.rowcount is the number of rows modified in the last DML/DQL statement.

Best Answer

DELETE without RETURNING doesn't return a result set. So you can't use fetch... calls with it, like cursor.fetchall().

The affected row count is stored in cursor.rowcount for DML.

Related Question