Problem Returning MySQL Stored Procedure results using Python

[cored0mp]

Hey Gang!

OK today I am having trouble with my transaction processing application implemented in python/MySQL. Here is some "working" testing code.

import psycopg2
from psycopg2 import Error
import binascii
from binascii import unhexlify
import mysql.connector as mysql


sql='''CREATE PROCEDURE testprocedure(OUT tacos INT)
BEGIN

 show tables;
 SET tacos := 1 ;
END'''

blank_connection_string = {
                'user': 'someusername',
                'password': 'daniwebisthebest',
                'host': 'localhost',
                'database': 'fundatabase',
                'raise_on_warnings': True }


connection=mysql.connect(**blank_connection_string, autocommit=True)
cursor=connection.cursor()


cursor.execute(sql)

cursor.callproc("testprocedure", (0, ) )
for r in cursor.stored_results():
        print(r)
        results=r.fetchall()
        print(results)
        for result in results:
                print(result)

cursor.execute("drop procedure testprocedure")

cursor.close( )
connection.close()

So it works in that everything that I can fit into a stored procedure certainly executes. But I'm getting nonsense as far as returning the variable "tacos" into any format that python can access. To wit, here is what I'm getting for script output:

MySQLCursorBuffered: (a result of CALL testprocedure(@_testpr..
[('sometable1',), ('funtablename',), ('othertable',)]
('sometable1',)
('funtablename',)
('othertable',)

In other words it seems to be doing a pretty solid job of returning the results that I DON'T need sent back to me, but is not returning the result of the actual SP that I need.

How do I resolve this?

[cored0mp]

I just figured out how to do it!

As part of the segment I can:

import python_modules_as_needed



sql="CREATE PROCEDURE nifty_stored_procedure()
BEGIN
  do_the_needful;
  INSERT INTO metatable (field_name, field_value) VALUES (whatever I want goes here, the results are limitless)
"

cursor.execute(sql)

#time to call stored procedure

cursor.execute("CALL nifty_stored_procedure()")

#OK, maybe it ran, but where are the results?  Oh yeah, I remember!

sql="select * from metatable where some_criterium=true"

cursor.execute(sql)
results=cursor.fetchall()

#from here everything is wonderfully and conventionally loaded into the "results" variable, right?  

Daniweb is just that efficient - no sooner had I typed this all up that I seemed to have guessed my way to a solution. I'll update if this works!

[Dani]

Haha, good job! Thanks for sharing your solution. Sometimes that happens with me. As you type it up, you think about how to phrase what's wrong and what needs to happen, and it makes you think about it from different angles, and you figure it out.