Database Integration

Integrating a database into a Flask application is essential for storing and managing data. Flask supports various databases, but the most common choices are relational databases like SQLite, PostgreSQL, and MySQL. Flask can work directly with these databases using tools like SQLAlchemy, a powerful ORM (Object-Relational Mapping) library, or through simpler interfaces like Flask-SQLite for lightweight needs.

Database Integration with Flask-SQLAlchemy

Flask-SQLAlchemy is a popular extension that simplifies using SQLAlchemy with Flask. It provides a higher-level interface to manage database connections, define models, and perform database operations.

Setting Up Flask-SQLAlchemy

Install Flask-SQLAlchemy:
```
pip install flask-sqlalchemy
```

Configure the Database:

Define your database configuration in the config.py file.

# config.py
import os

basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

SQLALCHEMY_DATABASE_URI specifies the database location. It can be a local SQLite database or a remote PostgreSQL/MySQL database URL.
SQLALCHEMY_TRACK_MODIFICATIONS disables the modification tracking feature to save resources.

Initialize Flask-SQLAlchemy:

Update app/__init__.py to include Flask-SQLAlchemy initialization.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    app.config.from_object('config.Config')

    db.init_app(app)

    from .routes import bp as main_bp
    app.register_blueprint(main_bp)

    return app

Define Database Models:

Create a file models.py in your app directory to define your data models using SQLAlchemy.

# app/models.py
from . import db

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 f'<User {self.username}>'

Here, we define a simple User model with id, username, and email fields.

Create and Initialize the Database:

Use Flask’s shell context to create the database tables.

# run.py
from app import create_app, db
from app.models import User

app = create_app()

@app.shell_context_processor
def make_shell_context():
    return {'db': db, 'User': User}

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

Run the Flask shell and create the database tables.

flask shell
>>> db.create_all()

Perform Database Operations:

Now, you can interact with the database through your Flask routes or a shell session.

# app/routes.py
from flask import Blueprint, render_template, request, redirect, url_for
from . import db
from .models import User

bp = Blueprint('main', __name__)

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

@bp.route('/add_user', methods=['POST'])
def add_user():
    username = request.form['username']
    email = request.form['email']
    new_user = User(username=username, email=email)
    db.session.add(new_user)
    db.session.commit()
    return redirect(url_for('main.index'))

In this example, we list all users on the index page and provide a route to add a new user to the database.

Using Flask-Migrate for Database Migrations

Flask-Migrate, based on Alembic, helps manage database migrations, allowing you to update the database schema as your application evolves.

Install Flask-Migrate:
```
pip install flask-migrate
```

Set Up Flask-Migrate:

Initialize Flask-Migrate in your app/__init__.py.

from flask_migrate import Migrate

def create_app():
    app = Flask(__name__)
    app.config.from_object('config.Config')

    db.init_app(app)
    migrate = Migrate(app, db)

    from .routes import bp as main_bp
    app.register_blueprint(main_bp)

    return app

Initialize Migrations:
- Run the following command to initialize the migrations directory.
```
flask db init
```
Create and Apply Migrations:
- When you make changes to your models, generate a new migration script.
```
flask db migrate -m "Initial migration."
```
- Apply the migration to update the database schema.
```
flask db upgrade
```

Example Project: Database Integration

Here’s a simplified example project structure with Flask-SQLAlchemy and Flask-Migrate.

Project Structure:

my_flask_project/
├── app/
│   ├── __init__.py
│   ├── routes.py
│   ├── models.py
│   ├── templates/
│   │   └── index.html
│   └── static/
│       ├── css/
│       │   └── style.css
│       ├── js/
│       │   └── script.js
│       └── images/
│           └── logo.png
├── migrations/
├── run.py
└── config.py

app/models.py:

from . import db

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 f'<User {self.username}>'

app/routes.py:

from flask import Blueprint, render_template, request, redirect, url_for
from . import db
from .models import User

bp = Blueprint('main', __name__)

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

@bp.route('/add_user', methods=['POST'])
def add_user():
    username = request.form['username']
    email = request.form['email']
    new_user = User(username=username, email=email)
    db.session.add(new_user)
    db.session.commit()
    return redirect(url_for('main.index'))

run.py:

from app import create_app, db
from app.models import User
from flask_migrate import Migrate

app = create_app()
migrate = Migrate(app, db)

@app.shell_context_processor
def make_shell_context():
    return {'db': db, 'User': User}

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

config.py:

import os

basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

app/templates/index.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>User List</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}">
</head>
<body>
    <h1>User List</h1>
    <ul>
        {% for user in users %}
        <li>{{ user.username }} - {{ user.email }}</li>
        {% endfor %}
    </ul>
    <h2>Add User</h2>
    <form action="{{ url_for('main.add_user') }}" method="post">
        <label for="username">Username:</label>
        <input type="text" id="username" name="username"><br>
        <label for="email">Email:</label>
        <input type="email" id="email" name="email"><br>
        <button type="submit">Add User</button>
    </form>
</body>
</html>

Summary

Flask-SQLAlchemy: Provides a powerful ORM for defining and interacting with your database models.
Flask-Migrate: Simplifies database schema changes and versioning through migrations.
Configuration: Use environment variables or configuration files to manage database URIs securely.
Models and Migrations: Define models to represent your database schema and use migrations to apply changes.

By following these steps, you can effectively integrate and manage a database in your Flask applications, ensuring a scalable and maintainable project structure.

FlaskStatic files

FlaskMySQL