システムの概要
このアプリケーションは、生徒情報の登録、成績の入力、成績一覧の表示、および生徒ごとの平均点計算・表示機能を提供する。フロントエンドはHTMLとJavaScriptで構築し、バックエンドはFastAPIフレームワークを使用してデータの永続化と検索を処理し、データベースにはSQLiteを採用している。
フロントエンドの実装
UI構成とスタイリング
フロントエンドはHTMLとCSSを用いてレイアウトとスタイルを定義する。Flexboxを利用してページ全体を「生徒情報入力」「成績入力」「成績一覧」「平均点表示」の4つのセクションに分割し、カード型のデザインを採用することで視認性と操作性を向上させている。
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>生徒成績管理システム</title>
<style>
* {
box-sizing: border-box;
font-family: 'Helvetica', sans-serif;
}
body {
max-width: 1000px;
margin: 0 auto;
padding: 25px;
background-color: #fafafa;
}
.main-wrapper {
display: flex;
flex-wrap: wrap;
gap: 25px;
}
.card {
background: #ffffff;
padding: 25px;
border-radius: 10px;
box-shadow: 0 4px 12px rgba(0, 0, 0, 0.08);
flex: 1;
min-width: 320px;
}
h1, h2 { color: #2c3e50; }
form { display: flex; flex-direction: column; gap: 12px; }
input, select, button {
padding: 12px;
border: 1px solid #bdc3c7;
border-radius: 6px;
font-size: 15px;
}
button {
background: #3498db;
color: #ffffff;
border: none;
cursor: pointer;
transition: background 0.3s ease;
}
button:hover { background: #2980b9; }
table { width: 100%; border-collapse: collapse; margin-top: 15px; }
th, td { border: 1px solid #ecf0f1; padding: 10px; text-align: left; }
th { background-color: #ecf0f1; }
.avg-box { background: #e8f8f5; padding: 20px; border-radius: 10px; margin-top: 20px; }
.err-msg { color: #e74c3c; margin-top: 5px; font-size: 14px; }
</style>
</head>
<body>
<h1>生徒成績管理システム</h1>
<div class="main-wrapper">
<div class="card">
<h2>生徒登録</h2>
<form id="pupil-form">
<input type="text" id="pupil-name" placeholder="氏名" required>
<input type="number" id="pupil-age" placeholder="年齢" min="1" required>
<input type="text" id="class-group" placeholder="クラス" required>
<button type="submit">登録</button>
</form>
<div id="pupil-err" class="err-msg"></div>
</div>
<div class="card">
<h2>成績登録</h2>
<form id="grade-form">
<select id="pupil-dropdown" required>
<option value="">--生徒を選択--</option>
</select>
<input type="text" id="subject-name" placeholder="科目" required>
<input type="number" id="grade-value" placeholder="点数" min="0" max="100" step="0.1" required>
<button type="submit">登録</button>
</form>
<div id="grade-err" class="err-msg"></div>
</div>
</div>
<div class="card">
<h2>成績一覧</h2>
<div id="grades-container"></div>
</div>
<div class="card avg-box">
<h2>平均点</h2>
<button onclick="fetchAverages()">平均点を更新</button>
<div id="averages-container"></div>
</div>
<script>
const API_BASE = 'http://localhost:8000/api';
async function refreshPupilOptions() {
try {
const res = await fetch(`${API_BASE}/pupils/`);
const data = await res.json();
const select = document.getElementById('pupil-dropdown');
select.innerHTML = '<option value="">--生徒を選択--</option>';
data.forEach(p => {
const opt = document.createElement('option');
opt.value = p.id;
opt.textContent = `${p.name} (${p.class_group})`;
select.appendChild(opt);
});
} catch (e) {
console.error('生徒データの取得に失敗:', e);
}
}
async function renderGradeTables() {
try {
const res = await fetch(`${API_BASE}/pupils/`);
const pupils = await res.json();
const container = document.getElementById('grades-container');
container.innerHTML = '';
for (const p of pupils) {
const gRes = await fetch(`${API_BASE}/pupils/${p.id}/grades`);
const grades = await gRes.json();
if (grades.length > 0) {
const tbl = document.createElement('table');
tbl.innerHTML = `
<caption>${p.name} (${p.class_group}) - 成績表</caption>
<thead><tr><th>科目</th><th>点数</th><th>登録日時</th></tr></thead>
<tbody>
${grades.map(g => `
<tr>
<td>${g.subject}</td>
<td>${g.grade}</td>
<td>${new Date(g.recorded_at).toLocaleString()}</td>
</tr>
`).join('')}
</tbody>
`;
container.appendChild(tbl);
}
}
} catch (e) {
console.error('成績データの取得に失敗:', e);
}
}
async function fetchAverages() {
try {
const res = await fetch(`${API_BASE}/pupils/averages`);
const avgs = await res.json();
const container = document.getElementById('averages-container');
let html = '<ul>';
avgs.forEach(a => {
const val = a.average_grade !== null ? a.average_grade.toFixed(2) : 'データなし';
html += `<li><strong>${a.name}</strong>: ${val}点</li>`;
});
html += '</ul>';
container.innerHTML = html;
} catch (e) {
console.error('平均点の取得に失敗:', e);
}
}
document.getElementById('pupil-form').addEventListener('submit', async (e) => {
e.preventDefault();
const errDiv = document.getElementById('pupil-err');
errDiv.textContent = '';
const payload = {
name: document.getElementById('pupil-name').value,
age: parseInt(document.getElementById('pupil-age').value),
class_group: document.getElementById('class-group').value
};
try {
const res = await fetch(`${API_BASE}/pupils/`, {
method: 'POST',
headers: {'Content-Type': 'application/json'},
body: JSON.stringify(payload)
});
if (res.ok) {
e.target.reset();
await refreshPupilOptions();
await renderGradeTables();
} else {
const err = await res.json();
throw new Error(err.detail || '登録失敗');
}
} catch (err) {
errDiv.textContent = err.message;
}
});
document.getElementById('grade-form').addEventListener('submit', async (e) => {
e.preventDefault();
const errDiv = document.getElementById('grade-err');
errDiv.textContent = '';
const payload = {
pupil_id: parseInt(document.getElementById('pupil-dropdown').value),
subject: document.getElementById('subject-name').value,
grade: parseFloat(document.getElementById('grade-value').value)
};
try {
const res = await fetch(`${API_BASE}/grades/`, {
method: 'POST',
headers: {'Content-Type': 'application/json'},
body: JSON.stringify(payload)
});
if (res.ok) {
e.target.reset();
await renderGradeTables();
await fetchAverages();
} else {
const err = await res.json();
throw new Error(err.detail || '登録失敗');
}
} catch (err) {
errDiv.textContent = err.message;
}
});
window.addEventListener('DOMContentLoaded', async () => {
await refreshPupilOptions();
await renderGradeTables();
await fetchAverages();
});
</script>
</body>
</html>
バックエンドの実装
FastAPIフレームワークを用いてRESTful APIを構築する。Pythonの型ヒントを活用し、Pydanticによるデータバリデーションとシリアライズを行う。データベース接続はSQLiteを使用し、CORS設定を施すことでフロントエンドからのアクセスを許可する。
from fastapi import FastAPI, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
import sqlite3
import datetime
app = FastAPI()
app.add_middleware(
CORSMiddleware,
allow_origins=["*"],
allow_methods=["*"],
allow_headers=["*"],
)
DB_PATH = "academy.db"
def setup_database():
with sqlite3.connect(DB_PATH) as conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS pupils (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
class_group TEXT NOT NULL
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS grades (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pupil_id INTEGER NOT NULL,
subject TEXT NOT NULL,
grade REAL NOT NULL,
recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pupil_id) REFERENCES pupils(id)
)
""")
conn.commit()
setup_database()
class PupilInput(BaseModel):
name: str
age: int
class_group: str
class GradeInput(BaseModel):
pupil_id: int
subject: str
grade: float
class PupilOutput(BaseModel):
id: int
name: str
age: int
class_group: str
class GradeOutput(BaseModel):
id: int
pupil_id: int
subject: str
grade: float
recorded_at: datetime.datetime
@app.post("/api/pupils/", response_model=PupilOutput)
def add_pupil(pupil: PupilInput):
try:
with sqlite3.connect(DB_PATH) as conn:
cur = conn.cursor()
cur.execute(
"INSERT INTO pupils (name, age, class_group) VALUES (?, ?, ?)",
(pupil.name, pupil.age, pupil.class_group)
)
new_id = cur.lastrowid
conn.commit()
return {"id": new_id, **pupil.dict()}
except sqlite3.Error as e:
raise HTTPException(status_code=400, detail=str(e))
@app.get("/api/pupils/", response_model=list[PupilOutput])
def list_pupils():
try:
with sqlite3.connect(DB_PATH) as conn:
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM pupils")
return [dict(row) for row in cur.fetchall()]
except sqlite3.Error as e:
raise HTTPException(status_code=400, detail=str(e))
@app.post("/api/grades/", response_model=GradeOutput)
def add_grade(grade: GradeInput):
try:
with sqlite3.connect(DB_PATH) as conn:
cur = conn.cursor()
cur.execute("SELECT id FROM pupils WHERE id = ?", (grade.pupil_id,))
if not cur.fetchone():
raise HTTPException(status_code=404, detail="対象の生徒が見つかりません")
cur.execute(
"INSERT INTO grades (pupil_id, subject, grade) VALUES (?, ?, ?)",
(grade.pupil_id, grade.subject, grade.grade)
)
new_id = cur.lastrowid
conn.commit()
cur.execute("SELECT recorded_at FROM grades WHERE id = ?", (new_id,))
recorded_at = cur.fetchone()[0]
return {"id": new_id, **grade.dict(), "recorded_at": recorded_at}
except sqlite3.Error as e:
raise HTTPException(status_code=400, detail=str(e))
@app.get("/api/pupils/{pupil_id}/grades", response_model=list[GradeOutput])
def get_pupil_grades(pupil_id: int):
try:
with sqlite3.connect(DB_PATH) as conn:
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM grades WHERE pupil_id = ?", (pupil_id,))
return [dict(row) for row in cur.fetchall()]
except sqlite3.Error as e:
raise HTTPException(status_code=400, detail=str(e))
@app.get("/api/pupils/averages")
def calculate_averages():
try:
with sqlite3.connect(DB_PATH) as conn:
cur = conn.cursor()
cur.execute("""
SELECT p.id, p.name, AVG(g.grade) as avg_grade
FROM pupils p
LEFT JOIN grades g ON p.id = g.pupil_id
GROUP BY p.id
""")
results = cur.fetchall()
return [{"pupil_id": row[0], "name": row[1], "average_grade": row[2]} for row in results]
except sqlite3.Error as e:
raise HTTPException(status_code=400, detail=str(e))
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="127.0.0.1", port=8000)