我正在嘗試在 python 中使用 pyodbc 運(yùn)行 SQL 命令。其中 SQL 命令包含多個(gè) SELECT 命令和 IF 語句。但我收到如下錯(cuò)誤columns = [col_desc[0] for col_desc in cursor.description]TypeError: 'NoneType' object is not iterableimport pyodbcimport pandas as pdconn = pyodbc.connect("DRIVER={SQL Server};" "SERVER=server_name;" "DATABASE=master;" "Trusted_Connection=yes;")cursor = conn.cursor()script="""If object_id ('tempdb..#Temp1')is not nulldrop table #Temp1Select distinct a1.some_ID into #Temp1from DOC.dbo.Document_tbl (NOLOCK)a1from #Temp1 a1If object_id ('tempdb..#Temp2')is not nullDrop table #Temp2select distinct v2.some_datainto #Temp2 from tbl_name (nolock) v2 If object_id ('tempdb..#Results')is not nulldrop table #Resultsselect distinct a1.*,b1.####into #Resultsfrom #Temp1 a1left 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()
2 回答
米琪卡哇伊
TA貢獻(xiàn)1998條經(jīng)驗(yàn) 獲得超6個(gè)贊
包含多個(gè) SQL 語句的 SQL 命令文本稱為匿名代碼塊。一個(gè)匿名代碼塊可以返回多個(gè)結(jié)果，其中每個(gè)結(jié)果可以是
行數(shù)，
包含零行或多行數(shù)據(jù)的結(jié)果集，或
一個(gè)錯(cuò)誤。
以下示例失敗...
sql = """\
SELECT 1 AS foo INTO #tmp;
SELECT * FROM #tmp;
"""
df = pd.read_sql_query(sql, cnxn)
# TypeError: 'NoneType' object is not iterable
...因?yàn)榈谝粋€(gè)在第二個(gè)返回其結(jié)果集SELECT ... INTO之前返回行數(shù)。SELECT
修復(fù)方法是啟動(dòng)匿名代碼塊，使用SET NOCOUNT ON;該代碼塊抑制行數(shù)并僅返回結(jié)果集：
sql = """\
SET NOCOUNT ON;
SELECT 1 AS foo INTO #tmp;
SELECT * FROM #tmp;
"""
df = pd.read_sql_query(sql, cnxn)
# no error
夢里花落0921
TA貢獻(xiàn)1772條經(jīng)驗(yàn) 獲得超6個(gè)贊
對(duì)于仍然遇到此錯(cuò)誤的其他任何人，我發(fā)現(xiàn)對(duì)于某些語句（聚合空值的窗口函數(shù)），我還需要包含SET ANSI_WARNINGS OFF;.
添加回答
舉報(bào)
0/150
提交
取消