Everything you need to know about SQLAlchemy

What is SQL Alchemy?

SQL Alchemy is an Object-Relational Mapper/Mapping-tool, or ORM: a library that developers use to create databases and manipulate their data without the need of knowing/using SQL.

There are other alternatives in Python like Peewee, and other languages have their own ORM's like PHP Eloquent or Java Hibernate.

Why use ORM?

ORM's have gained popularity because dealing with SQL language directly requires a lot of effort in many cases. The goal of any ORM is to simplify the maintenance of your data. This is done by creating objects to deal with database interactions.

With ORM's you won't have to type SQL again (95% of the time) and you will be able to work with objects.

Example:

To insert a user with SQL, you have to type:

1INSERT INTO user (name, last_name) VALUES ('Bob', 'Ross');

With an ORM your code keeps being familiar like this:

1user = User()
2user.name = "Bob"
3user.last_name = "Ross"
4
5# Add the user to the database
6db.session.add(user)
7
8# Similar to the Git commit, what this does is save all the changes you have made
9db.session.commit()

Just use the db.session.commit() function and everything you have done with your code will be translated into SQL language code.

Let's review the most typical database operations

Importing and initializing the application

To use SQL Alchemy, we need to install the Python flask library. Once we have done that, we will establish a connection to the database and define the db object, which is the most important thing to start working with.

1from flask import Flask
2from flask_sqlalchemy import SQLAlchemy
3
4app = Flask(__name__)
5app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////tmp/test.db"
6db = SQLAlchemy(app)

Creating our database

The first step will be defining our model:

1class Person(Base):
2    __tablename__ = "person"
3    # Here we define columns for the table 'Person'
4    # Notice that each column is also a normal Python instance attribute
5    id = Column(Integer, primary_key = True)
6    name = Column(String(250), nullable = False)
7
8    # The serialize method converts the object to a dictionary
9    def serialize(self):
10        return {
11            "id": self.id,
12            "name": self.name
13        }

INSERT: Inserting a record into the database

To insert a record in the database, it is first necessary to have the instance to be added. Then, add it to the database session and complete the action with a commit. The following code visualizes this functionality (replace <username_value> and <email_value> with the actual values you want to add):

1person = Person(username = <username_value>, email = <email_value>)
2db.session.add(person)
3db.session.commit()

SELECT: Searching or retrieving records from the database

There are 3 ways to retrieve data from a database:

1. Fetch all records from a particular table/model using MyModel.query.all()
2. Fetch one single record based on its primary key using MyModel.query.get(id)
3. Fetch a group of records based on a query Person.query.filter_by(arg1=value, arg2=value, ...)

1# Fetch all records from a particular table/model
2all_people = Person.query.all()
3all_people = list(map(lambda x: x.serialize(), all_people))
4
5# Get a single record based on its primary key, which in this case is the "id" of the person (only works with primary keys)
6person = Person.query.get(3)
7
8# Get a group of records based on a query, in this case, the string "alex" on the "name" column
9all_people = Person.query.filter_by(name = "alex")
10all_people = list(map(lambda x: x.serialize(), all_people))

DELETE: Removing a record from the database

To delete a record from the database, it is necessary to previously select the instance to be deleted (through its primary key, the id) and delete it using db.session.delete(person), according to the following example:

1person = Person.query.get(3)
2db.session.delete(person)
3db.session.commit()

UPDATE: Updating a record

To modify a record, you must first select it from the database, then you can work with it by changing its properties and commit it again, as shown in the following example:

1person = Person.query.get(3)
2person.name = "Bob"
3db.session.commit()

Transactions

A transaction is a sequence of operations (such as INSERT, UPDATE, SELECT) performed on your database. For a transaction to be complete, all operations must be successful. If one operation fails, the whole transaction fails.

All transactions must ensure 4 main properties (known as ACID properties): Atomicity, Consistency, Isolation and Durability.

A transaction ends with COMMIT or ROLLBACK.

COMMIT: session.commit()

The COMMIT command is used to permanently save the changes made in a transaction within the database.

When you use INSERT, UPDATE or DELETE, the changes made with these commands are not permanent, the changes made can be undone or, in other words, we can go back.

However, when you use the COMMIT command, the changes in your database will be permanent.

ROLLBACK: session.rollback()

The ROLLBACK command restores your database to your last COMMIT. You can also use it with the SAVEPOINT command to jump to a point you saved during an ongoing transaction.

Similarly, if you use UPDATE to make changes to your database, you can undo them using the ROLLBACK command, but only if you have not yet used the COMMIT command.

1db.session.rollback()

CHECKPOINT OR SAVEPOINT

The SAVEPOINT command is used to temporarily save a transaction so that you can return to a certain point using the ROLLBACK command if you need to. You can use it like this:

1db.session.begin_nested()

This command can be called many times, and with each call, a checkpoint called checkpoint is established and has a unique identifier associated with it.

Let's take, for example, the case that we want to prepare a pizza, and we prepare a database in which to enter the ingredients it contains. The base of this pizza that we want to prepare has three ingredients: mozzarella, tomato and olives. Our table is going to be called 'Pizza' and, after inserting the ingredients, it would look like this:

In addition, we have a list of extra ingredients we can add: we choose meat first, but then change our mind and want to replace it with mushrooms. We will also add pepperoni and bacon. Let's see how this transaction would be done:

1# Let's suppose that we already have the base ingredients added beforehand
2
3# Now we insert a new ingredient in the pizza, the meat
4ingredient = Ingredient()
5ingredient.name = "meat"
6ingredient.id = 4
7db.session.add(ingredient)
8
9# Now we do COMMIT and save it in the database so that we set the ingredient in the Pizza
10db.session.commit()
11
12# We replace the fourth ingredient, which was previously the meat, with the mushrooms
13ingredient = Ingredient.query.get(4)
14ingredient.name = "mushrooms"
15db.session.commit()
16
17# Save a "checkpoint"
18checkpoint_a = db.session.begin_nested()
19
20# Add pepperoni to the pizza
21ingredient = Ingredient()
22ingredient.name = "pepperoni"
23db.session.add(ingredient)
24db.session.commit()
25
26# One last "checkpoint" before adding the bacon ingredient
27checkpoint_b = db.session.begin_nested()
28
29# Add bacon
30ingredient = Ingredient()
31ingredient.name = "bacon"
32db.session.add(ingredient)
33db.session.commit()

Now, our 'Pizza' has the following ingredients:

However, before putting it in the oven, we decided we didn't want bacon, so we used the rollback:

1checkpoint_b.rollback()
2# Back to checkpoint B, not including the bacon

Finally, our 'Pizza' looks like this:

... I'm a bit hungry after reading this lesson!! Aren't you??