Michael,
As you suggested, I grabbed the underlying dbapi connection from the the
sqlalchemy connection and was able to get the result back in a somewhat
round about way (in the code that follows conn is a SQLAlchemy connection):
mysqlconn = conn.connection
cursor = mysqlconn.cursor()
args=(1,2)
cusor.callproc('myproc', args)
status = None
for result in cursor.stored_results():
status = result.fetchone()[0]
break
According to the MySQL Connector documentation for callproc (
http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLCursor_callproc.html)
I should be able to call my original stored procedure passing a tuple of 3
arguments, the last one being a placeholder for the output parameter. After
the call to callproc it should be filled in with the output parameter.
However, this didn't work which is why I had to go with looking into the
returned result set.
Thanks for your help!
Steve R
On Thursday, April 18, 2013 1:09:39 PM UTC-4, Michael Bayer wrote:
> yeah I've no idea, would need to get it working with raw cursor first to
> even know how this should be treated.
>
>
> On Apr 18, 2013, at 10:43 AM, Stephen Ray <[email protected]<javascript:>>
> wrote:
>
>
> Michael,
>
> Thanks for the quick reply.
>
> As a workaround I wrapped the stored procedure in another stored procedure
> that selects the return status. Now I am getting an 'Unread results
> exception' when I execute.
>
> Here is the code fragment (where self._engine is a SQLAlchemy engine):
>
> t = text('CALL myproc(:in1, :in2);', bindparams=[bindparam('in1',
> type_=Integer, value=1), bindparam('in2', type_=Integer, value=2)])
>
> conn = self._engine.connect()
>
> result = conn.execute(t)
>
> The 'Unread results" exception is thrown by the last line.
>
> Is it possible to read a result set from a stored procedure through
> SQLALchemy or do I need to drop down to DBAPI cursor level (which as you
> can see I am trying to avoid).
>
> Steve R
>
>
>
>
>
>
>
> On Thursday, April 18, 2013 9:26:29 AM UTC-4, Michael Bayer wrote:
>
>> to my knowledge, the existing DBAPIs for MySQL don't support output
>> parameters (news to me that MySQL SPs did). But I haven't confirmed
>> that. You'd need to figure out first how to do this with the plain DBAPI
>> cursor, such as that of MySQL-python. Within SQLAlchemy for now you'd
>> probably need to use the DBAPI connection directly from an Engine or a
>> Connection and then manipulate the cursor directly.
>>
>>
>> On Apr 18, 2013, at 9:18 AM, Stephen Ray <[email protected]> wrote:
>>
>>
>> My environment is Python 3.2, SQLAlchemy 0.8, MySQL 5.5, and using
>> MySQL-connector 1.0.9.
>>
>> I have a stored procedure that takes two input parameters (both integers)
>> and returns a single integer output parameter indicating the success of
>> failure of the stored procedure. No record sets are returned by the stored
>> procedure, its essentially part of an ETL process that loads from staging
>> tables. All I need to know is the return status contained in the single
>> output parameter to know whether the load was successful or not.
>>
>> I've trawled the web for good examples and seen solutions using func
>> objects, text objects, and calling a constructed string directly. Which
>> would be the best approach to use in this situation? I would like something
>> as DB agnostic as possible so I tried the func approach first but this
>> seemed to be treating the SQL object as a MySQL Function rather than a
>> MySQL Stored Procedure.
>>
>> Thanks in advance.
>>
>> Stephen Ray
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>>
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
