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
– Parfait, Commented Jan 16, 2018 at 18:23

r.ook · Accepted Answer · 2018-01-16 21:55:46Z

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, 2018 at 21:55

r.ook

13.9k2 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ProgSky Over a year ago

Thanks. I will try your suggestion.

ProgSky Over a year ago

I converted my query to a stored procedure and was able to call it with read_sql_query().

N. Maks · Accepted Answer · 2021-09-21 11:22:44Z

4

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.

answered Sep 21, 2021 at 11:22

N. Maks

6865 silver badges21 bronze badges

Comments

Josef · Accepted Answer · 2021-07-06 16:05:29Z

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.

edited Jul 6, 2021 at 16:05

Josef

3,6522 gold badges26 silver badges27 bronze badges

answered Jul 6, 2021 at 12:52

Deniz Kizilca

592 bronze badges

Comments

Gedas Miksenas · Accepted Answer · 2021-10-04 11:09:14Z

0

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

answered Oct 4, 2021 at 11:09

Gedas Miksenas

1,07910 silver badges18 bronze badges

1 Comment

Jesper Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related