■ サンプル
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]]
