Database Integration with Flask-SQLAlchemy

Relational databases are widely used in web applications. These databases organize data into tables with fixed columns and variable rows, where columns represent the attributes of entities being modeled.

Flask doesn't impose any restrictions on database selection, allowing developers to choose between SQL or NoSQL solutions. For more convenient database operations, SQLAlchemy—an Object-Relational Mapping (ORM) framework similar to Django's ORM—can be utilized.

Flask-SQLAlchemy Extension

SQLAlchemy serves as an abstraction layer over databases, enabling developers to interact with databases through Python objects rather than raw SQL statements. This approach trades some performance overhead for significantly improved development efficiency.

Flask-SQLAlchemy is a Flask extension that simplifies SQLAlchemy operations, providing both high-level ORM functionality and access to low-level database operations.

Installation

To begin using Flask-SQLAlchemy, install it via pip:

pip install flask-sqlalchemy

For MySQL connections, you'll also need to install Flask-MySQLdb:

pip install flask-mysqldb

Configuring Flask-SQLAlchemy

In Flask-SQLAlchemy, databases are specified using URIs, and the application's database must be configured in the Flask configuration object's SQLALCHEMY_DATABASE_URI key.

Basic Database Configuration

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost:3306/database_name'

Additional Configuration Options

# Enable or disable modification tracking
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

# Display original SQL statements during queries
app.config['SQLALCHEMY_ECHO'] = True

Configuration Key	Description
SQLALCHEMY_DATABASE_URI	Database URI for connection. Examples: sqlite:////tmp/test.db, mysql://user:pass@server/db
SQLALCHEMY_BINDS	Dictionary mapping binds to connection URIs for multiple databases
SQLALCHEMY_ECHO	If True, SQLAlchemy logs all statements to stderr (prints SQL)
SQLALCHEMY_RECORD_QUERIES	Enable or disable query recording, useful for debugging
SQLALCHEMY_POOL_SIZE	Size of database connection pool (default is 5)
SQLALCHEMY_POOL_TIMEOUT	Connection timeout for the pool (default is 10 seconds)
SQLALCHEMY_POOL_RECYCLE	Seconds after which connections are recycled (important for MySQL)

Connecting to Different Databases

Here are connection strings for common database systems:

PostgreSQL

postgresql://username:password@localhost/database_name

MySQL

mysql://username:password@localhost/database_name

Oracle

oracle://username:password@127.0.0.1:1521/service_name

SQLite

Note the four leading slashes for absolute paths:

sqlite:////absolute/path/to/database.db

Common SQLAlchemy Field Types

Type Name	Python Type	Description
Integer	int	Standard integer, typically 32-bit
SmallInteger	int	Small-range integer, typically 16-bit
BigInteger	int or long	Integer with unlimited precision
Float	float	Floating-point number
Numeric	decimal.Decimal	Fixed-point or floating-point number
String	str	Variable-length string
Text	str	Variable-length string optimized for longer content
Boolean	bool	Boolean value (True/False)
Date	datetime.date	Date value
DateTime	datetime.datetime	Date and time value
LargeBinary	bytes	Binary data

Common SQLAlchemy Column Options

Option	Description
primary_key	If True, designates the column as the table's primary key
unique	If True, ensures the column contains only unique values
index	If True, creates an index on the column for faster queries
nullable	If True, allows NULL values in the column
default	Specifies a default value for the column

Common SQLAlchemy Relationship Options

Option	Description
backref	Adds a reverse reference in the related model
primary join	Explicitly specifies the join condition between models
uselist	If False, uses scalar values instead of lists
order_by	Specifies sorting order for related records
secondary	Specifies the association table for many-to-many relationships

Basic Database Operations

In Flask-SQLAlchemy, insert, update, and delete operations are managed through database sessions. Data must be added to the session before committing changes to the database using the commit() method.

Query operations are performed through the query object, which allows filtering for more precise database searches.

Defining Models

Models can be defined in view functions with an explicit table name:

# Explicitly specify table name; if omitted, defaults to class name
__tablename__ = "explicit_table_name"

Example Application Setup

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Database connection settings
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user:password@localhost:3306/my_database'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Initialize SQLAlchemy and associate with the current app
db = SQLAlchemy(app)

# Define model classes
class Department(db.Model):
    __tablename__ = 'departments'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

class Employee(db.Model):
    __tablename__ = 'employees'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(64), default='')
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))

@app.route('/')
def index():
    return 'Hello World'

if __name__ == '__main__':
    # Drop all existing tables
    db.drop_all()
    # Create all tables
    db.create_all()
    app.run(debug=True)

Model Relationships

One-to-Many Relationships

In the "one" side, add a relationship with a back reference to the "many" side:

class Department(db.Model):
    # ...
    # Key relationship code
    employees = db.relationship('Employee', backref='department', lazy='dynamic')

Many-to-One Relationships

In the "many" side, add a foreign key to the "one" side:

class Employee(db.Model):
    # ...
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))

Many-to-Many Relationships

association_table = db.Table('employee_projects',
    db.Column('employee_id', db.Integer, db.ForeignKey('employees.id')),
    db.Column('project_id', db.Integer, db.ForeignKey('projects.id'))
)

class Project(db.Model):
    # ...
    pass

class Employee(db.Model):
    # ...
    projects = db.relationship('Project', secondary=association_table,
                              backref=db.backref('employees', lazy='dynamic'),
                              lazy='dynamic')

Common Query Filters

Filter	Description
filter()	Adds a filter to the query, returning a new query
filter_by()	Adds equality filters to the query, returning a new query
limit()	Limits the query results to a specified number of items
offset()	Offsets the query results, returning a new query
order_by()	Orders query results by specified criteria, returning a new query
group_by()	Groups query results by specified criteria, returning a new query

Common Query Execution Methods

Method	Description
all()	Returns all query results as a list
first()	Returns the first query result or None if not found
first_or_404()	Returns the first result or raises a 404 error if not found
get()	Returns the record with the specified primary key or None
get_or_404()	Returns the record with the specified primary key or raises a 404 error
count()	Returns the number of query results
paginate()	Returns a Pagination object with results in a specified range

Database Migration with Flask-Migrate

During development, database models often need modification, and the database schema must be updated accordingly. Rather than deleting tables (which would lose data), database migration frameworks can track schema changes and apply them to the database.

Flask-Migrate is an extension that integrates with Flask-Script to provide database migration commands.

Installation

pip install flask-migrate

Setting Up Migration

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

app = Flask(__name__)

# Database connection configuration
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://user:password@localhost:3306/my_database"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize SQLAlchemy and associate with the current app
db = SQLAlchemy(app)

# Initialize migration and associate with the app
migrate = Migrate(app, db)
manager = Manager(app)
# Add db command to manager
manager.add_command('db', MigrateCommand)

# Define models
class Department(db.Model):
    __tablename__ = "departments"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    employees = db.relationship('Employee', backref='department', lazy='dynamic')

    def __repr__(self):
        return f'<Department {self.name}>'

class Employee(db.Model):
    __tablename__ = "employees"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(64), default='')
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))

    def __repr__(self):
        return f'<Employee {self.name} {self.email} {self.department_id}>'

@app.route('/')
def index():
    return 'Hello World'

if __name__ == '__main__':
    manager.run()

Migration Commands

Create a migration repository:

python app.py db init

Create migration scripts:

python app.py db migrate -m 'initial migration'

Update the database:

python app.py db upgrade

View migration history:

python app.py db history

Downgrade to a specific version:

python app.py db downgrade <version_id>