0

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.
Victor Valente
  • 761
  • 9
  • 24
  • 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 Apr 30 '21 at 16:49
  • 4
    Try adding `SET NOCOUNT ON;` to the beginning of your code block. – Gord Thompson Apr 30 '21 at 17:00
  • If you example code is representative of the real code, you could actually create the SQLAlchemy query using [CTE](https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.SelectBase.cte) instead of executing a `text` clause _(I am making an assumption here that you do)_. – van May 01 '21 at 13:40

1 Answers1

2

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

Jim T
  • 136
  • 5