read_from_sql() throws TypeError: 'NoneType' object is not iterable in sql.py

[Semiarty]

Code Sample, a copy-pastable example if possible

import pyodbc
import sys
from contextlib import contextmanager
import textwrap
import pandas as pd

@contextmanager
def open_db_connection(connection_string):
    pyodbc.pooling = False
    connection = pyodbc.connect(connection_string)

    try:
        yield connection
    except pyodbc.DatabaseError as err:
        error, = err.args
        sys.stderr.write(error.message)
    finally:
        connection.close()

def create_query_string(sql_full_path):
    with open(sql_full_path, 'r') as f_in:
        lines = f_in.read()

    query_string = textwrap.dedent("""{}""".format(lines))
    return query_string

def query(server, database, driver, queryObj):

    connection_string = 'DRIVER={};SERVER={};DATABASE={};Trusted_Connection=yes'.format(driver, server, database)
    
    noCount = """ SET NOCOUNT ON; """
    with open_db_connection(connection_string) as conn:
        res = pd.read_sql_query(noCount+queryObj, conn)

    return res


def get_data_set(sql_file_name, db_info):
    query_string = create_query_string(sql_file_name+'.sql')
    server = db_info['server']
    database = db_info['database']
    driver = db_info['driver']
    df = query(server, database, driver, query_string)
    #df.to_excel(sql_file_name+'_backup.xlsx', index=False)
    return df

db_info = {'server' : 'wsp', 'database' : 'fin', 'driver' : 'SQL Server'}

df = get_data_set('equities', db_info)

SQL part

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
select * 
into #tmp 
from fin.dbo.items_info

IF OBJECT_ID('tempdb..#test1') IS NOT NULL DROP TABLE #test1;
select t.PositionID, b.SecurityID
into #test1
from #tmp as t
inner join fin.dbo.items  as b on (b.PositionID = t.PositionID and b.StudyDate = '20191230')
where t.ast = 'eq';

IF OBJECT_ID('tempdb..#beta_index') IS NOT NULL DROP TABLE #beta_index;
select distinct isin, beta_index into #beta_index from md.data.equity; 

IF OBJECT_ID('tempdb..#test2') IS NOT NULL DROP TABLE #test2;
select t.PositionID,    
       case
       when count(i.beta_index)=0 then 1
       else count(i.beta_index)
       end as noIndex
into #test2 
from #test1 as t 
   left join #beta_index as i on (t.SecurityID = i.isin)
   group by t.PositionID;

select * from #test2

Problem description

I am trying to get a dataframe from the above query, however it throws TypeError: 'NoneType' object is not iterable when trying to pull results from #test2 table ('SET NOCOUNT ON;' is being used) . Everything up to that point and after works perfectly, however it looks like it has something to do with counting and grouping. I can pull #test1 table above just fine. No issues with other lengthy queries with insert, update, group though.

Additionally, this query works fine when running it in SSMS v18.4 (using it as a driver).

Expected Output

I expect to get an output from #table2 table. The select statement should return a non empty table (which it does in SSMS).

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.6.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 78 Stepping 3, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 0.25.3
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 41.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.4.2
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.11.1
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : 4.4.2
matplotlib : 3.1.2
numexpr : None
odfpy : None
openpyxl : 3.0.3
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.4.1
sqlalchemy : None
tables : None
xarray : None
xlrd : 1.2.0
xlwt : None
xlsxwriter : None

[Semiarty]

I have tried connecting to db manually with pyodbc and fetching the table - it seems to throw "ProgrammingError: No results. Previous SQL was not a query." when fetching #test2, but works when fetching #test1. Looks like it is a pyodbc issue.

[Semiarty]

cursor.nextset()
cursor.fetchone()
resulted in the expected ouput. Closing as it's not a pandas issue.

[Semiarty]

I found the solution to my problem, so just in case someone else runs into this issue, here's my comment from pyodbc ticket:

So I just realized that I was ignoring/disregarding warning messages in SSMS, which, I believe, results in cursor not being a query and pyodbc throwing ProgrammingError "No results. Previous SQL was not a query."

The warning:
Warning: Null value is eliminated by an aggregate or other SET operation.

SET ANSI_WARNINGS OFF solved the issue.

Inserting "SET ANSI_WARNINGS OFF" at the beginning of SQL query worked.

[JaiSharma-10]

This helps a lot !!!