pyodbc fetchall() not fetching all

Question

I have Azure serverless functions which are connecting to a SQL database. One of the functions executes a stored procedure which in turn executes sub procedures, each of which produce a results set.

in T-SQL:

CREATE PROCEDURE usp_proc_1 AS 
SELECT GETDATE() AS 'Date'
GO
CREATE PROCEDURE usp_proc_2 AS 
EXEC usp_proc_1
SELECT 1 AS 'number'
GO
EXEC usp_proc_2

In Python I have as helper function that I have cribbed from other posts,

def getMultiResults(conn , query, params = None):
    # use this when the query returns many results sets
    cursor = conn.cursor()
    records = []

    if (params == None): 
        cursor.execute(query)
    else:
        cursor.execute(query,params)

    # first record set
    logging.info(cursor.description[0])
    attributes = [key[0] for key in cursor.description] # gets the column headers 
    dataframe = cursor.fetchall()
    cursor.commit()
    records.append( [dict(zip(attributes, row)) for row in dataframe])
    
    # more record sets
    while (cursor.nextset()):
      logging.info(cursor.description[0])
      attributes = [key[0] for key in cursor.description] # gets the column headers 
      dataframe = cursor.fetchall()
      cursor.commit()
      records.append( [dict(zip(attributes, row)) for row in dataframe])
    
    return records

When I call the getMultiResults on EXEC usp_proc_2 the dataframe only contains the date results set. I think this is because although the outer procedure looks like it returns two data sets, the inner procedure creates the first one and this is all that the fetchall() is seeing. if the stored procedure was

CREATE PROCEDURE usp_proc_3
SELECT GETDATE() AS 'Date'
SELECT 1 AS 'Number'
GO
EXEC usp_proc_3

then fetchall() would work as expected

Am I correct and if so, how can I correct the issue.

In reality I have a complex master procedure that depending on the business scenario runs a number of different sub-procedures, each of which returns both a results set and a return value. All I need is the LAST results set from the master procedure but this could be the 3rd, 5th or 6th results set depending on what internal procedures were run.

Can you place `SET NOCOUNT ON` as first line in your procedures and test agian? Thanks. — Zhorov, Jan 07 '22 at 13:11
I just altered your attempt using logic from [this question's](https://stackoverflow.com/questions/50683810/stored-procedure-multiple-tables-pyodbc-python) answer and got it working. Does that answer your question? — Thom A, Jan 07 '22 at 13:30

score 0 · Answer 1 · answered Jan 07 '22 at 22:41

So it turns out the problem was likely in the premature cursor.commit(). The code below works just fine.

def getMultiResults(conn , query, params = None):
  # use this when the query returns many results sets
    cursor = conn.cursor()
    records = []

    if (params == None): 
        cursor.execute(query)
    else:
        cursor.execute(query,params)

    # first record set
    logging.info(cursor.description[0])
    attributes = [key[0] for key in cursor.description] # gets the column headers 
    dataframe = cursor.fetchall()
    logging.info("dataframe")
    logging.info(dataframe)
    #cursor.commit()
    records.append( [dict(zip(attributes, row)) for row in dataframe])
    
    # more record sets
    while (cursor.nextset()):
      # if(len(cursor.description)> 0):
      logging.info(cursor.description[0])
      attributes = [key[0] for key in cursor.description] # gets the column headers 
      dataframe = cursor.fetchall()
      # cursor.commit()
      records.append( [dict(zip(attributes, row)) for row in dataframe])
    
    cursor.commit()
    return records

the result of records is an array of 6 arrays (in my case) so you can pull off the correct array when you get the results.data[x] where x is zero-based array index or results.data[results.data.length -1] for the last one.

pyodbc fetchall() not fetching all

1 Answers1

Linked