Skip to main content

Defining Models with SQLAlchemy in Flask

Defining Models with SQLAlchemy in Flask

Defining models with SQLAlchemy in Flask is a cornerstone of building data-driven web applications, such as dashboards, machine learning (ML) platforms, or user management systems. SQLAlchemy, integrated with Flask via Flask-SQLAlchemy, provides an Object-Relational Mapping (ORM) layer that maps Python classes to database tables, enabling seamless interaction with relational databases like SQLite, PostgreSQL, or MySQL. This guide covers defining models with SQLAlchemy in Flask, including model creation, relationships, constraints, and practical examples tailored to data-driven applications.

01. Overview of SQLAlchemy Models

SQLAlchemy models define the structure of database tables as Python classes, where attributes represent columns and relationships link tables. Flask-SQLAlchemy simplifies integration with Flask, providing tools to define, query, and manage models efficiently.

  • Purpose: Map database tables to Python objects for easy data manipulation.
  • Key Components: db.Model, columns, relationships, constraints.
  • Use Cases: Storing user data, ML model metadata, or analytics logs.

1.1 Key Concepts

  • Model: A Python class that represents a database table.
  • Column: An attribute defining a table column with a data type and constraints.
  • Relationship: A link between models (e.g., one-to-many, many-to-many).
  • Constraints: Rules like primary keys, foreign keys, or unique constraints.

1.2 Security and Best Practices

  • Prevent SQL Injection: Use ORM queries instead of raw SQL.
  • Validate Data: Enforce constraints and validate input before saving.
  • Secure Sensitive Data: Avoid storing sensitive data in plaintext.

02. Setting Up Flask-SQLAlchemy

2.1 Installation

Install Flask-SQLAlchemy and a database driver (e.g., psycopg2 for PostgreSQL or sqlite3 for SQLite, which is built into Python): 

pip install flask-sqlalchemy psycopg2-binary

2.2 Project Structure

project/
├── app.py
├── static/
│   ├── css/
│   │   └── style.css
├── templates/
│   ├── base.html
│   ├── users.html
│   └── add_user.html
└── instance/
    └── database.db  # SQLite database

2.3 Basic Configuration

Configure Flask-SQLAlchemy with a database URI and initialize the database.

Example: Basic Model Definition with SQLite

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired, Email
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///instance/database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Define User model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

# Form for adding users
class UserForm(FlaskForm):
    name = StringField('Name', validators=[DataRequired()])
    email = StringField('Email', validators=[DataRequired(), Email()])
    submit = SubmitField('Add User')

# Create database tables
with app.app_context():
    db.create_all()

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

@app.route('/add', methods=['GET', 'POST'])
def add_user():
    form = UserForm()
    if form.validate_on_submit():
        user = User(name=form.name.data, email=form.email.data)
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_user.html', form=form)

if __name__ == '__main__':
    app.run(debug=True)

File: templates/base.html

<!DOCTYPE html>
<html>
<head>
    <title>{% block title %}Flask SQLAlchemy Models{% endblock %}</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}">
</head>
<body>
    <div class="container mt-3">
        {% block content %}{% endblock %}
    </div>
</body>
</html>

File: static/css/style.css

body {
    font-family: Arial, sans-serif;
}
.form-group {
    margin-bottom: 15px;
}
.error {
    color: red;
    font-size: 0.9em;
}

File: templates/users.html

{% extends 'base.html' %}

{% block title %}Users{% endblock %}

