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

Question

I am trying to get the ROW_COUNT() from a MySQL stored procedure into python.

here is what I got, but I don't know what I am missing.

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_refresh_mytable(
    OUT row_count INT
)
BEGIN
    DECLARE exit handler for SQLEXCEPTION
        BEGIN
            ROLLBACK;
        END;
    DECLARE exit handler for SQLWARNING
        BEGIN
            ROLLBACK;
        END;
    DECLARE exit handler FOR NOT FOUND
        BEGIN
            ROLLBACK;
        END;

    START TRANSACTION;
    DELETE FROM mytable;

    INSERT INTO mytable
    (
          col1
        , col2
    )
    SELECT
          col1
        , col2
    FROM othertable
    ;
    SET row_count =  ROW_COUNT();
    COMMIT;
END //

DELIMITER ;

If I call this in via normal SQL like follows I get the correct row_count of the insert operation (e.g. 26 rows inserted):

CALL sp_refresh_mytable(@rowcount);
select @rowcount as t;
-- output: 26 

Then in python/mysqlalchemy:

def call_procedure(engine, function_name, params=None):
    connection = engine.raw_connection()
    try:
        cursor = connection.cursor()
        result = cursor.callproc('sp_refresh_mytable', [0])
        ## try result outputs
        resultfetch = cursor.fetchone()
        logger.info(result)
        logger.info(result[0])
        logger.info(resultfetch)
        cursor.close()
        connection.commit()
        connection.close()
        logger.info(f"Running procedure {function_name} success!")
        return result
    except Exception as e:
        logger.error(f"Running procedure {function_name} failed!")
        logger.exception(e)
        return None
    finally:
        connection.close()

So I tried logging different variations of getting the out value, but it is always 0 or None.

[INFO] db_update    [0]
[INFO] db_update    0
[INFO] db_update    None

What am I missing?

Thanks!

Answer 1

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.

Answer 2

I just wanted to add another piece of code, since I was trying to get callproc to work (using sqlalchemy) with multiple in- and out-params.

For this case I went with the callproc method using a raw connection [solution b) in my previous answer], since this functions accepts params as a list.

It could probably be done more elegantly or more pythonic in some parts, but it was mainly for getting it to work and I will probably create a function from this so I can use it for generically calling a SP with multiple in and out params.

I included comments in the code below to make it easier to understand what is going on.

In my case I decided to put the out-params in a dict so I can pass it along to the calling app in case I need to react to the results. Of course you could also include the in-params which could make sense for error logging maybe.

## some in params
function_name   = 'sp_upsert'
in_param1       = 'my param 1'
in_param2       = 'abcdefg'
in_param3       = 'some-name'
in_param4       = 'some display name'
in_params = [in_param1, in_param1, in_param1, in_param1]
## out params
out_params = [
     'out1_row_count'
    ,'out2_row_count'
    ,'out3_row_count'
    ,'out4_row_count_ins'
    ,'out5_row_count_upd'
]
params = copy(in_params)
## adding the outparams as integers from out_params indices
params.extend([i for i, x in enumerate(out_params)])
## the params list will look like
## ['my param 1', 'abcdefg', 'some-name', 'some display name', 0, 1, 2, 3, 4]
logger.info(params)
## build query to get results from callproc (including in and out params)
res_qry_params = []
for i in range(len(params)):
    res_qry_params.append(f"@_{function_name}_{i}")
res_qry = f"SELECT  {', '.join(res_qry_params)}"
## the query to fetch the results (in and out params) will look like
## SELECT  @_sp_upsert_0, @_sp_upsert_1, @_sp_upsert_2, @_sp_upsert_3, @_sp_upsert_4, @_sp_upsert_5, @_sp_upsert_6, @_sp_upsert_7, @_sp_upsert_8
logger.info(res_qry)
try:
    connection = engine.raw_connection()
    ## calling the sp
    cursor = connection.cursor()
    cursor.callproc(function_name, params)
    ## get the results (includes in and out params), the 0/1 in the end are the row_counts from the sp
    ## fetchone is enough since all results come as on result record like
    ## ('my param 1', 'abcdefg', 'some-name', 'some display name', 1, 0, 1, 1, 0)
    cursor.execute(res_qry)
    results = cursor.fetchone()
    logger.info(results)
    ## adding just the out params to a dict
    res_dict = {}
    for i, element in enumerate(out_params):
        res_dict.update({
            element: results[i + len(in_params)]
            })
    ## the result dict in this case only contains the out param results and will look like
    ## { 'out1_row_count': 1,
    ##   'out2_row_count': 0,
    ##   'out3_row_count': 1,
    ##   'out4_row_count_ins': 1,
    ##   'out5_row_count_upd': 0}
    logger.info(pformat(res_dict, indent=2, sort_dicts=False))
    cursor.close()
    connection.commit()
    logger.debug(f"Running procedure {function_name} success!")
except Exception as e:
    logger.error(f"Running procedure {function_name} failed!")
    logger.exception(e)

Just to complete the picture, here is a shortened version of my stored procedure. After BEGIN I declare some error handlers I set the out params to default 0, otherwise they could also return as NULL/None if not set by the procedure (e.g. because no insert was made):

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_upsert(
    IN in_param1 VARCHAR(32),
    IN in_param2 VARCHAR(250),
    IN in_param3 VARCHAR(250),
    IN in_param4 VARCHAR(250),
    OUT out1_row_count INTEGER,
    OUT out2_row_count INTEGER,
    OUT out3_row_count INTEGER,
    OUT out4_row_count_ins INTEGER,
    OUT out5_row_count_upd INTEGER
)
BEGIN
    -- declare variables, do NOT declare the out params here!
    DECLARE dummy INTEGER DEFAULT 0;
    
    -- declare error handlers (e.g. continue handler for not found)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET dummy = 1;
    
    -- set out params defaulting to 0
    SET out1_row_count = 0;
    SET out2_row_count = 0;
    SET out3_row_count = 0;
    SET out4_row_count_ins = 0;
    SET out5_row_count_upd = 0;

    -- do inserts and updates and set the outparam variables accordingly
    INSERT INTO some_table ...;
    SET out1_row_count = ROW_COUNT();

    -- commit if no errors
    COMMIT;
END //
DELIMITER ;