python - 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(). 我怎样才能找到我的陈述的结果？

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

标签： pythonsql-serverpyodbc

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