{% block content %}
    <h1>Users</h1>
    <a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">Add User</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Created At</th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
                <tr>
                    <td>{{ user.id }}</td>
                    <td>{{ user.name | escape }}</td>
                    <td>{{ user.email | escape }}</td>
                    <td>{{ user.created_at | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

File: templates/add_user.html

{% extends 'base.html' %}

{% block title %}Add User{% endblock %}

{% block content %}
    <h1>Add User</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.name.label }}
            {{ form.name(class="form-control") }}
            {% for error in form.name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.email.label }}
            {{ form.email(class="form-control") }}
            {% for error in form.email.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Output (/):

  • GET /: Displays a table of users with their ID, name, email, and creation date.
  • GET /add: Shows a form to add a user.
  • POST /add (valid data): Adds a user to the database and redirects to the user list.
  • POST /add (invalid email): Shows validation errors.

Explanation:

  • User model: Defines a table with id, name, email, and created_at columns.
  • Constraints: primary_key=True, unique=True, nullable=False.
  • db.create_all(): Creates the table based on the model.
  • FlaskForm: Validates user input and provides CSRF protection.

2.4 Common Column Types

SQLAlchemy supports various column types:

  • db.Integer: Integer values.
  • db.String(length): Fixed-length strings.
  • db.Text: Variable-length text.
  • db.Float: Floating-point numbers.
  • db.DateTime: Date and time values.
  • db.Boolean: True/False values.

2.5 Column Constraints

  • primary_key=True: Marks a column as the primary key.
  • nullable=False: Requires a non-null value.
  • unique=True: Ensures unique values.
  • default=value: Sets a default value.

03. Defining Relationships

SQLAlchemy supports relationships like one-to-many, many-to-one, and many-to-many to model complex data structures.

Example: One-to-Many Relationship

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField, IntegerField
from wtforms.validators import DataRequired, Email
import os
from datetime import datetime

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///instance/database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Define User model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    datasets = db.relationship('Dataset', backref='user', lazy=True)

# Define Dataset model
class Dataset(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    size = db.Column(db.Integer, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

# Forms
class UserForm(FlaskForm):
    name = StringField('Name', validators=[DataRequired()])
    email = StringField('Email', validators=[DataRequired(), Email()])
    submit = SubmitField('Add User')

class DatasetForm(FlaskForm):
    name = StringField('Dataset Name', validators=[DataRequired()])
    size = IntegerField('Size (MB)', validators=[DataRequired()])
    user_id = IntegerField('User ID', validators=[DataRequired()])
    submit = SubmitField('Add Dataset')

# Create database tables
with app.app_context():
    db.create_all()

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

@app.route('/add_user', methods=['GET', 'POST'])
def add_user():
    form = UserForm()
    if form.validate_on_submit():
        user = User(name=form.name.data, email=form.email.data)
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_user.html', form=form)

@app.route('/add_dataset', methods=['GET', 'POST'])
def add_dataset():
    form = DatasetForm()
    if form.validate_on_submit():
        dataset = Dataset(
            name=form.name.data,
            size=form.size.data,
            user_id=form.user_id.data
        )
        db.session.add(dataset)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_dataset.html', form=form)

if __name__ == '__main__':
    app.run(debug=True)

File: templates/users.html

{% extends 'base.html' %}

{% block title %}Users and Datasets{% endblock %}

{% block content %}
    <h1>Users and Their Datasets</h1>
    <a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">Add User</a>
    <a href="{{ url_for('add_dataset') }}" class="btn btn-primary mb-3">Add Dataset</a>
    {% for user in users %}
        <h3>{{ user.name | escape }} ({{ user.email | escape }})</h3>
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>Dataset Name</th>
                    <th>Size (MB)</th>
                </tr>
            </thead>
            <tbody>
                {% for dataset in user.datasets %}
                    <tr>
                        <td>{{ dataset.name | escape }}</td>
                        <td>{{ dataset.size }}</td>
                    </tr>
                {% endfor %}
            </tbody>
        </table>
    {% endfor %}
{% endblock %}

File: templates/add_dataset.html

{% extends 'base.html' %}

{% block title %}Add Dataset{% endblock %}

{% block content %}
    <h1>Add Dataset</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.name.label }}
            {{ form.name(class="form-control") }}
            {% for error in form.name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.size.label }}
            {{ form.size(class="form-control") }}
            {% for error in form.size.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.user_id.label }}
            {{ form.user_id(class="form-control") }}
            {% for error in form.user_id.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Output (/):

  • GET /: Displays users and their associated datasets.
  • GET /add_user: Shows a form to add a user.
  • GET /add_dataset: Shows a form to add a dataset linked to a user.
  • POST /add_user or /add_dataset (valid data): Adds the record and redirects to the index.

Explanation:

  • db.relationship: Establishes a one-to-many relationship between User and Dataset.
  • db.ForeignKey: Links Dataset.user_id to User.id.
  • backref='user': Allows access to the parent User from a Dataset.
  • lazy=True: Loads related objects on demand for efficiency.

3.1 Many-to-Many Relationship

Many-to-many relationships require an association table.

Example: Many-to-Many Relationship

File: app.py (Snippet)

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

# Association table for many-to-many relationship
user_project = db.Table('user_project',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('project_id', db.Integer, db.ForeignKey('project.id'), primary_key=True)
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    projects = db.relationship('Project', secondary=user_project, backref=db.backref('users', lazy=True))

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)

Explanation:

  • db.Table: Defines the association table user_project.
  • secondary=user_project: Links User and Project in a many-to-many relationship.
  • backref='users': Allows access to users from a project.

04. Models in Data-Driven Applications

Data-driven applications, such as ML platforms, require models to store complex data like model metadata or predictions.

Example: ML Model Metadata Model

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, FloatField, SubmitField
from wtforms.validators import DataRequired
from datetime import datetime
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'your-secret-key')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///instance/database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Define ModelMetadata model
class ModelMetadata(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    accuracy = db.Column(db.Float, nullable=False)
    version = db.Column(db.String(20), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    predictions = db.relationship('Prediction', backref='model', lazy=True)

# Define Prediction model
class Prediction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    input_data = db.Column(db.Text, nullable=False)
    result = db.Column(db.Float, nullable=False)
    model_id = db.Column(db.Integer, db.ForeignKey('model_metadata.id'), nullable=False)

# Forms
class ModelForm(FlaskForm):
    name = StringField('Model Name', validators=[DataRequired()])
    accuracy = FloatField('Accuracy', validators=[DataRequired()])
    version = StringField('Version')
    submit = SubmitField('Add Model')

class PredictionForm(FlaskForm):
    input_data = StringField('Input Data', validators=[DataRequired()])
    result = FloatField('Result', validators=[DataRequired()])
    model_id = IntegerField('Model ID', validators=[DataRequired()])
    submit = SubmitField('Add Prediction')

# Create database tables
with app.app_context():
    db.create_all()

@app.route('/')
def index():
    models = ModelMetadata.query.all()
    return render_template('models.html', models=models)

@app.route('/add_model', methods=['GET', 'POST'])
def add_model():
    form = ModelForm()
    if form.validate_on_submit():
        model = ModelMetadata(
            name=form.name.data,
            accuracy=form.accuracy.data,
            version=form.version.data
        )
        db.session.add(model)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_model.html', form=form)

@app.route('/add_prediction', methods=['GET', 'POST'])
def add_prediction():
    form = PredictionForm()
    if form.validate_on_submit():
        prediction = Prediction(
            input_data=form.input_data.data,
            result=form.result.data,
            model_id=form.model_id.data
        )
        db.session.add(prediction)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_prediction.html', form=form)

if __name__ == '__main__':
    app.run(debug=True)

File: templates/models.html

{% extends 'base.html' %}

{% block title %}ML Models{% endblock %}

{% block content %}
    <h1>ML Model Metadata</h1>
    <a href="{{ url_for('add_model') }}" class="btn btn-primary mb-3">Add Model</a>
    <a href="{{ url_for('add_prediction') }}" class="btn btn-primary mb-3">Add Prediction</a>
    {% for model in models %}
        <h3>{{ model.name | escape }} (Version: {{ model.version | escape }})</h3>
        <p>Accuracy: {{ model.accuracy }}</p>
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>Input Data</th>
                    <th>Result</th>
                </tr>
            </thead>
            <tbody>
                {% for prediction in model.predictions %}
                    <tr>
                        <td>{{ prediction.input_data | escape }}</td>
                        <td>{{ prediction.result }}</td>
                    </tr>
                {% endfor %}
            </tbody>
        </table>
    {% endfor %}
{% endblock %}

File: templates/add_model.html

{% extends 'base.html' %}

{% block title %}Add Model{% endblock %}

{% block content %}
    <h1>Add Model Metadata</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.name.label }}
            {{ form.name(class="form-control") }}
            {% for error in form.name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.accuracy.label }}
            {{ form.accuracy(class="form-control") }}
            {% for error in form.accuracy.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.version.label }}
            {{ form.version(class="form-control") }}
            {% for error in form.version.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

File: templates/add_prediction.html

{% extends 'base.html' %}

{% block title %}Add Prediction{% endblock %}

{% block content %}
    <h1>Add Prediction</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.input_data.label }}
            {{ form.input_data(class="form-control") }}
            {% for error in form.input_data.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.result.label }}
            {{ form.result(class="form-control") }}
            {% for error in form.result.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.model_id.label }}
            {{ form.model_id(class="form-control") }}
            {% for error in form.model_id.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Output (/):

  • GET /: Displays ML models and their predictions.
  • GET /add_model: Shows a form to add model metadata.
  • GET /add_prediction: Shows a form to add a prediction linked to a model.
  • POST /add_model or /add_prediction (valid data): Adds the record and redirects to the index.

Explanation:

  • ModelMetadata: Stores ML model details (name, accuracy, version).
  • Prediction: Stores prediction data linked to a model via a foreign key.
  • Relationships: One-to-many between ModelMetadata and Prediction.
  • FlaskForm: Validates input and ensures CSRF protection.

05. Best Practices for Defining Models

5.1 Recommended Practices

  • Use Descriptive Names: Choose clear model and column names (e.g., User, email).
  • Enforce Constraints: Use nullable, unique, and default appropriately.
  • Define Relationships: Use db.relationship for clear, navigable links.
  • Validate Input: Pair models with Flask-WTF forms for data validation.
  • Use Indexes: Add index=True for frequently queried columns.
  • Secure Data: Avoid storing sensitive data in plaintext; use encryption if needed.

5.2 Performance Considerations

  • Lazy Loading: Use lazy=True for relationships to optimize queries.
  • Indexes: Add indexes for columns used in filters or joins.
  • Minimal Columns: Avoid unnecessary columns to reduce storage and query overhead.

5.3 Security Considerations

  • Prevent Injection: Use ORM queries instead of raw SQL.
  • Escape Outputs: Use | escape in templates to prevent XSS.
  • Restrict Access: Implement authentication to control data access.

Example: Insecure Model Definition

class User(db.Model):
    id = db.Column(db.Integer)  # Missing primary_key
    name = db.Column(db.String)  # Missing constraints
    email = db.Column(db.String)  # Missing unique constraint
    password = db.Column(db.String)  # Storing plaintext passwords

Correct:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)  # Store hashed passwords

5.4 Practices to Avoid

  • Avoid Missing Constraints: Always define primary_key, nullable, etc.
  • Avoid Plaintext Sensitive Data: Hash passwords and encrypt sensitive fields.
  • Avoid Overly Complex Models: Keep models focused and modular.

06. Conclusion

Defining models with SQLAlchemy in Flask enables robust data management for data-driven applications. Key takeaways:

  • Define models using db.Model with appropriate column types and constraints.
  • Use relationships (db.relationship, db.ForeignKey) for complex data structures.
  • Validate input with Flask-WTF and secure outputs to prevent vulnerabilities.
  • Optimize performance with indexes and lazy loading.

By following these practices, you can create scalable, secure Flask applications that efficiently manage data for workflows like ML, analytics, and user management!

Comments

Post a Comment