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?

score 5 · Answer 1 · answered Feb 14 '12 at 02:12

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

score 1 · Answer 2 · answered Jun 29 '17 at 18:52

To be more specific about zzzeek answer you should do something like that

from pyodbc import ProgrammingError
from sqlalchemy import create_engine

# do the connection
engine = create_engine('mssql+pyodbc://user:password@SERVERNAME/DatabaseName?driver=SQL Server')
connection = engine.raw_connection()
cursor = connection.cursor()

# do the query
cursor.execute(query)

# processing it
while True:
    try:
        result = cursor.fetchall()

        # ... do something with result

        if not cursor.nextset(): # trying to get next set
            break
    except ProgrammingError as ex:
        pass

That helps me to work with very complex MSSQL with lots of temporal tables and declares

SQLAlchemy Execute with raw SQL containing @DECLARE local tables

2 Answers2