G2Labs Grzegorz Grzęda

Databases in Flask

February 24, 2024

Database integration is a crucial aspect of many web applications, and Flask provides the flexibility to integrate with a variety of database systems, both SQL and NoSQL. Let’s discuss how to integrate a relational database into a Flask application using the Flask-SQLAlchemy extension, which provides an ORM (Object-Relational Mapping) layer to work with databases in a more Pythonic way.

Flask-SQLAlchemy

Flask-SQLAlchemy is an extension for Flask that simplifies the use of SQLAlchemy, a powerful ORM for Python, with Flask applications. It allows you to define your database models using Python classes and perform database operations without writing raw SQL.

Key Features:

ORM: Translates Python classes to database tables and automatically converts function calls to SQL statements.
Database Agnostic: Works with various types of databases, like SQLite, MySQL, PostgreSQL.
Migrations: Integrates with Flask-Migrate to handle database migrations.

Setting Up Flask-SQLAlchemy

Install Flask-SQLAlchemy:
1
pip install Flask-SQLAlchemy
Configure Database URI: In your Flask app’s configuration, set the SQLALCHEMY_DATABASE_URI to specify the database to use.

Initialize SQLAlchemy:

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///yourdatabase.db'
db = SQLAlchemy(app)

Defining Models

Models in Flask-SQLAlchemy are defined as Python classes. Each class corresponds to a table in the database.

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

Here, User is a model representing a user in the database with id, username, and email fields.

Performing Database Operations

With Flask-SQLAlchemy, you can perform database operations using Python methods rather than writing SQL queries.

Create a Record:

1
2
3

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

Read Records:

1
2

users = User.query.all()
john = User.query.filter_by(username='john_doe').first()

Update a Record:

1
2
3

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

Delete a Record:

1
2
3

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

Database Migrations

For handling changes in the database schema, Flask-Migrate (an extension that adds SQLAlchemy database migrations to Flask applications) can be used.

Install Flask-Migrate:
1
pip install Flask-Migrate

Initialize Migrate:

1
2
3

from flask_migrate import Migrate

migrate = Migrate(app, db)

Migration Commands:
- flask db init to initialize the migration directory.
- flask db migrate to create a new migration.
- flask db upgrade to apply the migration to the database.

Conclusion

Integrating a database with Flask using SQLAlchemy provides a powerful, flexible, and Pythonic way of working with data. This approach significantly simplifies database operations and schema management in Flask applications, making it an ideal choice for both small and large-scale web applications.