我正在嘗試在 python 中使用 pyodbc 運行 SQL 命令。其中 SQL 命令包含多個 SELECT 命令和 IF 語句。但我收到如下錯誤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貢獻1998條經驗 獲得超6個贊
包含多個 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
添加回答
舉報
0/150
提交
取消