CODEWITHSUNDEEP
pythonpyodbc
Shubham R
Shubham R

Reputation: 7644

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

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

Upvotes: 5

Views: 7300

Answers (2)

LemuxOne
LemuxOne

Reputation: 1

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!

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123849

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.

Upvotes: 11

Related Questions