I'm trying to call a stored procedure that returns multiple result sets using SQLAlchemy. If it matters, underneath I'm using PyODBC and FreeTDS. I call the execute() method using a raw query with "exec" calling my stored procedure on a session object and get a ResultProxy object back.
With a raw pyodbc cursor, I can call the nextset() function to advance to the next result set. I see no way to do the same using the ResultProxy I get back from SQLAlchemy. Indeed, the docs say:
The DBAPI cursor will be closed by the ResultProxy when all of its result rows (if any) are exhausted.
Is there a way to read multiple result sets with SQLAlchemy, or will I have to perform this query with the raw DBAPI?
support for nextset() is ticket 1635. It's two years old. It contains a partial patch which needs updating, in particular to work along with an execution option that passes along a hint that the statement will be returning multiple result sets, so that the resultproxy's existing autoclose behavior can remain the default. the feature would also need a lot of tests.
There's no major technical hurdle to this feature but there's generally very little interest in this use case. So at the moment you need to stick with the raw cursor, until people express enough interest in this feature to put momentum behind it again.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With