Python & SQL Server: Manage Database Transactions

**If you don’t have a premium account you can read this post for free here**

Intro

In our last post we used SQLAlchemy to bulk load data into SQL Server. Now that we can bulk load data we want to perform other actions on our database. In todays post we want follow a common loading strategy and TRUNCATE the target table before loading it. Adding this functionality to our script leaves us with a choice to make in how we want to execute our SQL statements and manage database transactions.

Transaction Management

Using SQLAlchemy we have a couple different options to manage our transactions. The first option is their commit as you go approach with Connection.commit(). The second option is begin once with Engine.begin().

Connection.commit()

To get started we need to add a new import. SQLAlchemy text will prepare our SQL Statement to pass to the connection.

from sqlalchemy import text

Once we have the import in place we are going to create a new function to execute a passed in SQL statement. Here is a breakdown of what our new function is doing:

1. Use the text() function to prepare out SQL statement