Python Pandas read_sql_query “'NoneType' 对象不可迭代"错误 [英] Python Pandas read_sql_query “'NoneType' object is not iterable” error

问题描述

我正在尝试执行 sql 并将结果保存到 Panda Dataframe 中.这是我的代码.

I am trying to execute a sql and save a result into Panda Dataframe. here is my code.

        dbserver = 'validserver'
        filename = 'myquery.sql'
        database  ='validdb'       
        conn = pyodbc.connect(r'Driver={SQL Server};Server=' + dbserver + 
            ';Database=' + database + ';Trusted_Connection=yes;')
        fd = open(filename, 'r')
        resultingData = pd.read_sql_query(fd.read(),conn)
        fd.close()
        conn.close()

line pd.read_sql_query(fd.read(),conn) 继续给我错误NoneType"对象不可迭代"错误

line pd.read_sql_query(fd.read(),conn) continues to give me error 'NoneType' object is not iterable" error

我可以在带有结果的 sql server 窗口中运行 myquery.sql.我确实有 SET NOCOUNT ON;

I can run myquery.sql in a sql server window with results. I do have SET NOCOUNT ON;

任何线索我在这里遗漏了什么，我该如何调试?myquery.sql 很少有#temp 表和连接.结果大约有 75k 行.谢谢大家.

Any clue what am I missing here and how do I debug this? myquery.sql has few #temp tables and joins. Result has about 75k rows. Thanks all.

更新:

我无法发布确切的查询，但这就是查询的样子.

I can not post the exact query but this is how query looks like.

SET NOCOUNT ON;

SELECT SourceID, PeriodEndDate = MAX(PeriodEndDate)
INTO #SourceDate 
FROM table1
WHERE PERIODENDDATE <= 20171229
GROUP BY SourceID

SELECT RS.*, R.TypeCode INTO #final
FROM table2 RS
INNER JOIN #SourceDate SD ON SD.id = RS.id
INNER JOIN table3 R ON R.id = RS.id

select * from #final

推荐答案

据我所知，read_sql_query() 无论如何只会返回您的第一个语句的结果.在这种情况下，它是 SET NOCOUNT ON; 并且如您所见，将返回 None，这就是您的代码失败的原因.IMO 您应该优化您的 SQL 语句以在此过程中返回一个表而不是多个表(因为您只想从 #final 中读取).

From what I understand, read_sql_query() would only return the results from your first statement anyhow. In which case, it's SET NOCOUNT ON; and as you can see, will return None, which is why your code failed. IMO You should be optimizing your SQL statement to return one table instead of multiple in the process (as you only want to read from #final I assume).

如果你真的想在一个数据框中有多个 sql 查询，你可以使用这个线程中提到的 lists 和 split :

If you really want to have multiple sql query in a dataframe, you can use lists and split as mentioned in this thread:

多选的 Pandas read_sql 查询

这篇关于Python Pandas read_sql_query “'NoneType' 对象不可迭代"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！