1

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.

3

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.

  • But it returns a result of some sort, at least within the psql command line client. What is the difference between a result set and a command tag? Is this documented anywhere? – Fake Name Sep 7 '14 at 11:42
  • @FakeName psql reports a command tag like SELECT or INSERT 0 3 for every operation. That's different to a result set, where it prints the rows. I don't know if the difference is set out explicitly in the docs; perhaps it should be. Certainly in the psycopg2 docs. – Craig Ringer Sep 7 '14 at 12:32
1

I think what you were after was:

print(cur.statusmessage)

When I use INSERT, this correctly prints INSERT 0 3 etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.