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
  2. Establish a connection to the database
  3. Execute the SQL statement
  4. Commit the transaction
def execute_statement(sql_statement):
    try:
        sql = text(sql_statement)
        with engine.connect() as connection:
            connection.execute(sql)
            connection.commit()
    except Exception as e:
        print(f"Error occurred: {e}")

Now all we have to do is call the function before loading our dataset.

sql_statement = 'TRUNCATE TABLE dbo.CT_Real_Estate;'
execute_statement(sql_statement)

The entire script can be found on GitHub.

Engine.begin()

This method is slightly easier to implement for our use case. We don’t require any additional imports for this to work so we can jump right into writing our function. The function is also simpler because it can simply take in a string and execute it against the target server. We don’t need an explicit commit because this method automatically commits the transaction for us.

def execute_statement(sql_statement):
    try:
        # Execute SQL Statement
        with engine.begin() as con:
            con.exec_driver_sql(sql_statement)
    except Exception as e:
        print(f"Error occurred: {e}")

The entire script can be found on GitHub.

Differences

  1. For Connection.commit() you need to import text from SQL Alchemy and format your SQL statement with it before executing the statement.
  2. You need to have an additional line of code to commit the transaction with Connection.commit().

Wrapping Up

For our use case I chose to use Engine.Begin(). Our use case is fairly small so I like that I can keep my codebase to a minimum and skip the extras that Connection.commit() requires. For more advanced use cases where transaction handling plays a bigger role I would definitely reconsider these options.