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

Please show contents of *myquery.sql*. Only single SQL `SELECT` statements should be passed into `read_sql()`. — Parfait, Jan 16 '18 at 18:23

score 7 · Accepted Answer · answered Jan 16 '18 at 21:55

7

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

answered Jan 16 '18 at 21:55

r.ook

13,466
2
22
39

Thanks. I will try your suggestion. – ProgSky Jan 17 '18 at 14:11
1

I converted my query to a stored procedure and was able to call it with read_sql_query(). – ProgSky Jan 17 '18 at 20:12

score 4 · Answer 2 · answered Sep 21 '21 at 11:22

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.

score 2 · Answer 3 · edited Jul 06 '21 at 16:05

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.

score 0 · Answer 4 · answered Oct 04 '21 at 11:09

0

Please try to remove the semicolon after "SET NOCOUNT ON" and then run the script.

answered Oct 04 '21 at 11:09

Gedas Miksenas

959
7
15

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/29989460) – Jesper Oct 04 '21 at 13:32

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

4 Answers4

Linked