■ はじめに

https://dk521123.hatenablog.com/entry/2018/09/19/223200

の続き。

今回は、SQLAlchemy (アルケミー)で、外部キーがあるテーブルを連結する。

【１】サンプル

DBデータ

-- データベース
CREATE SCHEMA `sample_db`;

-- テーブル
CREATE TABLE `section` (
  `id` bigint(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `person` (
  `id` bigint(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `section_id` bigint(12) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `section_id_idx` (`section_id`),
  CONSTRAINT `section_id` FOREIGN KEY (`section_id`) REFERENCES `section` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- データ
INSERT INTO `sample_db`.`section` (`name`) VALUES ('Finance');
INSERT INTO `sample_db`.`section` (`name`) VALUES ('Accounting');
INSERT INTO `sample_db`.`section` (`name`) VALUES ('Engineering');
INSERT INTO `sample_db`.`section` (`name`) VALUES ('Development');

INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Mike', '1');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Tom', '2');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Sam', '2');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Sean', '3');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Kevin', '2');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('John', '1');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Kevin', '2');
INSERT INTO `sample_db`.`person` (`name`, `section_id`) VALUES ('Ken', '4');

SQLAlchemy-demo.py

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:password@localhost/sample_db?charset=utf8"
db = SQLAlchemy(app)

class Person(db.Model):
  __tablename__ = "person"
  id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
  name = db.Column(db.Text())
  section_id = db.Column(db.BigInteger, db.ForeignKey('section.id'))

class Section(db.Model):
  __tablename__ = "section"
  id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
  name = db.Column(db.Text())
  
  person = db.relationship('Person', backref=db.backref('section', lazy=True))
  
@app.route("/")
def selectAll():

  people = Person.query.all()
  return render_template("SQLAlchemy-demo.html", person_vals = people)

if __name__ == "__main__":
  app.run()
SQLAlchemy-demo.html

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>Flask - Demo</title>
</head>
<body>
<h1>Sample</h1>
<table>
{% for person in person_vals: %}
  <tr>
    <td>{{ person.id }}</td>
    <td>{{ person.name }}</td>
    <td>{{ person.section_id }}</td>
    <td>{{ person.section.name }}</td>
  </tr>
{% endfor %}
</table>
</body>
</html>

実行コマンド

python SQLAlchemy-demo.py

動作確認

* ブラウザで以下にアクセスする

http://localhost:5000

Flask ～入門編～
https://dk521123.hatenablog.com/entry/2018/09/11/021900
Flask ～ DB接続～
https://dk521123.hatenablog.com/entry/2018/09/18/220600
Flask ～ SQLAlchemy / 入門編～
https://dk521123.hatenablog.com/entry/2018/09/19/223200
Flask ～ REST API / 基本編～
https://dk521123.hatenablog.com/entry/2018/09/15/214600
Python ORM ～ SQLAlchemy / 入門編～
https://dk521123.hatenablog.com/entry/2025/10/07/133808
Python ORM ～ SQLAlchemy / 基本編～
https://dk521123.hatenablog.com/entry/2025/10/10/013250

ランキング参加中

プログラミング

【Python】Flask ～ SQLAlchemy / 基本編～

■ はじめに

【１】サンプル

関連記事