← Back to Lessons
Edit on Github

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 an user with SQL you have to type:

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

With an ORM your code keeps being familiar like this:

user = User() user.name = 'Bob' user.last_name = 'Ross' db.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

class Person(Base): __tablename__ = 'person' # Here we define columns for the table person # Notice that each column is also a normal Python instance attribute. id = Column(Integer, primary_key=True) name = Column(String(250), nullable=False) def serialize(self): return { "id": self.id, "name": self.name }

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.

person = Person(username=<username_value>, email=<email_value>) db.session.add(person) db.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, ...)

# here is how to fetch all people all_people = Person.query.all() all_people = list(map(lambda x: x.serialize(), all_people)) # here is how to fetch a group of people with name = alex all_people = Person.query.filter_by(name='alex') all_people = list(map(lambda x: x.serialize(), all_people)) # here is how to fetch the person with id=3 (only works with primary keys) person = Person.query.get(3)

DELETE: Removing a Database Record.

All you have to do is create a new Person object, add it into the database session and commit!

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

UDPATE: 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.

person = Person.query.get(3) person.name = "Bob" db.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):

Transactions

A transaction ends with COMMIT or ROLLBACK.

COMMIT: session.commit()

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 a 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 commited those changes like this:

db.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:

db.session.begin_nested()

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

SQL

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:

SQL

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:

# we insert a new ingredient into out pizza ingredient = Ingredient() ingredient.name = 'meat' ingredient.id = 4 db.session.add(ingredient) # now we COMMIT the transaction and save it into the database db.session.commit() ingredient = Ingredient.query.get(4) ingredient.name = mushrooms # save a checkpoint checkpoint_a = db.session.begin_nested() # add pepperoni ingredient = Ingredient() ingredient.name = 'pepperoni' db.session.add(ingredient) # one last checkpoint before adding bacon ingredient checkpoint_b = db.session.begin_nested() # add bacon ingredient = Ingredient() ingredient.name = 'bacon' db.session.add(ingredient)

Now our 'PIZZA' has the following ingredients:

SQL

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

checkpoint_b.rollback()

and our pizza looks like this:

SQL

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


Subscribe for more!


COMPANY

ABOUT

CONTACT

MEDIA KIT

SOCIAL & LIVE LEARNING

The most efficient way to learn: Join a cohort with classmates like yourself, live streamings, coding jam sessions, live mentorships with real experts and keep the motivation.

INTRO TO CODING

From zero to getting paid as a developer, learn the skills of the present and future. Boost your professional career and get hired by a tech company.

DATA SCIENCE

Start a career in data science and analytics. A hands-on approach with interactive exercises, chat support, and access to mentorships.

30DAYSOFGEEKCODING

Keep your motivation with this 30 day challenge. Join hundreds of other developers coding a little every day.

A.I. & MACHINE LEARNING

Start with Python and Data Science, Machine Learning, Deep Learning and maintaining a production environment in A.I.


©4Geeks Academy LLC 2019

Privacy policies


Cookies policies


Terms & Conditions