PYODBC Only returning values of the first query in a stored procedure

Question

I am trying to make a script that will run a stored procedure and then return the values. A simple enough script. As I run the script it is only returning the output of the first query in my stored procedure. When the call is made I have the values being appended to a list. Once the list is created I have it printing onto a GUI that I have made. My stored procedure does work and returns the values for all the queries it contains when run on SSMS. My stored procedure is as follows (Sorry if these are poorly made stored procedures, I am fairly new to SQL):

It takes a parameter @username

Select username,user_id,user_type
from db1.users
where username like '%' + TRIM(@username) + '%'
ORDER BY username

Select username,user_id,user_type
from db2.users
where username like '%' + TRIM(@username) + '%'
ORDER BY username

Select username,user_id,user_type
from db3.users
where username like '%' + TRIM(@username) + '%'
ORDER BY username

Select username,user_id,user_type
from db4.users
where username like '%' + TRIM(@username) + '%'
ORDER BY username

Select username,user_id,user_type
from db5.users
where username like '%' + TRIM(@username) + '%'
ORDER BY username

The code that I am using to try and run this procedure and then append it to the list are as follows:

def user_query(user, conn, output_field):
    global user
    user_results = []
    username = user.get()
    
    cursor = conn.cursor()
    

    get_user_stored_proc = "SET NOCOUNT ON; EXEC [dbo].[getUser] @username = ?"
    output_field.config(state=NORMAL)
    output_field.delete('1.0', END)

    cursor.execute(get_user_stored_proc, username)
    #TODO Fix this so that it will output everything from the query
    columns = [column[0] for column in cursor.description]
    for row in cursor:
        user_results.append(dict(zip(columns, row)))
    
    print_results(user_results, output_field)
    #cursor.close()
    #conn.close()

As previously mentioned, the only output I have returned to me when running this is the result of the first query. Any help is appreciated!

You need to iterate through the sets in your `cursor`. YOu have use `cursor.nextset()` to read the *next* result set. THis is a DB2 [question](https://stackoverflow.com/a/7263940/2029983), but the idea is the samee. — Thom A, Dec 19 '22 at 16:05
Does this answer your question? [pyodbc fetchall() not fetching all](https://stackoverflow.com/questions/70621673/pyodbc-fetchall-not-fetching-all) — Thom A, Dec 19 '22 at 16:07
Does this answer your question? [Stored Procedure Multiple Tables - PYODBC - Python](https://stackoverflow.com/questions/50683810/stored-procedure-multiple-tables-pyodbc-python) — Thom A, Dec 19 '22 at 16:08
@Larnu thank you, I will give your recommendation a try and post the result assuming it works! Thank you for pointing me in the right direction! — Buzzkillionair, Dec 19 '22 at 16:24
@Larnu in your 3rd link, do you know what pd refers to? I am trying to implement their solution but am confused by that. — Buzzkillionair, Dec 19 '22 at 17:06

score 1 · Answer 1 · answered Dec 19 '22 at 18:17

I learned that the following link does have the resources needed. Note that they use pandas and import it as pd. They also create a datafield as df but do not show the initial creation of this variable. Additionally, I did make some of my own changes; these are mainly preferential changes and do not really have an impact on the overall output. Thank you to @larnu for pointing this out. Here is my edited code.

def user_query(user, conn, output_field):
    global user
    user_results = []
    username = user.get()

    cursor = conn.cursor()
    get_user_stored_proc = "EXEC [dbo].[getuser] @username = ?"
    output_field.config(state=NORMAL)
    output_field.delete('1.0', END)

    rows = cursor.execute(get_user_stored_proc, username).fetchall()
    columns = [column[0] for column in cursor.description]

    user_results.append(pd.DataFrame.from_records(rows, columns=columns))


    while True:
        rows = cursor.fetchall()
        columns = [column[0] for column in cursor.description]
        user_results.append(pd.DataFrame.from_records(rows, columns=columns))
        if cursor.nextset() == False:
            break

PYODBC Only returning values of the first query in a stored procedure

1 Answers1