Flask and Databases - SQLite and SQLAlchemy

Introduction

Flask, SQLite, and SQLAlchemy form a powerful combination for working with databases in web applications. In this guide, we'll explore how to use SQLite as the database engine, and SQLAlchemy as the Object-Relational Mapping (ORM) library within your Flask application. You'll learn how to set up a database, create models, and perform database operations.

Step 1: Setting Up Your Flask Application

Before working with databases, make sure you have a Flask application. If not, you can create a basic Flask app like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///your_database.db'
db = SQLAlchemy(app)
        

Ensure you have Flask-SQLAlchemy installed, and you can specify the database URI for your SQLite database.

Step 2: Creating a Model

To define a model, you can create a Python class representing a table in your database. Here's an example of a simple model:

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 __init__(self, username, email):
        self.username = username
        self.email = email
        

In this example, we define a `User` model with three fields: `id,` `username,` and `email.` The model class inherits from db.Model, and the fields are defined as class attributes.

Step 3: Creating Database Tables

To create the database tables based on your models, run the following commands in your Python script:

# Create the database tables
db.create_all()
        

This command initializes the database schema based on your defined models.

Step 4: Performing Database Operations

With your models and tables in place, you can interact with the database by using SQLAlchemy methods. Here's an example of how to add a new user to the database:

# Create a new user
new_user = User(username='john_doe', email='john@example.com')
# Add the user to the database
db.session.add(new_user)
db.session.commit()
        

This code creates a new user object, adds it to the session, and commits the changes to the database.

Conclusion

Flask, SQLite, and SQLAlchemy provide a powerful stack for managing databases in your Flask applications. By defining models, creating database tables, and using SQLAlchemy to interact with the database, you can build data-driven web applications with ease.