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 to it like SQLAlchemy 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 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 it does is save all the changes you have made
9db.session.commit()

You can just say: db.session.commit() and all the things you have done in your code will be translated into SQL language code.

Let's review the most typical database operation

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 Database Record

All you have to do is create a new Person object, add it into the database session and commit! Just replace <username_value> and <email_value> with the real values you want, added below.

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

SELECT: Fetching / Retrieving Records

There are 3 ways to retrieve data from a database: 1. Fetch all record 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# here is how to fetch all people
2all_people = Person.query.all()
3all_people = list(map(lambda x: x.serialize(), all_people))
4
5# here is how to fetch a group of people with name = alex
6all_people = Person.query.filter_by(name='alex')
7all_people = list(map(lambda x: x.serialize(), all_people))
8
9# here is how to fetch the person with id=3 (only works with primary keys)
10person = Person.query.get(3)

DELETE: Removing a Database Record.

All you have to do is select the instance of a Person you would like to delete (i.e. by its id) and delete it by typing db.session.delete(person).

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

UPDATE: Updating a Record

To update you need first to retrieve/select the record from the database, then you can update whatever property you like and commit again.

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

Transactions

A transaction is a sequence of operations (like INSERT, UPDATE, SELECT) made on your database. In order for a transaction to be completed, a number of operations within a group must be successful. If one operation fails, the whole transaction fails.

Transactions have the following 4 standard properties(known as ACID properties):

A transaction ends with COMMIT or ROLLBACK.

COMMIT: session.commit()

The COMMIT command is used to permanently save any transaction into the database.

When you use INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, the changes made by these commands can be undone or "rolled back".

If you use the COMMIT command, though, the changes to your database are permanent.

ROLLBACK

It restores the database to last your last COMMIT. You can also use it with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

Also, if you use UPDATE to make changes to your database, you can undo them by using the ROLLBACK command but only if you haven't committed those changes like this:

1db.session.rollback()

CHECKPOINT OR SAVEPOINT

This command is used to temporarily to save a transaction so that you can go back to a certain point by using the ROLLBACK command whenever needed, you can use like this:

1db.session.begin_nested()

This command may be called many times, and it will issue a new CHECKPOINT with an ID.

Now, let's say we go out to have some pizza. Our pizza comes with three ingredients basic ingredients: mozzarella, tomato, olives. Our table called 'PIZZA' would look like this:

But we have a list of extra ingredients we can add to it: first we choose meat, but then we change our mind, and we want to add mushrooms instead. We would also like to add some pepperoni and bacon. Let see, how could we do that:

1# we insert a new ingredient into out pizza
2ingredient = Ingredient()
3ingredient.name = 'meat'
4ingredient.id = 4
5db.session.add(ingredient)
6
7# now we COMMIT the transaction and save it into the database
8db.session.commit()
9
10ingredient = Ingredient.query.get(4)
11ingredient.name = mushrooms
12
13# save a checkpoint
14checkpoint_a = db.session.begin_nested()
15
16# add pepperoni
17ingredient = Ingredient()
18ingredient.name = 'pepperoni'
19db.session.add(ingredient)
20
21# one last checkpoint before adding bacon ingredient
22checkpoint_b = db.session.begin_nested()
23
24# add bacon
25ingredient = Ingredient()
26ingredient.name = 'bacon'
27db.session.add(ingredient)

Now our 'PIZZA' has the following ingredients:

Now we have decided we no longer want bacon, so we use ROLLBACK:

1checkpoint_b.rollback()

and our pizza looks like this:

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