Flask for Beginners: Building Robust Databases with SQLAlchemy

Databases are essential to the development of online applications. The user transmits information that must be stored for later use throughout the life of a web application. The user simultaneously requests data from the location where they are kept.

Introduction to Databases and SQLAlchemy

A database is a collection of organized data that can be easily accessed, managed and updated. Relational databases are the most common type of database used in web applications today. These databases organize data into tables, which consist of rows and columns.

SQLAlchemy is a popular Python library that provides an Object-Relational Mapping (ORM) layer, allowing you to interact with relational databases using Python objects instead of writing SQL queries directly. This makes it easier to work with databases in your Python applications, and can help to reduce the amount of code needed to manage database connections and transactions.

Setting up a Database in Flask

To use SQLAlchemy in your Flask application, you'll need to install the Flask-SQLAlchemy extension. Once you've installed the extension, you can create a database connection by creating an instance of the SQLAlchemy class:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

In this example, we're creating a new instance of the SQLAlchemy class and assigning it to the db variable.

To configure the database connection, you'll need to set the SQLALCHEMY_DATABASE_URI configuration variable to the URL of your database. Here's an example of how to configure the database connection to use a SQLite database:

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

In this example, we're setting the SQLALCHEMY_DATABASE_URI configuration variable to the URL of a SQLite database named example.db.

Once you've configured the database connection, you can initialize the extension by calling the init_app() method on your Flask application instance:

from flask import Flask

app = Flask(__name__)
db.init_app(app)

Creating Database Models using SQLAlchemy

To define your database schema using SQLAlchemy, you'll need to create a model class for each table in your database. Here's an example of a User model class:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    # Like username add email and password_hash
    email = # Add here
    password_hash = # Add here

In this example, we're defining a User model class that inherits from db.Model. We're also defining three columns in the User table: id, username, email, and password_hash.

To create the table in the database, you can call the create_all() method on your db instance:

db.create_all()

Creating, Reading, Updating, and Deleting Data using Flask and SQLAlchemy

Once you've defined your database models, you can use Flask and SQLAlchemy to perform CRUD (Create, Read, Update, Delete) operations on your data.

Creating Data

To create a new record in the database, you can create a new instance of your model class and add it to the database session:

new_user = User(
username='john',
email='john@example.com',
password_hash='password'
)
db.session.add(new_user)
db.session.commit()

In this example, we're creating a new User object and adding it to the database session. Once we're done adding objects, we call db.session.commit() to save the changes to the database.

Reading Data

To retrieve data from the database, you can use SQLAlchemy's query API. Here's an example of retrieving all User objects from the database:

users = User.query.all()

In this example, we're using the query method to retrieve all the User objects from the database.

You can also use the query API to filter the results based on specific criteria. Here's an example of retrieving all User objects where the username is equal to 'john':

user = User.query.filter_by(username='john').first()

In this example, we're using the filter_by() method to filter the results based on the username column, and then calling the first() method to retrieve the first result.

Updating Data

To update an existing record in the database, you can retrieve the record, update its attributes, and then commit the changes to the database session:

user = User.query.filter_by(username='john').first()
user.email = 'new_email@example.com'
db.session.commit()

In this example, we're retrieving the User object with the username equal to 'john', updating its email attribute, and then committing the changes to the database.

Deleting Data

To delete a record from the database, you can retrieve the record and then call the delete() method on the record:

user = User.query.filter_by(username='john').first()
db.session.delete(user)
db.session.commit()

In this example, we're retrieving the User object with the username equal to 'john', deleting it from the database, and then committing the changes to the database.

Using Flask-Migrate for Database Schema Migrations

Flask-Migrate is a Flask extension that provides database migration support. Database migrations allow you to modify the structure of your database schema over time, while preserving existing data.

To use Flask-Migrate, you'll need to install the Flask-Migrate package and initialize it with your Flask app and SQLAlchemy database:

from flask_migrate import Migrate

migrate = Migrate(app, db)

Once you've initialized Flask-Migrate, you can create a migration script by running the flask db migrate command:

flask db migrate -m "Create users table"

This will generate a migration script that you can review and apply to your database by running the flask db upgrade command:

flask db upgrade

In this article, we've covered the basics of databases and SQLAlchemy, how to set up a database in Flask, how to create database models using SQLAlchemy, how to create, read, update, and delete data using Flask and SQLAlchemy, how to use Flask-Migrate for database schema migrations.

If you need a files used in this project or need assistance for your Flask project feel free to drop a mail to contact@codersarts.com