Utilizing transactions to maintain consistency

A transaction in a relational database provides us with the power to solve the issues that we have just discussed. In terms of a relational database, a transaction can be considered an envelope consisting of multiple database queries that are either executed as one task or are completely reverted if any of them fails. We can also consider a transaction an atomic unit in terms of database's operations, where even a single failure will revert the whole transaction. But, isn't this exactly what we require to solve the issue with our database consistency?

Now, let's take a look at how our ORM solution can help us to implement transactional support.

To understand this, let's take an example. Our BugZot application has been newly developed and will be replacing an existing application, which the Omega Corporation was using previously. The IT team has provided us with a CSV dump (hail the great CSV) of the user records, which they expect us to restore in our new application.

One of the engineers in the BugZot team went creative and developed a script in Python that parses the provided CSV file and returns a dictionary that can be mapped to one of our models (role, user, ActivationKeys), and has asked us to use these dictionaries to start loading the data into our new database schema. So, let's take a look at how we can do this while leveraging transactions in our ORM. The following code snippet takes a look at this example:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from .models import Role, User, ActivationKey

db_engine = create_engine("postgresql://bugzot:alphonso@localhost/bugzot")

Session = sessionmaker(bind=db_engine)

def load_data(db_record):
  role = get_role(db_record)
  user = get_user(db_record)
  activation_key = get_activation_key(db_record)
  db_session = Session()
  db_session.add(role)
  db_session.add(user)
  db_session.add(activation_key)
  try:
    db_session.commit()
  except:
    db_session.rollback()
    raise
  finally:
    db_session.close()

#Call to the load_data method

In this example, we utilized sessions to implement a transaction through SQLAlchemy. Now let's take a look at this and understand what we did.

In the first two lines, we imported the create_engine and sessionmaker objects from sqlalchemy. The create_engine object is used to create a new connection to the database by providing the object with the database connection URI.

The sessionmaker object is used to initialize a database session object, which can be used to further initialize new transactions.

In the next line, we created a new connection to the database engine by providing the URI of the database engine:

db_engine = create_engine("postgresql://bugzot:alphonso@localhost/bugzot")

Once the database engine had been created, we then initialized a session by calling the sessionmaker object with the engine parameter whose value is equivalent to the one we initialized in the previous step:

Session = sessionmaker(bind=db_engine)

Next, we define our load_data method, which takes as a parameter a dictionary that represents a single user data record. The method parses the dictionary and creates three different model objects, a role, a user, and an activation_key.

The next thing we do is add these models to the session one by one:

db_session.add(role)
db_session.add(user)
db_session.add(activation_key)

Now, once the data has been added to the session, we are all set to write this data to our database. To do this, we call the commit() method from the session object. The commit method obtains a connection to the database, starts a transaction, runs the queries, and closes the transaction.

Since, as we know, any inconsistency in any of the models described can cause the transaction to be aborted, we wrap our commit() call in a try-except block. If the commit() method raises an exception, we roll back the whole session, effectively undoing all the changes that we made to the database.

Once all of this is done, we call the close() method of session in the final block to clean up any of the resources that may be remaining.

Transactions provide us with a lot of power to keep our database in a consistent state.If case the database server experiences a crash while a transaction is happening, we can have a certain amount of assurance that no changes will have been made to the final database state attributed to the atomic nature of a transaction.

Now, let's take a look at one of the important aspects of how relationship loading works in SQLAlchemy, and how we can optimize our data access patterns.