SQLServer: query runs on console but not on SQLAlchemy

Question

I'm trying to run a very expensive SQL Server query via Python + SQLAlchemy. It runs just fine on sql server console but it errors out when called via sqlalchemy.

Test run looks like this:

Run query on SQL Server console.
Wait about 15 minutes for it to finish.
Query runs just fine and returns ~50,000 rows.

When running the same query using Python + SQLAlchemy, it looks like this:

Run query.
Wait a long time.
Code errors out and throws a misleading error stating that the query did not return any rows.

I am positive that this error message cannot possibly be right, because I have tested the same query on console and it runs just fine and returns A LOT of rows. Does anyone know what is really happening here?

Query looks like this:

USE DB_NAME;

DROP TABLE IF EXISTS #TB_1;
CREATE TABLE #TB_1 (FIELD_1 BIGINT, FIELD_2 BIGINT);
INSERT INTO #TB_1 VALUES (1, 5), (2, 6), (3, 7);

--------------------------------------------------

DROP TABLE IF EXISTS #TB_2;
SELECT * INTO #TB_2 FROM (
    SELECT DISTINCT FIELD_1, FIELD_2
    FROM dbo.PRIMARY_TABLE_1 (NOLOCK)
    WHERE FIELD_1 IN (SELECT * FROM #TB_1)
) AS TB_2;

--------------------------------------------------

SELECT FIELD_1, FIELD_2 FROM #TB_1
UNION ALL
SELECT FIELD_1, FIELD_2 FROM #TB_2

Code looks like this:

from sqlalchemy.engine import create_engine

engine = create_engine(SQLServer_URI)
with engine.connect() as connection:
    connection.execute(huge_query).fetchall()

Here's the Stack Trace:

Traceback (most recent call last):
  ...
  File "path-to-project/src/etl/ETL.py", line 48, in extract
    raw_data = connection.execute(query).fetchall()
  File "path-to-project\venv\lib\site-packages\sqlalchemy\engine\result.py", line 984, in fetchall
    return self._allrows()
  File "path-to-project\venv\lib\site-packages\sqlalchemy\engine\result.py", line 398, in _allrows
    make_row = self._row_getter
  File "path-to-project\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1160, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "path-to-project\venv\lib\site-packages\sqlalchemy\engine\result.py", line 319, in _row_getter
    keymap = metadata._keymap
  File "path-to-project\venv\lib\site-packages\sqlalchemy\engine\cursor.py", line 1197, in _keymap
    self._we_dont_return_rows()
  File "path-to-project\venv\lib\site-packages\sqlalchemy\engine\cursor.py", line 1178, in _we_dont_return_rows
    util.raise_(
  File "path-to-project\venv\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

Can you paste the call stack again while specifying echo parameter while creating a engine? Replace: engine = create_engine(SQLServer_URI) with: engine = create_engine(SQLServer_URI, echo=True) Would like to see the statement in your query that was about to execute when the error message occurred. — LinuxUser
– LinuxUser, Commented Apr 30, 2021 at 16:49
Try adding SET NOCOUNT ON; to the beginning of your code block. — Gord Thompson
– Gord Thompson, Commented Apr 30, 2021 at 17:00
If you example code is representative of the real code, you could actually create the SQLAlchemy query using CTE instead of executing a text clause (I am making an assumption here that you do). — van
– van, Commented May 1, 2021 at 13:40

Jim T · Accepted Answer · 2022-10-24 12:57:39Z

3

Yep adding "SET NOCOUNT ON;" as per Gord Thompson's comment, to the front of my query solved it for me. I got the same error running a stored procedure containing multiple queries. So this will do the trick:

data = pd.read_sql("SET NOCOUNT ON; Your SQL Query.....

This answer https://stackoverflow.com/a/55597613/11692538 helped solve it and explain it for me. Basically sqlalchemy (or pyodbc) reads any messages sent in the execution of a query as the result, hence why you get the error "name": "ResourceClosedError", "message": "This result object does not return rows. It has been closed automatically." So setting NOCOUNT prevents sql server sending back count messages during the execution of the query(s).

edited Oct 24, 2022 at 12:57

answered Oct 24, 2022 at 10:55

Jim T

3161 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLServer: query runs on console but not on SQLAlchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related