资源关闭错误/属性错误时使用 pandas read_sql_query 属性错误：'NoneType'对象没有属性'fetchall'

Question

我正在尝试使用pandas.read_sql_query读取sql查询。

我遇到的问题几周前还不在这里。问题是当我阅读一些查询时，出现以下错误：

df = pd.read_sql_query(sql = query_string, con = engine)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~.condaenvstestlibsite-packagessqlalchemyengineresult.py in _fetchall_impl(self)
1160         try:
-> 1161             return self.cursor.fetchall()
1162         except AttributeError:
AttributeError: 'NoneType' object has no attribute 'fetchall'
During handling of the above exception, another exception occurred:
ResourceClosedError                       Traceback (most recent call last)
...
~.condaenvstestlibsite-packagessqlalchemyengineresult.py in _non_result(self, default)
1166         if self._metadata is None:
1167             raise exc.ResourceClosedError(
-> 1168                 "This result object does not return rows. "
1169                 "It has been closed automatically."
1170             )
ResourceClosedError: This result object does not return rows. It has been closed automatically.

这仅在使用某些查询而不是其他查询时发生，所以我知道这不是我的连接。我在两个语句上都设置了 NOCOUNT，并且都使用临时表。谁能指导我为什么一个查询有效而不是另一个查询？两者都在 SSMS 中工作正常。我试图在下面提供两者的基本结构。第一个有效，第二个不起作用。

set nocount on

DECLARE      @var1  varchar(20)= 'sometext'                                                             

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL                                                                     
DROP TABLE #temp1                                                                       
  
select * into  #temp1 from  some_function_view WHERE  [x] = @var1                                                                       
  
IF OBJECT_ID('tempdb..#temp2', 'U') IS NOT NULL                                                                     
DROP TABLE #temp2                                                                       
select * into #temp2 FROM another_view JOIN ...                                                     
  
SELECT Main.[y]...                                                                                                                                      
FROM (                                                                      
  
SELECT *                                                                                                                                    
FROM another_view                                                                       
JOIN...                                                                                                                                     
WHERE...                                                                
GROUP BY...
) Main                                                                      
JOIN #temp2...          

set nocount on
begin try drop table #temp1 end try begin catch end catch
begin try drop table #temp2 end try begin catch end catch
begin try drop table #temp3 end try begin catch end catch
DECLARE @x  int = 202007
DECLARE @y  int = 2016  
SELECT * into #temp1 FROM a_view
SELECT  * into #temp2       
FROM a_view     
JOIN (SELECT...FROM a_view WHERE...GROUP BY ...)

SELECT... into #temp3 FROM a_view       
JOIN... 
LEFT JOIN...
WHERE x = @x, y = @y... 
Group BY...
SELECT *
FROM #policy as FPFR        
JOIN #temp1...
JOIN #temp2...
JOIN #temp3...
ORDER BY...

Answer 1

所以我想通了。我需要在我的SQL查询中包含"SET ANSI_WARNINGS OFF">和"SET NOCOUNT ON"。

拥有一个或另一个是不够的。两者的组合设法捕获SQL抛出的任何存储过程错误，以便Python可以成功读取它。