"ProgrammingError: No results. Previous SQL was not a query." while trying to truncate a table

Question

I'm trying to delete all the entries from a table but are not able to do it. Does not matter if it is TRUNCATE, or DELETE keyword. The same error occurs

import pyodbc
conn = pyodbc.connect(
                r'Driver={SQL Server};'
                r'Server=' + ip + '\SQLEXPRESS;'
                                  r'Database=...;'
                                  r'UID=...;'
                                  r'PWD=...;', timeout=5)
cursor = conn.cursor()
data = cursor.execute("TRUNCATE TABLE table_name")

pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Setting autocommit to True does not work. Parametrizing it also does not work. The connection is right because SELECT clause works well and returns the right value. With truncating and deleting it does not work at all. The DDBB is still intact.

When excecuting from the pycharm's Python Console i get the folowwing error whenever i try to access the data object (f.e. print(data.fetchval()):

Traceback (most recent call last): File "", line 1, in pyodbc.ProgrammingError: No results. Previous SQL was not a query.

I've read before i might have to do with how the DDBB table is indexed and its private key, but i'm not able to explain it.

Answer 1

I was hoping on getting the number of rows affected.

When we execute a single SQL statement via Cursor.execute, the server can return one of three things:

1. zero or more rows of data in a result set (for a SELECT statement), or
2. an integer row count (for DML statements like UPDATE, DELETE, etc.), or
3. an error.

We retrieve information from a result set via the pyodbc methods .fetchall(), .fetchone(), .fetchval(), etc.. We retrieve row counts using the cursor's rowcount attribute.

crsr = cnxn.cursor()
crsr.execute("DROP TABLE IF EXISTS so64124053")
crsr.execute("CREATE TABLE so64124053 (id int primary key, txt varchar(10))")
crsr.execute("INSERT INTO so64124053 (id, txt) VALUES (1, 'foo')")
print(crsr.rowcount)  # 1
print(crsr.execute("SELECT COUNT(*) AS n FROM so64124053").fetchval())  # 1
crsr.execute("INSERT INTO so64124053 (id, txt) VALUES (2, 'bar')")
print(crsr.rowcount)  # 1
print(crsr.execute("SELECT COUNT(*) AS n FROM so64124053").fetchval())  # 2

Note that TRUNCATE is a special case because it doesn't bother counting the rows it removes from the table; it just returns a row count of -1 …

crsr.execute("TRUNCATE TABLE so64124053")
print(crsr.rowcount)  # -1

… however the rows are indeed removed

print(crsr.execute("SELECT COUNT(*) AS n FROM so64124053").fetchval())  # 0