Mysql + SQLAlchemy + Stored Procedure + output parameter

[jichao liu]

procedure something like:

CREATE  PROCEDURE `NewProc`(IN `groupID_` int,OUT `CountParam_` int)

BEGIN

set @CountParam_ = (select count(1) FROM mytable WHERE GroupID=groupID_ );

END;

python:

counts =  bindparam('CountParam_',isoutparam=True,type_=Integer,value = 0)
connection = engine.raw_connection()

    try:

        cursor = connection.cursor()

        ss = cursor.callproc("QueryUsersByAuthorID", [user.groupID, counts])

        results = list(cursor.fetchall())
        print counts

        cursor.close()

        connection.commit()

    finally:

        connection.close()

why it always come out  0 for counts while results get the data?

is my way calling the procedure correct or something else.

ps. how to get the class obj results for the fetchall() other than tuple at now.

[Michael Bayer]

On Dec 15, 2014, at 4:53 AM, jichao liu <liujic...@gmail.com> wrote:

procedure something like:

CREATE  PROCEDURE `NewProc`(IN `groupID_` int,OUT `CountParam_` int)

BEGIN

set @CountParam_ = (select count(1) FROM mytable WHERE GroupID=groupID_ );

END;

python:

counts =  bindparam('CountParam_',isoutparam=True,type_=Integer,value = 0)
connection = engine.raw_connection()

    try:

        cursor = connection.cursor()

        ss = cursor.callproc("QueryUsersByAuthorID", [user.groupID, counts])

        results = list(cursor.fetchall())
        print counts

        cursor.close()

        connection.commit()

    finally:

        connection.close()

why it always come out  0 for counts while results get the data?

no idea, that is the DBAPI cursor.  Email the maintainers of MySQLdb, or whatever DBAPI it is that you’re using.

ps. how to get the class obj results for the fetchall() other than tuple at now.

you’d need to build a ResultProxy out of that cursor, then feed it into query.instances().   This might not be not very easy to do, so short of figuring out that, this feature is not supported right now.

[dewey]

I can help you with part of this.

your CountParam_ and your @CountParam_ are DIFFERENT variables.....

the OUT param is local to the stored proc and is returned by a bind thru the DBapi

the @ param is GLOBAL to your current MySQL session -- can only be retrieved by SELECT

If you want to have the result value passed back to you directly, then you must do as Mike suggested and work with the specifics of the DBAPI.

If you don't mind issuing another query, you can get your value back via query RIGHT AFTER you run the stored proc....like:

Select @CountParam_ ;

Hope this helps!

D

[jichao liu]

thanks for your reply,michael.
depart of the fucking '@' in my procedure, i try it by another way using this:

engine.execute(text('call QueryDataByGroupID(166012,:totalCount_);', bindparams = [outparam('totalCount_',type_= Integer)]))

but i get this error :

(OperationalError) (1414, 'OUT or INOUT argument 2 for routine article_db.QueryDataByGroupID is not a variable or NEW pseudo-variable in BEFORE trigger') 'call QueryDataByGroupID(166012,,%s);' (None,)

finally i give up with the out param..

refer to the returning object, i figure out it using the attribute cursor.description,although i hope it can return an instance in the further:

recode_list = list(cursor.fetchall())

description_index = [x[0] for x in cursor.description]

data_list = [DictToObject(**dict(zip(description_index, each))) for each in recode_list]

class DictToObject:

    def __init__(self, **entries):

        self.__dict__.update(entries)

at last,forgive my pool english :D

[jichao liu]

YES,it is .thank you somuch