Working with Databases in Flask

SQLAlchemy is a very useful ORM that helps people work with databases. An ORM is short for OBJECT RELATIONAL MAPPER and it basically allows you to access your database in an easy to use object oriented way. It helps you use different databases without actually changing your python code. We will use SQLITE for development and later switch to POSTGRESQL for deployment.

from flask_sqlalchemy import SQLAlchemy

After importing the database, you need to specify the URI for the database which is basically where the database is located. We will use an SQlite database, which you can do by simply making it a file in our filesystem. So to set the location, you need to set the database up as a configuration. see below

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

This will basically create a database called site.db in your project directory. After setting the location, you then need to create an instance of the database. see below

db = SQLALCHEMY(app)

In SQLAlchemy you can represent your database structure as classes. These classes are often called models. You can put these classes into a different file and import them into your main application.

Each class is going to be its own table in the database. First you need to create the user class to hold your users. And this class will import from db.Model. Inside the class you can start creating your columns. Remember above that each class represents a table in the database. See below:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)

The id is a column, and the first argument is specifying the data type of the Id which is an integer, and the primary_key means it is a unique id for our user so we set it to true.

The username is also a column, which takes the data type of string(20 chars maximum), and it’s also unique to the user, and not nullable; which means it cannot be left empty

The image_file represents the profile image of the user, its not nullable because at least the user will have to have a profile picture of the default one we give them. The default argument is specifying the name of the default image the user is going to start with.

Same idea for the password.

After that is done, you need to specify an __repr__ method. This method refers to how our object will be printed when it’s printed out

def __repr__(self):
    return f"User('{self.username}', '{self.email}', '{self.image_file}')"

Since this notes were made relative to a blog application, we need to create a database for the posts as well.

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False )
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) #import datime
    content = db.Column(db.Text, nullable=False)

    def __repr__(self):
        return f"User('{self.title}', '{self.date_posted}')"

Note that in the above we didnt add the Author to the Post model. The reason is that the Post model and User model are going to have a relationship since users will author posts. This is called a one-to-many relationship since one user can write many posts but a post can only have one author.

In SQLalchemy, you can do that by creating a “posts” attribute in the User model and set it to “db.Relationship” the first argument will be the model it has the relationship with. i.e ‘Post’ and give it a backref. The backref is similar to adding another column to the Post model. When we have a post, we can simply use the author attribute to get the user who created the post, thats the use of the backref. Another argument is the “lazy” argument which defines when SQLalchemy loads the data from the database. Setting it to true means that SQLalchemy will load the data in one go. It is convenient because in this relationship we can use the “post” attribute to get all the posts created by a user. See below

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    posts = db.Relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.image_file}')"

Now in order to specify the user in the “Post” model, we can add the user id for the author. You can create an attribute called “user_id” in the “Post” model and set it equal to a db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

The ForeignKey argument simply means the user_id has a relationship to our “User” model. Note that even though the “User” model starts with a capital letter we actually call it with a lowercase inside our ForeignKey argument. This is because inside the ForeignKey we are not calling the “User” mode but the name of the table. Which are always stored in lowercase.

Now that we have the database models set up, which represent the structure of our database. We can then go ahead to create our database.

We use the command line to do that.

First, open python in your command line. Make sure you are in your project directory.

Now, type from “application_name” import “app”, “database_instance” The app is simply the instance of your flask app. The application name is the name of your app or main file, and the database_instance is the name of the variable which containse the database instance. See below

from shadys-blog import app, db

Also, make sure you create the database within the app context. Dont worry about what it means. Just type the below:

app.app_context().push()

Thirdly, type “db.create_all()” to create the database.

Now our database has been created. Since it is an sqlite database it actually going to create a file in our directory.

Now we can add data to our database using the command line. Open the command line and import the models

from shadys-blog import User, Post
user_1 = User(username='Ernest', email='user@example.com', password='password')

This will create an instance of the User model and pass it the necessary attributes.

Now lets tell our database that we want to add this user. To add this user we use the following command

db.session.add(user_1)

That doesnt necessarily add the user to the database, but what it does is that it tells the database that we have made some changes to it. We can make multiple changes at once, but in order to add the changes to our database we have to commit those changes.

We commit the changes by using:

db.session.commit()

If you want to get all the users in the database you can do so by typing:

User.query.all()
User.query.first() #returns the first user

Assuming you want to filter by username, you can use

User.query.filter_by(username='Ernest').all()

To delete all data in the database, use the opposite of db.create_all(). See below:

db.drop_all()