Getting 'None' as the result for my stored procedure-CodePudding

I have a stored procedure with 2 input parameters and 1 output parameter. I'm calling the stored procedure through python with mysql connector.

The procedure accepts a month and a year and return the sum of a certain column during that period.

This works as expected in MySql Workbench but when I run the same query in python it returns 'None'

mycursor = mydb.cursor()

self.args = ('October','2021',0)

mycursor.callproc('calc_sum', self.args)

mycursor.execute('SELECT @_calc_sum_0, @_calc_sum_1, @_calc_sum_2')

result = mycursor.fetchall()

print ('sum_calc_is',result)

Running the above block of code return a tuple made up of 'None'

[(None, None, None)]

What am I doing wrong?

CodePudding user response：

You need in something like:

mycursor = mydb.cursor()
self.args = ('October','2021',0)
mycursor.execute('SET @_calc_sum_0 := %s, @_calc_sum_1 := %s, @_calc_sum_2 := %s', self.args)
mycursor.callproc('calc_sum(@_calc_sum_0, @_calc_sum_1, @_calc_sum_2)')
# or maybe
# mycursor.execute('CALL calc_sum(@_calc_sum_0, @_calc_sum_1, @_calc_sum_2)')
mycursor.execute('SELECT @_calc_sum_0, @_calc_sum_1, @_calc_sum_2')
result = mycursor.fetchall()
print ('sum_calc_is',result)

First mycursor.execute assign parameters values into user-defined variables. Adjust the parameters insertion syntax if it is not correct.

Next mycursor.callproc calls the stored procedure specifying above UDVs (not literal values!) as its parameters. SP alters their values during the execution. Maybe you must use mycursor.execute('CALL calc_sum(@_calc_sum_0, @_calc_sum_1, @_calc_sum_2)') there, because you do not need in transferring parameters values.

Next mycursor.execute retrieves the values of UDVs after SP execution.