1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Eduardo Flores
  • 71
  • 7
  • How do you know the query is correct? Did you try to execute in the SQL Server Management Studio and it worked? Did you intercept the database logs? What errors do they show? – sophros Sep 29 '20 at 17:09
  • I am unable to reproduce your issue. [This code](https://pastebin.com/Q5mZPNi6) works without throwing a `ProgrammingError`. Please [edit] your question to show the complete stack trace. – Gord Thompson Sep 29 '20 at 17:25
  • @GordThompson i did the same you showed, when calling `data.fetchval()` i get: Traceback (most recent call last): File "", line 1, in pyodbc.ProgrammingError: No results. Previous SQL was not a query. And this is from the command line. – Eduardo Flores Sep 30 '20 at 07:12
  • @sophros Yes, when running it on a new query in sql server manager it outputs "Commands completed successfully." And the logs show no attempt or information from pyodbc commands. I'm looking [here](https://learn.microsoft.com/es-es/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver15) – Eduardo Flores Sep 30 '20 at 07:26
  • How could they if you run the query in the SQL Server Manager? I was asking for logs seen when you attempt to run the query through pyodbc. What does it show? Could you please add snippet to the question? – sophros Sep 30 '20 at 07:36
  • @sophros How can i see those logs? SQL SM does not log when one accesses it through pyodbc? This query `DELETE FROM ALARMS WHERE IDALARM > 0` gives me the desired result. Interestingly, the ProgrammingError [still](https://stackoverflow.com/questions/41302866/error-previous-sql-was-not-a-query-in-python/42672373) occurs. Maybe beacuse there is no return value to the query? I was hoping on getting the number of rows affected. – Eduardo Flores Sep 30 '20 at 08:35
  • 1
    Check this question/answer: https://stackoverflow.com/questions/2732985/logging-odbc-sql-server – sophros Sep 30 '20 at 08:51

1 Answers1

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418