MySQL

Integrating MySQL with a Flask application involves setting up a MySQL database, configuring Flask to connect to it, and using an ORM like SQLAlchemy to manage database operations. Here’s a step-by-step guide to integrating MySQL with Flask using Flask-SQLAlchemy:

Steps for Integrating MySQL with Flask

Install Necessary Packages:
- You need to install flask-sqlalchemy for ORM support and mysqlclient (or PyMySQL) for MySQL database drivers.
```
pip install flask-sqlalchemy mysqlclient
```
- Alternatively, you can use PyMySQL if mysqlclient is problematic on your platform.
```
pip install flask-sqlalchemy pymysql
```

Configure the MySQL Database:

Update the config.py file to specify the MySQL connection URI.

# config.py
import os

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'mysql+pymysql://username:password@localhost/db_name'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Replace username, password, localhost, and db_name with your MySQL credentials and database name.

Initialize Flask-SQLAlchemy:

Set up Flask-SQLAlchemy in your app/__init__.py file.

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 Your Models:

Create your data models in app/models.py using SQLAlchemy’s ORM capabilities.

# 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}>'

Create and Apply Migrations:

Use Flask-Migrate to handle database migrations. Install flask-migrate:

pip install flask-migrate

Set up Flask-Migrate in 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 the migration repository.

flask db init

Create and apply migrations.

flask db migrate -m "Initial migration."
flask db upgrade

Database Operations:

Perform CRUD operations within your routes using SQLAlchemy’s session management.

# 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'))

Create and Populate the Database:

You can use the Flask shell to interact with the database.

flask shell
>>> from app import db
>>> from app.models import User
>>> db.create_all()
>>> user = User(username='john_doe', email='john@example.com')
>>> db.session.add(user)
>>> db.session.commit()

Full Example Project Structure

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

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'mysql+pymysql://username:password@localhost/db_name'
    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

Packages: Install flask-sqlalchemy and mysqlclient (or PyMySQL).
Configuration: Set up your database URI in config.py.
Initialization: Initialize Flask-SQLAlchemy in your app setup.
Models: Define your database models using SQLAlchemy.
Migrations: Use Flask-Migrate for managing database migrations.
Operations: Perform CRUD operations using SQLAlchemy’s ORM.
Project Structure: Maintain a clear and organized project structure.

Following these steps will help you integrate MySQL into your Flask application effectively, allowing you to handle data with the flexibility and power of SQLAlchemy.

FlaskDatabase Integration

FlaskMongoDB