How to Build a CRUD app with Flask and SQLAlchemy

Overview

Here is what we will cover:

• Set up a Flask application
• Configure SQLAlchemy
• Define database models
• Set up routes and views for CRUD operations
  • Create
  • Read
  • Update
  • Delete
• Connect frontend to backend logic

By the end, you will have a fundamental understanding of building CRUD functionality with Flask and SQLAlchemy.

Prerequisites

Before starting, you should have:

• Basic understanding of Python web frameworks
• Basic understanding of SQL and databases
• Python 3 installed on your system
• Flask and SQLAlchemy installed

Here are the versions used for this tutorial:

• Python 3.7
• Flask 1.1.2
• SQLAlchemy 1.3.13

Set up a Flask Application

We will use the Flask framework to handle requests and rendering templates. Here are the steps to set up a basic Flask app:

1. Import Flask and create the app

from flask import Flask

app = Flask(__name__)

2. Define routes

We need to add routes that map URLs to Python functions. For now we will just define a simple home page route.

@app.route("/")
def home():
    return "Hello, World!"

3. Run development server

Now we can start our development server to test it out:

$ flask run

You should be able to open http://localhost:5000 and see our “Hello, World!” message.

Configure SQLAlchemy

With our basic Flask app set up, let’s configure SQLAlchemy for database access.

1. Import SQLAlchemy and create DB connection

First import sqlalchemy and initialize a SQLAlchemy instance:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(app)

This connects SQLAlchemy to our Flask application.

2. Choose database URI

We need to specify the database URI that tells SQLAlchemy where to connect. We will use a SQLite database for this tutorial.

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

3. Initialize database

Now that everything is configured, we can initialize our database:

$ flask db init

This will create a migrations folder that handles database schema changes.

Define Database Models

With the groundwork in place, let’s define our database models using the SQLAlchemy declarative style:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

This creates a User table with an primary key id, a unique username and email.

We can create any models we need this way to structure our database tables.

Set up CRUD Routes and Views

Now for the meat of our application – routes and views to handle CRUD operations:

Import Statements

We will need a few extra imports:

from app import db
from models import User

Accepts GET and POST requests.

View

if request.method == 'POST':

    username = request.form['username']
    email = request.form['email']

    new_user = User(username=username, email=email)

    db.session.add(new_user)
    db.session.commit()

    return redirect(url_for('list_users'))

return render_template('createuser.html')

This will get data from a form, create a new User, save to the database, and redirect to the user list view.

We will skip over the frontend templates for brevity.

READ

To fetch and read users:

Route

@app.route('/users')
def list_users():
  users = User.query.all()
  return render_template('listusers.html', users=users)

Query for all User objects and pass to template.

View

Again we’ll skip the template, but it would loop through the users and display details.

We would also create routes, views, and templates to handle:

• View single user details
• Edit user (update)
• Delete user

Using similar patterns for interacting with the database.

UPDATE

To edit/update a existing user with new info:

Route

@app.route('/users/edit/<int:id>', methods=['GET', 'POST'])  
def update_user(id):

Accepts GET and POST requests to same route, passing user id parameter.

View

user = User.query.get(id)

if request.method == 'POST':
    user.username = request.form['username']
    user.email = request.form['email']
        
    db.session.commit()

    return redirect(url_for('list_users'))
        
return render_template('updateuser.html', user=user)

Gets existing user from id, updates fields from form data, saves to database and redirects.

Also passes user object to pre-fill the form being updated.

DELETE

Deleting a user:

Route

@app.route('/users/delete/<int:id>')
def delete_user(id):

Takes in user id to target for deletion.

View

user = User.query.get(id)
db.session.delete(user)
db.session.commit()

return redirect(url_for('list_users'))

Fetches user, deletes from database, redirects back to user list view.

This covers the basics of performing CRUD using Flask and SQLAlchemy!

The last piece is connecting these backend routes, models, and databases to an actual frontend and templates for users to interact with.

Connect Frontend to Backend Logic

To build out a full CRUD application, you need:

• Templates – HTML files that display data and accept input
• Forms – WTForms that take in user input and data
• Routes – To handle requests and connect to DB logic
• Models – Database tables defined via SQLAlchemy

Here is a brief overview of how the frontend connects to the backend:

• User visits app in browser and clicks link to Create User
• Flask renders create user template with WTForm
• User fills out form fields and clicks Submit
• Request sent to create_user route
• Data passed to User model and added to DB
• Route redirects back to user list template
• Flask renders read template with all User data from DB

The same process flows through Update and Delete operations as well.

This is a simplified look at tying the whole CRUD app together.

There are many little details in developing full applications, but hopefully this gives you a primer on building core CRUD functionality with Flask and SQLAlchemy.

Summary

To recap building a CRUD app with Flask and SQLAlchemy:

• Set up Flask app and configure SQLAlchemy
• Define models and database tables
• Create routes and views for CRUD operations
  • Handle requests and call database
  • Interact with models to query or modify DB
• Connect backend to frontend templates and forms

Following these steps allows you to develop a simple yet complete CRUD application for managing database records.

There is a vast amount more you can do in expanding on this foundation with other Flask and SQLAlchemy features. But understanding the basics of tying these two tools together provides a hugely useful skillset for web development in Python.

Happy CRUDing with Flask!