[Python] SQL to EXCEL report generation application not working with a server connection

I'm working on a SQL to EXCEL report generator. The frontend is streamlit, and how it works is you paste your query in the query box on streamlit with a --sheet header, it executes the query and generates a excel workbook with sheet1. You can have multiple queries in multiple --sheet blocks but the problem is I'm working with complex queries, and the queries usually involve a bunch of temp tables being made and then data from those temp tables is extracted into the excel spreadsheet. My code works on localhost in Microsoft SQL Server Management Studio 18 but as soon as I connect to a server my app returns an error that says:

Error generating report: This result object does not return rows. It has been closed automatically.
​

It could be a connection issue, I troubleshooted with ChatGPT and it says its probably a connection issue, because SSMS is strict with how temp tables are handled in a connection when using SQLAlchemy and pandas read.sql(), but I'm not sure.

import pandas as pd
from sqlalchemy import create_engine, text
import time
import re
from pyexcelerate import Workbook

def parse_query_blocks(query):

parts = re.split(r"--\s*sheet:\s*(.+)", query)
blocks = []
for i in range(1, len(parts), 2):
sheet_name = parts.strip()
sql_block = parts[i + 1].strip()
queries = [q.strip() for q in sql_block.split(";") if q.strip()]
blocks.append((sheet_name, queries))
return blocks

def generate_dynamic_reports(engine, sheet_query_blocks, output_file):
start_time = time.time()

all_setup_queries = []
sheet_selects = []

for sheet_name, queries in sheet_query_blocks:
final_select = None
for q in reversed(queries):
if q.strip().lower().startswith("select"):
final_select = q
break
if not final_select:
raise ValueError(f"No SELECT query found for sheet: {sheet_name}")

non_selects = [q for q in queries if q != final_select]
all_setup_queries.extend(non_selects)
sheet_selects.append((sheet_name, final_select))

wb = Workbook()

with engine.connect() as conn:


try:

if all_setup_queries:
for q in all_setup_queries:
conn.execute(text(q))

print(f"[{round(time.time() - start_time, 2)}s] ✅ Executed all setup queries")


for sheet_name, select_query in sheet_selects:
print(f"[{round(time.time() - start_time, 2)}s] Running SELECT for: {sheet_name}")
df = pd.read_sql(text(select_query), conn)
df.fillna("NULL", inplace=True)

data = [df.columns.tolist()] + df.values.tolist()
wb.new_sheet(sheet_name[:31], data=data)
print(f"[{round(time.time() - start_time, 2)}s] Wrote sheet: {sheet_name} ({len(df)} rows)")

except Exception as e:
print(f"❌ Error generating report: {e}")
raise e

wb.save(output_file)
print(f"[{round(time.time() - start_time, 2)}s] ✅ All sheets exported to {output_file}")


EDIT:

I can share the query I used. Unfortunately, I’m currently unable to replicate the error because I don’t have permissions to connect to the SQL Server environment where the issue originally occurred.

The error message I posted earlier was generated only when the application was connected to the remote SQL Server. When I run the same query locally on localhost\SQLEXPRESS, it works perfectly — no errors, and the spreadsheet output is generated correctly.

Heres the query:-


--sheet: s1
DROP TABLE IF EXISTS #temp_orders;
DROP TABLE IF EXISTS #temp_discounts;
DROP TABLE IF EXISTS #temp_customers;


SELECT TOP 1000
item_id,
Customer_ID,
increment_id,
price,
grand_total,
discount_amount,
payment_method,
category_name_1,
created_at,
FY
INTO #temp_orders
FROM [Kaggletest].[dbo].[test_table]
WHERE ISNUMERIC(price) = 1 AND ISNUMERIC(grand_total) = 1;


SELECT
item_id,
Customer_ID,
increment_id,
discount_amount,
price
INTO #temp_discounts
FROM #temp_orders
WHERE discount_amount > 0;


SELECT
Customer_ID,
COUNT(*) AS total_orders,
SUM(grand_total) AS total_spent,
AVG(price) AS avg_price
INTO #temp_customers
FROM #temp_orders
GROUP BY Customer_ID;

SELECT * FROM #temp_orders;

--sheet: s2
SELECT * FROM #temp_discounts;

--sheet: s3
SELECT * FROM #temp_customers;

Continue reading...