Cannot Return Results from Stored Procedure Using Python Cursor

Cannot return results from stored procedure using Python cursor

Have you tried picking one of the resultsets?

for result in cursor.stored_results():
    people = result.fetchall()

It could be that it's allocating for multiple resultsets even though you only have one SELECT stmt. I know in PHP's MySQLi stored procedures do this to allow for INOUT and OUT variable returns (which again, you have none of, but maybe it's allocating anyways).

The complete code I'm using (which is working) is:

import mysql.connector

cnx = mysql.connector.connect(user='me',password='pw',host='localhost',database='mydb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.callproc("getperson",[1])

for result in cursor.stored_results():
    people=result.fetchall()

for person in people:
    print person

cnx.close()

MSSQL2014 & python 3.7.3: Trying to get stored procedure return value results in No results. Previous SQL was not a query.

You have to call cursor.nextset() to skip past any info messages that were output by the called stored procedures, as NOCOUNT ON is not enough here.

But unfortunately cursor doesn't have a .hasrows() method, so you have to catch the exception in a loop.

EG:

import pyodbc

sql = """\
        SET NOCOUNT ON
        DECLARE @rv INT
        EXEC @rv = sp_executesql N'print ''informational message'''
        SELECT @rv 
        """

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                                      'Server=localhost;'
                                      'Database=tempdb;'
                                      'Trusted_Connection=yes;')

conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql)

while True:
    try:
        retv = cursor.fetchone() 
        break
    except pyodbc.ProgrammingError as e:
       if "Previous SQL was not a query." in str(e):
        if not cursor.nextset():
          throw

print(retv)

How to retrieve an output parameter of a stored procedure?

pyodbc does not currently implement the optional .callproc() method, so a workaround is required to retrieve output parameters and return values. For SQL Server that involves an anonymous code block to EXEC the stored procedure and then SELECT the values, e.g.,

sql = """\
DECLARE @out nvarchar(max);
EXEC [dbo].[test_for_pyodbc] @param_in = ?, @param_out = @out OUTPUT;
SELECT @out AS the_output;
"""
params = ("Burma!", )
crsr.execute(sql, params)
rows = crsr.fetchall()
while rows:
    print(rows)
    if crsr.nextset():
        rows = crsr.fetchall()
    else:
        rows = None

More details at the pyodbc wiki:

https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures#output-parameters-and-return-values

Python sqlalchemy and mySQL stored procedure always returns 0 (out param only)

With the help of this answer I found the following solution that worked for me.

a) Working solution using engine.raw_connection() and cursor.callproc:

def call_procedure(engine, function_name):
    connection = engine.raw_connection()
    try:
        cursor = connection.cursor()
        cursor.callproc(function_name, [0])
        cursor.execute(f"""SELECT @_{function_name}_0""")
        results = cursor.fetchone() ## returns a tuple e.g. (285,)
        rows_affected = results[0]
        cursor.close()
        connection.commit()
        logger.info(f"Running procedure {function_name} success!")
        return rows_affected
    except Exception as e:
        logger.error(f"Running procedure {function_name} failed!")
        logger.exception(e)
        return None
    finally:
        connection.close()

And with this answer I found this solution also:

b) Instead of using a raw connection, this worked as well:

def call_procedure(engine, function_name, params=None):
    try:
        with engine.begin() as db_conn:
            db_conn.execute(f"""CALL {function_name}(@out)""")
            results = db_conn.execute('SELECT @out').fetchone() ## returns a tuple e.g. (285,)
            rows_affected = results[0]
        logger.debug(f"Running procedure {function_name} success!")
        return rows_affected
    except Exception as e:
        logger.error(f"Running procedure {function_name} failed!")
        logger.exception(e)
        return None
    finally:
        if db_conn: db_conn.close()

If there are any advantages or drawbacks of using one of these methods over the other, please let me know in a comment.

---

Related Topics