pyodbc-获取alter语句的结果

Question

我正试图通过pyodbc获得一个"alter"语句的结果。

我的代码：

...
connection = pyodbc.connect(connection_string, autocommit=True)
cursor = connection.cursor()
query = "ALTER INDEX index_name ON schema.table REBUILD"
cursor.execute(query)
...

由于这不是一个查询，我不能使用curosr.fetchall()。我怎样才能找到我的陈述结果？

有些索引会失败，因为运行脚本的用户没有权限(或其他原因(，我想显示服务器抛出的错误消息(光标没有引发异常(。

Answer 1

您可以在try/catch块中动态执行语句(exec或sp_executesql(，该块返回成功消息&错误：

--exec
begin try
exec('
--statement goes here
ALTER INDEX index_name ON schema.table REBUILD
');
select cast(N'success' as nvarchar(2048)) as errormsg, 0 as errornumber;
end try
begin catch
select error_message() as errormsg, error_number() as errornumber;
--..maybe some transaction handling...for statements with transactions..
--if @@trancount > 0
--begin
--   rollback transaction
--end
end catch
--sp_executesql
declare @sql nvarchar(max) = 'ALTER INDEX index_name ON schema.table REBUILD';
begin try
exec sp_executesql @stmt = @sql;

select cast(N'success' as nvarchar(2048)) as errormsg, 0 as errornumber;
end try
begin catch
select error_message() as errormsg, error_number() as errornumber;
end catch

Try/catch并不完全正常，一些错误会中止连接(取决于错误的严重程度(，调用方应该有某种异常处理：

begin try
exec ('
--statement goes here
raiserror(''xxx'', 20, 1) with log
');
select cast(N'success' as nvarchar(2048)) as errormsg, 0 as errornumber;
end try
begin catch
select error_message() as errormsg, error_number() as errornumber;
end catch
go
--transaction handling
begin try
exec ('
--statement goes here
begin transaction
declare @a int;
select @a = ''A'' --aborts the batch
commit transaction  --<-- this is not executed
');
select cast(N'success' as nvarchar(2048)) as errormsg, 0 as errornumber;
end try
begin catch
select error_message() as errormsg, error_number() as errornumber;
end catch
--error here: Uncommittable transaction....
go
begin try
exec ('
--statement goes here
begin transaction
declare @a int;
select @a = ''A'' --aborts the batch
commit transaction  
');
select cast(N'success' as nvarchar(2048)) as errormsg, 0 as errornumber;
end try
begin catch
select error_message() as errormsg, error_number() as errornumber;
if @@trancount > 0
begin
rollback transaction;
end
end catch