Flask入門編4:SQLAlchemyでデータベースを操作しよう

myapp/flask_app.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)


db_uri = 'mysql+pymysql://root:@localhost/mydb?charset=utf8'
app.config['SQLALCHEMY_DATABASE_URI'] = db_uri
db = SQLAlchemy(app)


class Player(db.Model):
    __tablename__ = 'players'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.Text())
    level = db.Column(db.Integer)
    job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'))


class Job(db.Model):
    __tablename__ = 'jobs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    job_name = db.Column(db.Text())
    vitality = db.Column(db.Integer)
    strength = db.Column(db.Integer)
    agility = db.Column(db.Integer)
    intelligence = db.Column(db.Integer)
    luck = db.Column(db.Integer)

    player = db.relationship('Player', backref=db.backref('jobs', lazy=True))


@app.route('/')
def select_sql():
    message = "Hello SQLAlchemy"

    players = Player.query.all()
    jobs = Job.query.all()

    return render_template('view.html', message = message, players = players, jobs = jobs)


@app.route('/show/<int:id>')
def show_player(id):
    message = "Hello player " + str(id)

    player = Player.query.get(id)

    return render_template('view_player.html', message = message, player = player)

myapp/templates/view.html

{% extends "layout.html" %}
{% block content %}
    <h1>Hello SQL</h1>
    <p>{{ message }}</p>

    <table>
    {% for player in players %}
        <tr>
            <td>{{ player.id }}</td>
            <td>{{ player.name }}</td>
            <td>{{ player.level }}</td>
            <td>{{ player.jobs.job_name }}</td>
            <td><a href='/show/{{ player.id }}'>表示</a></td>
        </tr>
    {% endfor %}
    </table>

    <h2>職業一覧</h2>
    <table>
        {% for job in jobs %}
        <tr>
            <td>{{ job.id }}</td>
            <td>{{ job.job_name }}</td>
            <td>{{ job.vitality }}</td>
            <td>{{ job.strength }}</td>
        </tr>
        {% endfor %}
    </table>

{% endblock %}

SQLAlchemy入門 SQLAlchemyとは - Python学習講座
http://www.python.ambitious-engineer.com/archives/1469

[オブジェクト関係マッピング - Qiita]
https://qiita.com/yk-nakamura/items/acd071f16cda844579b9

FlaskとPostgreSQLでウェブアプリを作ってHerokuで無料で運用する - Qiita
https://qiita.com/croquette0212/items/9b4dc5377e7d6f292671

Flask-SQLAlchemyのモデルでのリレーションシップ | blog.PanicBlanket.com
http://blog.panicblanket.com/archives/2987