pymssql 多插入单次提交

Question

我正在尝试在数据库中进行多次插入操作，要求只有在所有插入成功的情况下，才能提交这些操作。如果有任何一个插入失败，就要撤销所有的操作。在TSQL中，我可以很简单地通过把整个代码块包裹起来来实现这个功能：

BEGIN TRANSACTION
BEGIN TRY
    --INSERTIONS GO HERE
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT Error = 1
END CATCH

现在，如果我想在Python中使用PYMSSQL来实现同样的功能，我尝试了以下方法：

sql=""" SOME SQL CODE HERE """

try:
    cursor = DB.execute(sql)
except:
    DB.rollback()
    print('Fail')
    return False

sql=""" SOME DIFFERENT SQL CODE HERE """

try:
    cursor = DB.execute(sql)
except:
    DB.rollback()
    print('Fail')
    return False

DB.commit()
print('Success')
return True

结果是没有任何事务被提交，数据库中没有看到任何变化。而且，如果我在进行一次插入后尝试提交，虽然插入成功了，但由于一些复杂的父子关系，这个任务要求要么所有插入都成功，要么一个都不插入。

我还应该提到，我使用单例模式保持了一个持久的数据库连接，这样可以覆盖常规的连接方法，但只允许打开一个连接：

def __init__(self, connid='one'):
     self.ensure_conn(connid)

 def ensure_conn(self, connid='one'):
     conn = getattr(self.connection_stack, connid, None)
     if conn is None:
         conn = pymssql.connect(
             self.server, self.user, self.password, self.database)
         self.connection_stack[connid] = conn

 def conn(self, connid='one'):
     self.ensure_conn(connid)
     if connid in self.connection_stack:
         return self.connection_stack[connid]
     else:
         return None

我尝试在网上寻找相关的例子，但这个问题似乎有点独特，所以任何建议或意见都非常感谢。

Answer 1

我找到的解决办法是使用TSQL中的存储过程，并利用pymssql的远程过程调用功能。这需要在SQL账户层面有EXEC权限，但为了安全考虑，可以限制只对特定的存储过程开放权限。

这样做可以让你在不同的表上进行多条插入时，享受到事务处理的好处。

cursor.callproc(name, args)