pandas.read_sql_query() throws TypeError: 'NoneType' object is not iterable

Question

I am using pandas.read_sql_query function to read from a few sql files. One query throws an error at one particular bit which I have singled out.

(python bit - nothing exotic and works with other queries)

@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()

noCount = """ SET NOCOUNT ON; """
with open_db_connection(connection_string) as conn:
    res = pd.read_sql_query(noCount+queryObj, conn)

The following bit of sql throws an error and I have no idea why it could be so. Preceding statements and various temp tables work and can be collected with pandas.read_sql_query(), however at the following bit it breaks.


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

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

This should return data from test2. One note though - it executes and runs perfectly fine with SQL Server Management Studio.

When or where are #test1 and #beta_index created? Temp table evaporate as soon as the session thread ends. — John Cappelletti, Feb 05 '20 at 14:49
@JohnCappelletti they are created before (I have now included #test1. I ommited them in order not to overcrowd the thread. The thing is that everything up to that point works and can be called without no errors. Both #tmp and #test1 were successfully read (when setup as needed) into my df. — semiarty, Feb 05 '20 at 14:56

score 1 · Answer 1 · answered Feb 11 '20 at 08:51

The issue all along was 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." and consequently pandas.read_sql_query() crashing.

The warning:

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

"SET ANSI_WARNINGS OFF" at the beginning of the query solved the issue.

I don't think this is the best practise, though in my case I can disregard these warnings.

score 0 · Answer 2 · edited Mar 06 '23 at 14:37

Got this answer while searching other webistes as I was getting the same issue but for teradata.

As Teradata doesnt have either SET NOCOUNT ON or SET ANSI_WARNINGS OFF had to resort to other ways to solve this problem.

Instead of using read_sql, used cursor object to run the query. This resolved my problem

Issue

pd.read_sql(query, session)

Used below

curr = session.cursor()
curr.execute(query)

Got answer frow https://stackoverflow.com/a/49640766/

pandas.read_sql_query() throws TypeError: 'NoneType' object is not iterable

2 Answers2