I'm working on a research project and need to generate multiple temporary tables for my analysis. The query to create those tables looks like this:

DECLARE crsr CURSOR FORWARD_ONLY FOR
  SELECT DISTINCT 
    YEAR(s.date) AS Jahr, 
    MONTH(s.date) AS Monat
  FROM ##preliminary_data___id__ AS s;

OPEN crsr;
FETCH NEXT FROM crsr
INTO
  @year, @month;
WHILE @@fetch_Status = 0
BEGIN
  PRINT 'Next Date -------------------------------------'
  DECLARE @date DATE = DATEFROMPARTS(@year, @month, 1);
  PRINT @date;

  INSERT INTO ##final_data___id__(--snip--)
  SELECT 
    --snip--

  FETCH NEXT FROM crsr
  INTO @year, @month;
 
END;

CLOSE crsr;
DEALLOCATE crsr;

The query, executed from MS SSMS works as expected. I have about 15k subjects, so I would like to generate these table automatically, ideally using python. When I use sqlalchemy, this is my code:

engine = sqlalchemy.create_engine(cxn_str)
with engine.raw_connection().cursor() as cursor:
    cursor.execute(query)
    cursor.commit()

This runs successfully, but the resulting ##final_data___id__ only contains the data for one month. It also runs very quickly, which leads me to assume that FETCH NEXT FROM crsr is never actually performed.

How would I correctly execute this query from python, such that the created table contains the data for all available months?

This is a research project that runs against a corporate database, therefore I have no rights to create stored procedures or tables. ##tables are the longest lasting objects that are possible, and in this setup this is acceptable.

Thanks!

CodePudding user response：

Two of the most common causes of stored procedures and anonymous code blocks "working when executed in SSMS" but "not working when executed from a pyodbc (Python) connection" are:

1. the code not beginning with SET NOCOUNT ON;, and
2. PRINT statements.

Both of those can cause the server to return "results" that might mask the intended results from the code, and in some cases (like this one, apparently) can actually cause the code to fail if those extra "results" are numerous enough to overflow the buffer that the server has allocated for communication with the client.