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

Jingoism

Try adding SET NOCOUNT ON; to the beginning of your SQL string. – Landin

@GordThompson Thanks That Solved – Jingoism

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.

Landin

DML: https://mcmap.net/q/73678/-what-are-ddl-and-dml – Gretta

'Beginning of the anonymous code block' seems to mean the 'beginning of the script'. This worked for me after removing the 'Using' statements as well. – Franny

Answer 2

Whenever the server generates some informative messages this scenario may occur. The thing is that pyodbc is not ready to handle multiple result sets at the same time that it is receiving "messages" from the server. By setting "NOCOUNT ON/OFF" you may get rid of just one kind of this "messages". The server could also yield some warnings or some procedure may PRINT something and those would "break" the SQL provoking the same error.

So a more generalist solution would be to iterate over the result sets while also checking if there are more sets to retrieve and inspecting if the server has sent any messages in between. For instance:

def process_query(self, query):
    try:
        self.cursor.execute(query)
        rowlist = []
        rows = self.__extract_resultset()
        while rows or self.__has_next():
            if rows:
                rowlist.append(rows)
            rows = self.__extract_resultset()

        self.cursor.commit()
        return rowlist
    except pyodbc.ProgrammingError as e:
        raise CustomException()
    except Exception as e:            
        raise CustomException()


def __has_next(self):
    try:
        has_next = self.cursor.nextset()
        if self.cursor.messages:
            print(f'Info Message: {self.cursor.messages}', 'info')
    except pyodbc.ProgrammingError as err:
        has_next = False
        print(f'ProgrammingError: {err}', 'error')
    return has_next


def __extract_resultset(self):
    data = []
    try:
        records = self.cursor.fetchall()
        headers = [x[0] for x in self.cursor.description]
        for record in records:
            data.append(dict(zip(headers, record)))
    except pyodbc.ProgrammingError as err:
        print(f'ProgrammingError: {err}', 'error')
    return data

Also some exception handling is more likely to be mandatory since both cursor.fetchall() and cursor.nextset() are very prone to fail because we don't know before hand when a message from the server will appear and any time they do, then the fetch* operations will have been failed. In the other hand nextset will fail (instead of just returning False) when no more result sets are available.

Hope this helps!

Ollayos