fetchall() throws "Previous SQL was not a query." for DELETE query

Question

I have a python script which connects to sql server instance. I running a cte query to remove duplicates. The query run sucessfully but when i used the fetchall() function it results in an Error: the previous query is not a sql query and after checking in the db table for the duplicates, it shows the duplicate still exists. This is the same case with both pyodbc and sqlalchemy.

Code pyodbc:

import pyodbc

conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

query = ''';with cte as
(
  SELECT [ID], [TIME], ROW_NUMBER() OVER 
  (PARTITION BY [ID] order by [TIME] desc) as rn
  from table
)delete from cte WHERE rn > 1'''

cursor.execute(query)

cursor.close()
conn.close()

Code for sqlalchemy:

from sqlalchemy import create_engine 
from sqlalchemy.sql import text
import urllib

conn = urllib.parse.quote_plus(connection_string)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))

query = '''with cte as
(
  SELECT [ID], [TIME], ROW_NUMBER() OVER (PARTITION BY [ID] order by [TIME] desc) as rn
  from table
)
delete from cte WHERE rn > 1'''

connect = engine.connect()

result = connect.execute(query)

if result.returns_rows:
    print("Duplicates removed")
else:
    print("No row is returned")

Answer 1

when i used the fetchall() function it results in an Error: the previous query is not a sql query

This is the expected behaviour. Although your query includes a SELECT as part of the CTE, the query itself is ultimately a DELETE query which does not return rows. It will return a row count that you can retrieve with Cursor#rowcount, but Cursor#fetchall() will throw an error because there are no rows to retrieve.