"TypeError: 'NoneType' object is not iterable" from pandas read_sql

Question

I am trying to run a SQL command using pyodbc in python. in which SQL command contains multiple SELECT commands and IF statements.

but I am getting an error as follows

columns = [col_desc[0] for col_desc in cursor.description]
TypeError: 'NoneType' object is not iterable

import pyodbc
import pandas as pd
conn = pyodbc.connect("DRIVER={SQL Server};"
                "SERVER=server_name;"
                "DATABASE=master;"
                "Trusted_Connection=yes;")
cursor = conn.cursor()
script="""

If object_id ('tempdb..#Temp1')is not null
drop table #Temp1
Select distinct  a1.some_ID into #Temp1
from DOC.dbo.Document_tbl (NOLOCK)a1
from #Temp1 a1
If object_id ('tempdb..#Temp2')is not null
Drop table #Temp2
select distinct v2.some_data
into #Temp2 from tbl_name (nolock) v2 

If object_id ('tempdb..#Results')is not null
drop table #Results
select distinct a1.*,b1.####
into #Results
from #Temp1 a1
left join #Temp2 b1 on a1.## = b1.##
Select * from #Results
"""

df = pd.read_sql(script, cnxn)
writer = pd.ExcelWriter('result.xlsx')
df.to_excel(writer, sheet_name ='bar')
writer.save()

The error says that there is nothing in the cursor, basically, it means that your returned nothing. — vb_rises
– vb_rises, Commented Sep 10, 2019 at 8:35
Please remove those NOLOCK query hints unless you understand exactly why you need them and the risks of using them, including retrieving invalid data and data that does not exist — alroc
– alroc, Commented Sep 10, 2019 at 9:29

Gord Thompson · Accepted Answer · 2019-09-10 11:31:30Z

5

SQL command text that contains multiple SQL statements is called an anonymous code block. An anonymous code block can return multiple results, where each result can be

a row count,
a result set containing zero or more rows of data, or
an error.

The following example fails ...

sql = """\
SELECT 1 AS foo INTO #tmp;
SELECT * FROM #tmp;
"""
df = pd.read_sql_query(sql, cnxn)
# TypeError: 'NoneType' object is not iterable

... because the first SELECT ... INTO returns a row count before the second SELECT returns its result set.

The fix is to start the anonymous code block with SET NOCOUNT ON; which suppresses the row count and only returns the result set:

sql = """\
SET NOCOUNT ON;
SELECT 1 AS foo INTO #tmp;
SELECT * FROM #tmp;
"""
df = pd.read_sql_query(sql, cnxn)
# no error

answered Sep 10, 2019 at 11:31

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

Comments

Suspicious_Gardener · Accepted Answer · 2021-09-09 17:32:54Z

3

For anyone else who is still getting this error, I have found that for some statements (A window function that aggregated null values) I also need to include SET ANSI_WARNINGS OFF;.

answered Sep 9, 2021 at 17:32

Suspicious_Gardener

1263 bronze badges

Collectives™ on Stack Overflow

"TypeError: 'NoneType' object is not iterable" from pandas read_sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related