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

Try adding SET NOCOUNT ON; to the beginning of your SQL string. — Gord Thompson
– Gord Thompson, Commented Jan 2, 2018 at 13:08

Gord Thompson · Accepted Answer · 2018-01-02 15:58:28Z

11

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.

answered Jan 2, 2018 at 15:58

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scjorge Over a year ago

DML: stackoverflow.com/questions/2578194/what-is-ddl-and-dml

voluntier Over a year ago

'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.

LemuxOne · Accepted Answer · 2023-02-07 17:57:08Z

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!

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related