Python Pandas read_sql_query “'NoneType' object is not iterable” error

Question

I am trying to execute a sql and save a result into Panda Dataframe. here is my code.

        dbserver = 'validserver'
        filename = 'myquery.sql'
        database  ='validdb'       
        conn = pyodbc.connect(r'Driver={SQL Server};Server=' + dbserver + 
            ';Database=' + database + ';Trusted_Connection=yes;')
        fd = open(filename, 'r')
        resultingData = pd.read_sql_query(fd.read(),conn)
        fd.close()
        conn.close()

line pd.read_sql_query(fd.read(),conn) continues to give me error 'NoneType' object is not iterable” error

I can run myquery.sql in a sql server window with results. I do have SET NOCOUNT ON;

Any clue what am I missing here and how do I debug this? myquery.sql has few #temp tables and joins. Result has about 75k rows. Thanks all.

Update:

I can not post the exact query but this is how query looks like.

SET NOCOUNT ON;

SELECT SourceID, PeriodEndDate = MAX(PeriodEndDate)
INTO #SourceDate 
FROM table1
WHERE PERIODENDDATE <= 20171229
GROUP BY SourceID

SELECT RS.*, R.TypeCode INTO #final
FROM table2 RS
INNER JOIN #SourceDate SD ON SD.id = RS.id
INNER JOIN table3 R ON R.id = RS.id

select * from #final

Answer 1

Sometimes tsql warnings like Warning: Null value is Eliminated by an Aggregate or Other SET Operation can trigger the same problematic behaviour for pd.read_sql(). I found that additionally setting these warnings off helps in this situation. For that just add

SET ANSI_WARNINGS OFF;

besides your SET NOCOUNT ON; statement.

Of course you shouldn't just ignore these waringns. Check if your result is as expected before setting the warnings off.

Answer 2

From what I understand, read_sql_query() would only return the results from your first statement anyhow. In which case, it's SET NOCOUNT ON; and as you can see, will return None, which is why your code failed. IMO You should be optimizing your SQL statement to return one table instead of multiple in the process (as you only want to read from #final I assume).

If you really want to have multiple sql query in a dataframe, you can use lists and split as mentioned in this thread:

Pandas read_sql query with multiple selects

Answer 3

As @r.ook mentioned, use SET NOCOUNT ON; for each statement that returns "NoneType". So, your statement might be:

SET NOCOUNT ON;
SELECT SourceID, PeriodEndDate = MAX(PeriodEndDate)
INTO #SourceDate 
FROM table1
WHERE PERIODENDDATE <= 20171229
GROUP BY SourceID

SET NOCOUNT ON; --Added "SET NOCOUNT ON;" here as well.
SELECT RS.*, R.TypeCode INTO #final
FROM table2 RS
INNER JOIN #SourceDate SD ON SD.id = RS.id
INNER JOIN table3 R ON R.id = RS.id

select * from #final

You can also check this link for more details.