Executing sql-server transaction using python

Question

How can I execute a sql server transaction using pyodbc (python)?

I know that there is a “execute” method for one line and one list of arguments.

But now I want to execute two or more commands in a single transaction like this.

Is there any way to do this?

BEGIN TRANSACTION [Tran1]

BEGIN TRY

INSERT INTO [Test].[dbo].[T1]
  ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]
  SET [Title] = N'az2' ,[AVG] = 1
WHERE [dbo].[T1].[Title] = N'az'


COMMIT TRANSACTION [Tran1]

END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION [Tran1]
END CATCH  

GO

Answer 1

When you create a connection object, you can tell it not to execute every command you execute. Then, when you have executed all the commands that you wanted, you can transfer them in one transaction.

myconnection = pyodbc.connect(myconnectionstring, autocommit=False)
# your commands here
myconnection.commit()