In order to go through the exercises in this chapter, you will need a running database, if you do not already have one. If you have never installed a database, or you do not have a preference, then SQLite is the best option for beginners, or if you want to quickly bootstrap a proof of concept.

SQLite is an SQL-embedded database engine that is fast, works without a server, and is entirely contained in one file. SQLite is also natively supported in Python, so if you choose to go with SQLite, an SQLite database will be automatically created for you during the exercise in the Our first model section.

Flask SQLAlchemy can be used with multiple database engines, such as ORACLE, MSSQL, MySQL...

You may have noticed that we did not actually create any tables in our database to abstract from. This is because SQLAlchemy allows us to create either models from tables or tables from our models. We will look at this after we have created the first model.

In our main.py file, SQLAlchemy must first be initialized with our app as follows:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import DevConfig

app = Flask(__name__)
app.config.from_object(DevConfig)
db = SQLAlchemy(app) 

SQLAlchemy will read our app's configuration and automatically connect to our database. Let's create a User model to interact with a user table in the main.py file, as follows:

class User(db.Model): 
  id = db.Column(db.Integer(), primary_key=True) 
  username = db.Column(db.String(255)) 
  password = db.Column(db.String(255)) 
 
  def __init__(self, username...

In every storage mechanism for data, there are four basic types of functions: create, read, update, and delete (CRUD). These allow us to perform all the basic ways of manipulating and viewing the data that is needed for our web apps. To use these functions, we will use an object in the database named a session. Sessions will be explained later in the chapter, but for now, think of them as a storage location for all of our changes to the database.

To create a new row in your database using our models, add the model to the session and commit objects. Adding an object to the session marks its changes for saving. Committing is when the session is saved to the database, as follows:

    >>> user = User...

Relationships between models in SQLAlchemy are links between two or more models that allow models to reference each other automatically. This allows naturally related data, such as comments on posts, to be easily retrieved from the database with its related data. This is where the R in RDBMS comes from, and it gives this type of database a large amount of power.

Let's create our first relation. Our blogging website is going to need some blog posts. Each blog post is going to be written by one user, so it makes sense to link posts back to the user who wrote them so that we can easily get all the posts by a user. This is an example of a one-to-many relationship, as shown in the following code:

Using constraints is considered a good practice. This way, you can restrict the domain of a certain model attribute and ensure data integrity and quality. There are many types of constraints that you can use; primary key and foreign key constraints were already covered in the previous sections. The other kinds of constraints that are supported by SQLAlchemy are shown in the following list:

• Not NULL (ensures that a certain attribute contains data)
• UNIQUE (ensures that a certain attribute value is always unique in the database table, which contains the model data)
• DEFAULT (sets a default value for the attribute when no values were provided)
• CHECK (used to specify range of values)

Using SQLAlchemy, you can ensure that your data's domain restrictions are explicit and all in the same place, not spread across your application code.

Let's improve...

Now you understand the power of SQLAlchemy and what the SQLAlchemy session object is, and why web apps should never be made without them. As stated before, the session can be simply described as an object that tracks the changes in our models and commits them to the database when we tell it to. However, there is a bit more to it than this.

First, the session is also the handler for transactions. Transactions are sets of changes that are flushed to the database on commit. Transactions provide a lot of hidden functionality. For example, transactions automatically determine which objects are to be saved first when objects have relations. You might have noted this when we were saving tags in the previous section. When we added tags to the posts, the session automatically knew to save the tags first despite the fact that we did not add them to...

The functionality of web apps changes all the time, and with every new functionality, we need to change the structure of our database. Whether it's adding or dropping new columns or creating new tables, our models will change throughout the life cycle of our app. However, problems quickly arise when the database changes often. When moving our changes from development to production, how can you be sure that you carried over every change without manually comparing each model and its corresponding table? Let's say that you want to go back into your Git history to see whether an earlier version of your app had the same bug that you are now encountering in production. How will you change your database back to the correct schema without a lot of extra work?

As programmers, we hate extra work. Thankfully, there is a tool called Alembic, which...

Now that we have mastered data control, we may move on to displaying our data in our application. The next chapter, Chapter 3, Creating Views with Templates, will dynamically cover creating HTML based on our models and adding models from our web interface.