pyodbc.ProgrammingError: No results. Previous SQL was not a query, when executing multiple statements at once

Question

i am dealing with sql server database,

where i have a table named 'table1' containing 1 column and 1 row

exp_num
0 

I am trying to update the 0 value exp_num column to +1 and also return old experiment and updated experiment.

For this i am using declare statements.

DECLARE @UpdateOutput1 table (Oldexp_num int,Newexp_num int);

UPDATE get_exp_num
    SET exp_num = exp_num+1

OUTPUT
    DELETED.exp_num,
    INSERTED.exp_num
  INTO @UpdateOutput1;

 select * from @UpdateOutput1

When i'm running this in SQL editor i am getting the results.

Oldexp_num   Newexp_num
    0             1

but if i make this same as a query, and try to use pyodbc package i am getting error.

import pyodbc

connection = pyodbc.connect()  # i am getting a connection
query = "DECLARE @UpdateOutput1 table (Oldexp_num int,Newexp_num int);UPDATE get_exp_num SET exp_num = exp_num+1 OUTPUT DELETED.exp_num, INSERTED.exp_num INTO @UpdateOutput1; select Newexp_num from @UpdateOutput1;"
cursor = connection.cursor()
cursor.execute(query)
cursor.fetchone()

When im doing cursor.fetchone() , i am getting following error.

File "<ipython-input-1398-bdaba305080c>", line 1, in <module>
    cursor.fetchone()

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

Is there any error in pyodbc package? or in my query

Answer 1

The problem was solved by adding SET NOCOUNT ON; to the beginning of the anonymous code block. That statement suppresses the record count values generated by DML statements like UPDATE ... and allows the result set to be retrieved directly.