Flask Guide - Part 4. Database

Categories:   web development  
Tags:   python   flask  

This is part 4 of the “Flask Guide”.

You can check the previous post here.

We’ll cover more in the next post.


These are the sources I used:

SQlite Database

This section will cover:

  • Database
  • Installing flask-sqlalchemy
  • Creating the User & Post models
  • Migrating the database
  • Playing with the database

Install flask-sqlalchemy

  • Let’s install flask-sqlalchemy:
source .venv/Scripts/activate
pip install flask-sqlalchemy
pip freeze > requirements.txt

Create the DB Models:

  • We will only update one file in this section.
  • The begin.py file should look like:
from datetime import datetime
from flask import Flask, render_template, url_for, flash, redirect
from flask_sqlalchemy import SQLAlchemy
from forms import RegistrationForm, LoginForm

app = Flask(__name__)
app.config['SECRET_KEY'] = '67738246378uienohtkxbtx65782'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)

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}')"

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)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

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

posts = [
        'author': 'John Doe',
        'title': 'Blog Post 1',
        'content': 'First post content',
        'date_posted': '2019, Nov 21'
        'author': 'Jane Doe',
        'title': 'Blog Post 2',
        'content': 'Second post content',
        'date_posted': '2019, Nov 22'

def home():
    return render_template('home.html', posts=posts)

@app.route("/register", methods=['GET', 'POST'])
def register():
    form = RegistrationForm()
    if form.validate_on_submit():
        flash(f'Account created for {form.username.data}', 'success')
        return redirect(url_for('home'))
    return render_template('register.html', title='Register', form=form)

@app.route("/login", methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        if form.email.data == 'admin@blog.com' and form.password.data == 'password':
            flash('you have been logged in', 'success')
            return redirect(url_for('home'))
            flash('Login unsuccessful, please check username and password', 'danger')
    return render_template('login.html', title='Login', form=form)

def about():
    return render_template('about.html', title='About')

if __name__ == '__main__':
  • We imported the flask_sqlalchemy.
  • We imported datetime.
  • We added a config to specify where the db file should be created.
  • We created two models for the database with a one-to-many relationship (Post & User.)
  • Some fields are unique & some will have default values assigned if empty.

Migrating the Database

We will create the db file using the python in the command line:

from begin import db
  • replace begin with the name of the python app.
  • This will create the database file in specified location.

To delete the db, run:

from begin import db

To exit python, run:


Playing with the Database

We can edit and query the database via the command line:

from begin import db
from begin import User, Post
user_1 = User(username='Jim', email='jim@blog.com', password='password')
user_2 = User(username='Jane', email='jane@blog.com', password='password')


user = User.query.get(1)

post_1 = Post(title='first post', content='the content', user_id=user.id)
post_2 = Post(title='second post', content='the 2nd content', user_id=user.id)




Here we can see that the user and post tables have a relationship.


  • We created sqlite database for quick testing.
  • We created a User & Post model.
  • We migrated the database.
  • We created some entries and played with the data.

I think we will end this section here.

We’ll cover more in the next post.

Related Products

Categories:   web development  
Tags:   python   flask