SQLAlchemy Execute with raw SQL containing @DECLARE local tables

Question

I'm stuck -- I have the following python script with SQL alchemy which I've been using quite successfully for several other purposes.

import sqlalchemy
from sqlalchemy import MetaData
from sqlalchemy.orm import *

engine = sqlalchemy.create_engine("this line of code would provide credentials to the     database")
connection = engine.connect()
session = sessionmaker(bind=engine)
result = connection.execute(sqlquery)

for row in result: print row

Recently though I discovered that if my 'sqlquery' contains an @Declare MyTable statement I get the error:

"This result object does not return rows. "
sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

Here is my SQL query which works fine in SSMS but will not execute when I try to execute it using SQLAlchemy

DECLARE @USER TABLE
(
    UserID INT
    , StatsVals INT
)

INSERT INTO @USER (UserID, StatsVals)
    SELECT TOP 10 u.UserID
    , u.StatsVals
    FROM UserProfile u

SELECT * FROM @USER

Does anyone know why SQLAlchemy would be giving me this error? What should I do to fix this?

Answer 1

When the DBAPI executes on a cursor, if results are present, there's an attribute called cursor.description that's required to be present. If it's not, SQLAlchemy knows there's no results to return.

In this case, this is probably an issue with the DBAPI, unless this usage falls under the realm of "multiple result sets" on the cursor. SQLAlchemy doesn't have direct support for multiple result sets as of yet. If this is the case, you'd need to use the DBAPI cursor directly and call .nextset() to get at the results. You can get this via:

connection = engine.raw_connection()
cursor = connection.cursor()

(docs on how cursor.nextset() works at http://www.python.org/dev/peps/pep-0249/)

Otherwise, you'd really need to contact the DBAPI author and see if what you're doing here is really possible. I'm guessing this is pyodbc, even though you haven't specified what backend you're on. If so, you can contact them at http://code.google.com/p/pyodbc/.