Flask SQLAlchemy Basics
Building a minimal Flask app that keeps track of your book collection.
Introduction
In this article, we’re going to learn the basics of SQLAlchemy by creating a data-driven web application using Flask, a Python framework. We’ll build a minimal Flask app that keeps track of your book collection.
At its most simple, the app will allow users to create new books, read all the existing books, update the books, and delete them. These operations — create, read, update, and delete — are more commonly known as “CRUD” and form the basis of nearly all web applications. we’ll learn more about CRUD operations later in the article.
But before we start playing with CRUD, let’s understand other parts of the application, beginning with SQLAlchemy.
What is SQLAlchemy ?
Note that we have a Flask extension flask-sqlalchemy, which simplifies the use of SQLAlchemy by providing useful defaults and extra helpers, making it easier to accomplish common tasks. For this article, we’ll only be using plain SQLAlchemy — just so we have a basic understanding of SQLAlchemy before adding any extensions.
According to their website, “SQLAlchemy is the Python SQL toolkit and the Object Relational Mapper that gives application developers the full power and flexibility of SQL”.
After reading the definition above, the first question that pops up is what an Object Relational Mapper is. Object Relational Mapper, also known as ORM, is a technique used to write database queries using the object-oriented paradigm of your preferred language (in this case, Python).
In even simpler terms, an ORM can be thought of as a translator that translates code from one set of abstractions to another. In our case — from Python to SQL.
There are a lot of different reasons to use the ORM besides not having to craft SQL strings. Some of which are:
- Speeding up web development since we don’t have to switch back and forth between writing Python and SQL
- Eliminating repetitive code
- Streamlining the workflow and queries the data more efficiently
- Abstracting away the database system so switching between different databases becomes smooth
- Generating boilerplate code for the basic CRUD operations
Let’s dig a little deeper.
Why do we use the ORM when we can write database queries using raw SQL? When we write queries using raw SQL, we pass them to our database as strings. The following query is written in raw SQL:
Now, there’s absolutely nothing wrong with using raw SQL to talk to databases unless we make a mistake in the query, such as a typo or connecting to a database that doesn’t exist, or trying to access a non-existent table. The Python compiler wouldn’t be able to help us.
SQLAlchemy is one of the many Python object-relational mapper implementations out there. If we’re working on a small-scale application, using raw SQL might work — but if we’re working on a large-scale data-driven website, using raw SQL can end up being complex and error-prone.
To work around this issue, we can write our queries as objects using an ORM instead of writing them as strings. ORM converts our code written in Python (or any other language) to SQL statements. It’s that simple!
Enough with the theory. Let’s get into the good stuff and start writing some code!
Creating a database with SQLAlchemy
Let’s create a file that will set up and configure our database. We can name this file anything, but for this article, let’s name it database_setup.py.
At the top of the file, we’ll import all the necessary modules to configure and create our database. As you’ll see, we imported Column, ForeignKey, Integer, and String to define our database table columns.
Next, we import the declarative base. Base = declarative_base() constructs a base class for the declarative class definition and assigns it to the Base variable.
As the documentation describes, declarative_base() returns a new base class from which all mapped classes should inherit. It expresses Table, mapper(), and class objects all at once within the class declaration.
Next, we create an instance of our create engine class which points to the database by adding engine = create_engine('sqlite:///books-collection.db'). We can name our database anything, but here we named it books-collection.
The last step in our configuration is to add Base.metadata.create_all(engine).It will add the classes (we’ll write them in a bit) as new tables in the database we just created.
After configuring our database, we’ll create classes. In SQLAlchemy, classes are the object-oriented–or declarative–representation of tables in our database.
For this tutorial, we only need to create one table: Book. Our Book table has four columns: id, title, author, and genre. Integer and String are used to define the type of the values stored in a column: the column title, author and genre are all strings, while column id is of an integer type.
There are many class attributes that we can use to define our columns, but let’s take a look at some of the class attributes we’ve used here.
primary_key: When set to true, it indicates a value that can be used to identify each row of our table uniquely.String(250): While string defines the type of value, the enclosing number represents the maximum number of string.Integer: Integer establishes the type of the value.nullable: When set to false, indicates that it must have a value for the row to be created.
With that, we’re all set with the setting up and configuring of our database. If we run python database_setup.py in our terminal, an empty database called books-collection.db will be created. Now that we have our empty database ready, let’s populate the database and try talking to it.
CRUD with SQLAlchemy by example
Remember how we briefly touched on CRUD operations in the beginning? Let’s use them now.
We’ll create another file and name it populate.py (or any other name you’d like to give).
First, let’s import some dependencies and some classes from our database_setup.py file.
Then we let our program know which database engine we want to communicate with. We do this by using the create_engine function.
To make the connections between our class definitions and our tables in the database, we use the Base.metadata.bind command.
In order to create, delete, read or update entries in our database, SQLAlchemy provides an interface called Session. To execute our queries, we need to add and commit our entry. It also provides us with a method called flush(). Flush pushes our changes from memory to our database transaction buffer without committing the change.
CREATE:
The general procedure for creating an entry is:
We can create our first book by executing the following command:
READ:
Depending on what we want to read, we can use different functions. Let’s look at the two ways we’ll potentially use them in our app.
session.query(Book).all() – this will return a list of all the bookssession.query(Book).first() – this will return the first result or ‘None’ if the result doesn’t contain a row.
UPDATE:
To update entries in our database, we need to do the following:
- Find the entry
- Reset the values
- Add the new entry
- Commit the session to our database
If you hadn’t noticed yet, there is an error in our bookOne entry. The Bell Jar was written by Sylvia Plath and not some ‘Sylvia Pla’. Let’s update the author name using the four steps we just saw.
To find the entry, we can use the filter_by() that let us filter queries based on attribute entries. The following query will give us our book with id=1 (i.e. The Bell Jar)
To reset and commit the author name, I can execute the following commands:
We can use all(), one() or first() to find an entry depending on the result we’re expecting. There are, however, a few gotchas that we need to be careful about.
all()– returns the results represented by the query as a listone()– returns exactly one result or raises an exception. It raises ansqlalchemy.orm.exc.NoResultFoundexception if no result is found orsqlalchemy.orm.exc.NoResultFoundexception if multiple results are returnedfirst()– returns the first result of the query or ‘None’ if the result doesn’t contain any row but a non-exception is raised
DELETE:
Deleting values from our database is almost the same as updating the values. Instead of updating, we delete the values. Let’s take a look:
- Find the entry
- Delete the entry
- Commit the session
Now that we have our database set up and we know how to use CRUD operations, let’s write a minimal Flask application. This article won’t go deep into Flask, but if you need to know more about Flask, checkout this https://www.tutorialspoint.com/flask
Let’s create a new file app.py in the same directory as database_setup.py and populate.py. We’ll then import some of the necessary dependencies.
Lastly, we need to create templates i.e. books.html, newBook.html, editBook.html, and deleteBook.html. To do that, we’ll create a template folder at the same level as our app.py file. Within that folder, we’ll create these four files.
Then, we’ll create newBook.html.
Next is editBook.html.
Then deleteBook.html
If we execute python app.py command, and direct your browser to http://localhost:5000/books, you should see a list of books. If everything is working, you should see something like this on your screen:
Extending the App & Conclusion
If you’ve made it this far, then hopefully you’ll have learned a thing or two about how SQLAlchemy works! SQLAlchemy is a huge topic and we only covered the basics, so if you want to learn more, try making another CRUD app or enhancing this application by adding new features. If you want to continue working on this application, you can try adding Shelf table in the database to keep track of your reading progress, or if you want to go a step further, try using Flask-Login to add authentication and authorization feature to your application. Adding authentication and authorization can make your application more scalable. Instead of everybody applying CRUD operations on your book app, they can customize it and update just their books.
You can check out the code for the above app in the following Github Repository
Thank you for reading this article ! More articles to be released soon..
