我使用pyodbc从Microsoft SQL Server检索数据。查询的格式为
SET NOCOUNT ON --Ignore count statements
CREATE TABLE mytable ( ... )
EXEC some_stored_procedure
INSERT mytable
--Perform some processing...
SELECT *
FROM mytable
存储过程对包含NULLs
的值执行一些聚合,从而发出形式为Warning: Null value is eliminated by an aggregate or other SET operation.
的警告。这将导致pyodbc无法检索数据,并显示错误消息No results. Previous SQL was not a query.
我已经尝试通过设置SET ANSI_WARNINGS OFF
来禁用警告。但是,查询失败,并显示错误消息Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
。
有没有可能
- 禁用警告
- 还是让pyodbc忽略警告?
注意,我没有修改存储过程的权限。
将查询结果存储在临时表中,并将语句作为两个查询执行:
with pyodbc.connect(connection_string) as connection:
connection.execute(query1) #Do the work
result = connection.execute(query2) #Select the data
data = result.fetchall() #Retrieve the data
第一个查询做了比较重的工作,它的形式是
--Do some work and execute complicated queries that issue warning messages
--Store the results in a temporary table
SELECT some, column, names
INTO #datastore
FROM some_table
第二个查询检索数据,格式为
SELECT * FROM #datastore
因此,在执行第一个查询时发出所有警告消息。在执行第二个查询期间,它们不会干扰数据检索。
我很幸运地通过在有问题的视图或存储过程周围打开和关闭ansi_warnings来减轻这个错误。
/* vw_someView aggregates away some nulls and presents warnings that blow up pyodbc */
set ANSI_WARNINGS off
select *
into #my_temp
from vw_someView
set ANSI_WARNINGS on
/* rest of query follows */
这假定产生聚合警告的实体也不需要打开警告。如果它报错,这可能意味着实体本身有这样一部分代码需要切换ansi_warnings(或者重写以消除聚合)
需要注意的是,如果我尝试将此切换作为跨服务器查询运行,则该切换仍然返回"异构"警告。此外,在调试过程中,很容易进入一种状态,即当您没有意识到ansi_warnings被关闭,并且您开始无缘无故地获得异构错误。只需运行"set ANSI_WARNINGS on"行,就可以使自己恢复到良好的状态。
最好是添加try: except: block
sql="sp_help stored_procedure;"
print(">>>>>executing {}".format(sql))
next_cursor=cursor.execute(sql)
while next_cursor:
try:
row = cursor.fetchone()
while row:
print(row)
row = cursor.fetchone()
except Exception as my_ex:
print("stored procedure returning non-row {}".format(my_ex))
next_cursor=cursor.nextset()