来自 pandas read_sql 的“TypeError：'NoneType' 对象不可迭代”

Question

我正在尝试在 python 中使用 pyodbc 运行 SQL 命令。其中 SQL 命令包含多个 SELECT 命令和 IF 语句。

但我收到如下错误

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

Answer 1

包含多个 SQL 语句的 SQL 命令文本称为匿名代码块。一个匿名代码块可以返回多个结果，其中每个结果可以是行数，包含零行或多行数据的结果集，或一个错误。以下示例失败...sql = """\SELECT 1 AS foo INTO #tmp;SELECT * FROM #tmp;"""df = pd.read_sql_query(sql, cnxn)# TypeError: 'NoneType' object is not iterable...因为第一个在第二个返回其结果集SELECT ... INTO之前返回行数。SELECT修复方法是启动匿名代码块，使用SET NOCOUNT ON;该代码块抑制行数并仅返回结果集：sql = """\SET NOCOUNT ON;SELECT 1 AS foo INTO #tmp;SELECT * FROM #tmp;"""df = pd.read_sql_query(sql, cnxn)# no error

Answer 2

对于仍然遇到此错误的其他任何人，我发现对于某些语句（聚合空值的窗口函数），我还需要包含SET ANSI_WARNINGS OFF;.