"TypeError:'NoneType'对象不可迭代"从 pandas read_sql [英] "TypeError: 'NoneType' object is not iterable" from pandas read_sql
问题描述
我正在尝试在python中使用pyodbc运行SQL命令. 其中SQL命令包含多个SELECT命令和IF语句.
I am trying to run a SQL command using pyodbc in python. in which SQL command contains multiple SELECT commands and IF statements.
但是我收到如下错误
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()
推荐答案
包含多个SQL语句的SQL命令文本称为匿名代码块.匿名代码块可以返回多个结果,每个结果可以是
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
- 行计数,
- 包含零个或更多数据行的结果集,或
- 一个错误.
以下示例失败...
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
返回其结果集.
... because the first SELECT ... INTO
returns a row count before the second SELECT
returns its result set.
解决方法是使用SET NOCOUNT ON;
启动匿名代码块,该代码块抑制行数并仅返回结果集:
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
这篇关于"TypeError:'NoneType'对象不可迭代"从 pandas read_sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!