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: 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/>.

