Self-paced

Explore our extensive collection of courses designed to help you master various subjects and skills. Whether you're a beginner or an advanced learner, there's something here for everyone.

Bootcamp

Learn live

Join us for our free workshops, webinars, and other events to learn more about our programs and get started on your journey to becoming a developer.

Upcoming live events

Learning library

For all the self-taught geeks out there, here is our content library with most of the learning materials we have produced throughout the years.

It makes sense to start learning by reading and watching videos about fundamentals and how things work.

Search from all Lessons


LoginGet Started
← Back to Lessons
Edit on Github

Everything you need to know about SQLAlchemy

What is SQL Alchemy?
Why use ORM?
  • Example:

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 ORMs like PHP Eloquent or Java Hibernate.

Why use ORM?

ORMs 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 ORMs 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 4 # Here we define columns for the table "Person" 5 # Notice that each column is also a normal Python instance attribute 6 id = Column(Integer, primary_key = True) 7 name = Column(String(250), nullable = False) 8 9 # The serialize method converts the object to a dictionary 10 def serialize(self): 11 return { 12 "id": self.id, 13 "name": self.name 14 }

INSERT: Inserting a record into the database

To insert a record in the database, it is first necessary to have the instance 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.

ACID Properties

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.

SQL checkpoint

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:

SQL table pizza

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:

SQL Pizza model

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:

SQL Pizza model rollback

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