以下に、本日作業した内容を「そのまま再現できるメモ」として、必要なコマンド／修正したファイル全文（index.html / server.py）／動作確認手順まで、全部まとめて記載します。
1. 目的
-
index.htmlで iframe 4枚（sample2 / rides / bus_map / trips_map） を同時表示している -
ユーザが 任意の iframe を選び、ボタン押下で「ブラウザに表示されている内容と一致する PNG」 をローカル保存する
2. 問題と原因
2.1 保存画像と表示内容が一致しない
-
/shotで Playwright（別ブラウザセッション）を使ってHTMLを描画してPNG化していた -
そのPlaywright側には ブラウザの cookie（date_start/date_end） が無い
→ CSV生成期間がズレる → 表示と保存が不一致
2.2 bus_map / trips_map が「過去のデータ」っぽくなる
-
cookieが更新されても、iframeが「同じURLのまま」再利用され、再描画が起きない／キャッシュが残るケース
→ bus_map/trips_map の表示が更新されず古い状態に見える
3. 対策（実装方針）
3.1 表示と保存の“期間”を一致させる
-
/shotに来たリクエストの cookie（date_start/date_end）を Playwright に注入 -
Playwright が取得するCSVもブラウザと同じ期間になる
3.2 iframe表示を常に最新化（強制リロード）
-
index.htmlは/range.jsonから start/end を取得 -
iframe の
srcを?v=<start>_<end>_<timestamp>付きURLに置き換える
→ cookie変更後でも必ず再読み込みが走る
3.3 保存対象は「今iframeが表示しているURL（クエリ込み）」
-
保存時、固定パスではなく
iframe.srcを採用し、pathname + searchを/shotに渡す
→ 「見えているもの」＝「保存されるもの」を保証
4. 必要コマンド（サーバ側）
4.1 Playwright の導入
pip install playwright
playwright install chromium ※ Linux環境でブラウザ依存ライブラリ不足が出る場合（最小例、Ubuntu系）sudo apt-get update
sudo apt-get install -y \
libnss3 libnspr4 libatk1.0-0 libatk-bridge2.0-0 libcups2 \
libdrm2 libxkbcommon0 libxcomposite1 libxdamage1 libxfixes3 \
libxrandr2 libgbm1 libasound2 libpangocairo-1.0-0 libpango-1.0-0 \
libgtk-3-04.2 起動
python3 server.py（ポートを変えるなら）
SAMPLE2_PORT=18080 python3 server.py5. 修正後のコード（全文）
5.1 index.html（全文）
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>sample2 + rides + bus_map + trips_map</title>
<style>
body{font-family:sans-serif;margin:16px}
.row{display:flex;gap:12px;align-items:center;flex-wrap:wrap}
.panel{margin-top:16px;border:1px solid #ccc;border-radius:8px;overflow:hidden}
.panel h2{margin:0;padding:8px;background:#f5f5f5;display:flex;align-items:center;justify-content:space-between;gap:12px}
.panel h2 .right{display:flex;align-items:center;gap:8px;flex-wrap:wrap}
iframe{width:100%;border:0}
.h1{height:640px}
.h2{height:640px}
.h3{height:720px}
.h4{height:720px}
a.btn{padding:6px 10px;border:1px solid #888;text-decoration:none;color:#000;border-radius:8px;background:#fff}
button.btn{padding:6px 10px;border:1px solid #888;border-radius:8px;background:#fff;cursor:pointer}
select{padding:6px 8px}
.small{font-size:12px;color:#333}
</style>
</head>
<body>
<h1>日時指定（4表示）</h1>
<form method="POST" action="/set_range" class="row">
<label>開始日 <input id="start" type="date" name="start" required></label>
<label>終了日 <input id="end" type="date" name="end" required></label>
<button type="submit">OK（表示）</button>
<a class="btn" href="/sample2.csv?download=1">CSV(sample2)</a>
<a class="btn" href="/ride_summary.csv?download=1">CSV(rides)</a>
<a class="btn" href="/bus_stops.csv?download=1">CSV(bus_stops)</a>
<a class="btn" href="/trips.csv?download=1">CSV(trips)</a>
</form>
<div class="row" style="margin-top:10px">
<span class="small">保存対象</span>
<select id="shotTarget">
<option value="f1">sample2</option>
<option value="f2">rides</option>
<option value="f3">bus_map</option>
<option value="f4">trips_map</option>
</select>
<button type="button" class="btn" onclick="saveSelected()">選択した画面をPNG保存</button>
<span id="shotMsg" class="small"></span>
</div>
<div class="panel">
<h2>
<span>sample2</span>
<span class="right">
<button type="button" class="btn" onclick="saveFrame('f1','sample2')">PNG保存</button>
</span>
</h2>
<iframe id="f1" class="h1" src="/sample2.html"></iframe>
</div>
<div class="panel">
<h2>
<span>rides</span>
<span class="right">
<button type="button" class="btn" onclick="saveFrame('f2','rides')">PNG保存</button>
</span>
</h2>
<iframe id="f2" class="h2" src="/rides.html"></iframe>
</div>
<div class="panel">
<h2>
<span>bus_map</span>
<span class="right">
<button type="button" class="btn" onclick="saveFrame('f3','bus_map')">PNG保存</button>
</span>
</h2>
<iframe id="f3" class="h3" src="/bus_map.html"></iframe>
</div>
<div class="panel">
<h2>
<span>trips_map</span>
<span class="right">
<button type="button" class="btn" onclick="saveFrame('f4','trips_map')">PNG保存</button>
</span>
</h2>
<iframe id="f4" class="h4" src="/trips_map.html"></iframe>
</div>
<script>
function pad(n){ return String(n).padStart(2,'0'); }
function nowStr(){
const d = new Date();
return `${d.getFullYear()}${pad(d.getMonth()+1)}${pad(d.getDate())}_${pad(d.getHours())}${pad(d.getMinutes())}${pad(d.getSeconds())}`;
}
// cookieの開始/終了をサーバから取得してフォームに反映し、iframeを強制リロード
async function syncRangeAndReloadFrames(){
const res = await fetch('/range.json', {cache:'no-store'});
if(!res.ok) return;
const j = await res.json();
const startEl = document.getElementById('start');
const endEl = document.getElementById('end');
if(j.start) startEl.value = j.start;
if(j.end) endEl.value = j.end;
// start/end を含むクエリを付与して iframe を更新（必ず再読み込みさせる）
const v = encodeURIComponent(`${j.start||''}_${j.end||''}_${Date.now()}`);
document.getElementById('f1').src = `/sample2.html?v=${v}`;
document.getElementById('f2').src = `/rides.html?v=${v}`;
document.getElementById('f3').src = `/bus_map.html?v=${v}`;
document.getElementById('f4').src = `/trips_map.html?v=${v}`;
}
window.addEventListener('load', () => {
syncRangeAndReloadFrames();
});
function saveSelected(){
const sel = document.getElementById('shotTarget').value;
const map = {f1:'sample2', f2:'rides', f3:'bus_map', f4:'trips_map'};
saveFrame(sel, map[sel] || 'screen');
}
// 「いまiframeで表示しているURL（クエリ込み）」を /shot に渡す
async function saveFrame(frameId, label){
const msg = document.getElementById('shotMsg');
msg.textContent = '画像生成中...';
const iframe = document.getElementById(frameId);
if(!iframe){
msg.textContent = '失敗: iframeが見つかりません';
return;
}
const url = new URL(iframe.src);
const pathWithQuery = url.pathname + url.search;
const w = 1400, h = 900;
const shotUrl = `/shot?path=${encodeURIComponent(pathWithQuery)}&w=${w}&h=${h}`;
try{
const res = await fetch(shotUrl, {cache:'no-store'});
if(!res.ok){
msg.textContent = `失敗: /shot HTTP ${res.status}`;
return;
}
const blob = await res.blob();
const a = document.createElement('a');
const obj = URL.createObjectURL(blob);
a.href = obj;
a.download = `${label}_${nowStr()}.png`;
a.click();
URL.revokeObjectURL(obj);
msg.textContent = '保存しました';
}catch(e){
msg.textContent = '失敗: 通信/生成エラー';
}
}
</script>
</body>
</html>5.2 server.py（全文）
# server.py
from __future__ import annotations
import os
import io
import csv
import datetime as dt
from dataclasses import dataclass
from flask import (
Flask,
request,
make_response,
send_from_directory,
redirect,
Response,
abort,
jsonify,
)
import psycopg2
APP_HOST = "0.0.0.0"
APP_PORT = int(os.getenv("SAMPLE2_PORT", "18080"))
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
# ========= DB 接続 =========
PGHOST = os.getenv("PGHOST", "localhost")
PGPORT = int(os.getenv("PGPORT", "15432"))
PGDATABASE = os.getenv("PGDATABASE", "tsubame_db")
PGUSER = os.getenv("PGUSER", "postgres")
PGPASSWORD = os.getenv("PGPASSWORD", "password")
# ========= cookie =========
COOKIE_START = "date_start"
COOKIE_END = "date_end"
# ========= table / column =========
STOP_TABLE = "stop_tsubame"
STOP_ID_COL = "stop_num"
STOP_NAME_COL = "name"
STOP_GEOM_COL = "geom"
LOG_TABLE = "log_pass_tsubame"
LOG_TIME_COL = "ride_time"
LOG_ONOFF_COL = "on_off"
LOG_GEOM_COL = "geom"
app = Flask(__name__, static_folder=None)
@dataclass(frozen=True)
class DateRange:
start: dt.date
end: dt.date
@property
def end_exclusive(self) -> dt.date:
return self.end + dt.timedelta(days=1)
def parse_date(s: str) -> dt.date:
return dt.datetime.strptime(s, "%Y-%m-%d").date()
def get_range(req) -> DateRange:
s = req.cookies.get(COOKIE_START)
e = req.cookies.get(COOKIE_END)
# cookie が無い場合は「今月1日〜今日」
if not s or not e:
today = dt.date.today()
start = today.replace(day=1)
end = today
return DateRange(start, end)
start = parse_date(s)
end = parse_date(e)
if end < start:
start, end = end, start
return DateRange(start, end)
def db_connect():
return psycopg2.connect(
host=PGHOST,
port=PGPORT,
dbname=PGDATABASE,
user=PGUSER,
password=PGPASSWORD,
)
def csv_response(filename: str, text: str, download: bool):
resp = make_response(text)
resp.headers["Cache-Control"] = "no-store"
if download:
resp.headers["Content-Type"] = "application/octet-stream"
resp.headers["Content-Disposition"] = f'attachment; filename="{filename}"'
else:
resp.headers["Content-Type"] = "text/csv; charset=utf-8"
resp.headers["Content-Disposition"] = f'inline; filename="{filename}"'
return resp
# ========= 画面 =========
@app.get("/")
def root():
return send_from_directory(BASE_DIR, "index.html")
@app.get("/range.json")
def range_json():
# index.html が cookie の値を取得するため
dr = get_range(request)
return jsonify({
"start": dr.start.strftime("%Y-%m-%d"),
"end": dr.end.strftime("%Y-%m-%d"),
})
@app.post("/set_range")
def set_range():
s = request.form["start"]
e = request.form["end"]
resp = redirect("/")
resp.set_cookie(COOKIE_START, s, max_age=86400 * 365)
resp.set_cookie(COOKIE_END, e, max_age=86400 * 365)
return resp
# ========= 画面保存（PNG） =========
@app.get("/shot")
def shot():
"""
/shot?path=/bus_map.html?v=...&w=1400&h=900
- path は「/から始まるパス + クエリ」を許可（iframeの表示URLをそのまま渡す）
- /shot に来た cookie（date_start/date_end）を Playwright に注入
→ ブラウザ表示と同じ期間で描画される
"""
path = request.args.get("path", "")
if not path.startswith("/"):
abort(400)
w = int(request.args.get("w", "1400"))
h = int(request.args.get("h", "900"))
if w < 200 or h < 200 or w > 5000 or h > 5000:
abort(400)
base = request.host_url.rstrip("/") # 例: http://127.0.0.1:18080
target_url = f"{base}{path}"
try:
from playwright.sync_api import sync_playwright
except Exception:
return Response(
"Playwright が未導入です。サーバ側で `pip install playwright` と "
"`playwright install chromium` を実行してください。",
status=500,
mimetype="text/plain; charset=utf-8",
)
# cookie を Playwright に注入
date_start = request.cookies.get(COOKIE_START)
date_end = request.cookies.get(COOKIE_END)
try:
with sync_playwright() as p:
browser = p.chromium.launch()
context = browser.new_context(viewport={"width": w, "height": h})
cookies = []
if date_start:
cookies.append({"name": COOKIE_START, "value": date_start, "url": base})
if date_end:
cookies.append({"name": COOKIE_END, "value": date_end, "url": base})
if cookies:
context.add_cookies(cookies)
page = context.new_page()
page.goto(target_url, wait_until="networkidle")
# JS描画（Chart/Leaflet等）待ちの保険
page.wait_for_timeout(800)
png = page.screenshot(full_page=True, type="png")
context.close()
browser.close()
except Exception as e:
return Response(
f"shot failed: {e}",
status=500,
mimetype="text/plain; charset=utf-8",
)
resp = Response(png, mimetype="image/png")
resp.headers["Cache-Control"] = "no-store"
return resp
# ---------- sample2.csv ----------
@app.get("/sample2.csv")
def sample2_csv():
dr = get_range(request)
sql = f"""
SELECT on_count, COUNT(*) AS num_psg
FROM (
SELECT psg_id, COUNT(*) AS on_count
FROM {LOG_TABLE}
WHERE {LOG_TIME_COL} >= %(s)s
AND {LOG_TIME_COL} < %(e)s
AND {LOG_ONOFF_COL} = 1
GROUP BY psg_id
) t
GROUP BY on_count
ORDER BY on_count
"""
out = io.StringIO()
w = csv.writer(out)
w.writerow(["on_count", "num_psg", "cum_pct"])
with db_connect() as c:
with c.cursor() as cur:
cur.execute(sql, {"s": dr.start, "e": dr.end_exclusive})
rows = cur.fetchall()
total = sum(r[1] for r in rows)
cum = 0
for oc, n in rows:
cum += n
pct = (cum / total * 100) if total else 0
w.writerow([oc, n, f"{pct:.3f}"])
return csv_response("sample2.csv", out.getvalue(), request.args.get("download") == "1")
# ---------- ride_summary.csv ----------
@app.get("/ride_summary.csv")
def ride_summary_csv():
dr = get_range(request)
sql = f"""
SELECT {LOG_TIME_COL}::date,
EXTRACT(hour FROM {LOG_TIME_COL})::int,
COUNT(*)
FROM {LOG_TABLE}
WHERE {LOG_TIME_COL} >= %(s)s
AND {LOG_TIME_COL} < %(e)s
AND {LOG_ONOFF_COL} = 1
AND EXTRACT(hour FROM {LOG_TIME_COL}) BETWEEN 10 AND 13
GROUP BY 1,2
ORDER BY 1,2
"""
data = {}
with db_connect() as c:
with c.cursor() as cur:
cur.execute(sql, {"s": dr.start, "e": dr.end_exclusive})
for d, h, n in cur.fetchall():
key = d.strftime("%Y/%m/%d")
data.setdefault(key, {10: 0, 11: 0, 12: 0, 13: 0})
data[key][h] = n
out = io.StringIO()
w = csv.writer(out)
w.writerow(["日付", "10時", "11時", "12時", "13時"])
for d in sorted(data):
w.writerow([d, data[d][10], data[d][11], data[d][12], data[d][13]])
return csv_response("ride_summary.csv", out.getvalue(), request.args.get("download") == "1")
# ---------- bus_stops.csv ----------
@app.get("/bus_stops.csv")
def bus_stops_csv():
dr = get_range(request)
include_all = request.args.get("all") == "1" # /bus_stops.csv?all=1 で全停留所
sql = f"""
WITH counts AS (
SELECT
s.{STOP_ID_COL} AS {STOP_ID_COL},
SUM(CASE WHEN l.{LOG_ONOFF_COL} = 1 THEN 1 ELSE 0 END) AS board,
SUM(CASE WHEN l.{LOG_ONOFF_COL} = 0 THEN 1 ELSE 0 END) AS alight
FROM {LOG_TABLE} AS l
JOIN LATERAL (
SELECT {STOP_ID_COL}
FROM {STOP_TABLE}
ORDER BY {STOP_GEOM_COL} <-> l.{LOG_GEOM_COL}
LIMIT 1
) AS s ON TRUE
WHERE l.{LOG_TIME_COL} >= %(s)s
AND l.{LOG_TIME_COL} < %(e)s
GROUP BY s.{STOP_ID_COL}
)
SELECT
regexp_replace(st.{STOP_NAME_COL}, '^([0-9]+)\\s*[．.]\\s*', '\\1. ') AS name,
ST_Y(st.{STOP_GEOM_COL}) AS lat,
ST_X(st.{STOP_GEOM_COL}) AS lng,
COALESCE(c.board, 0) AS board,
COALESCE(c.alight, 0) AS alight
FROM {STOP_TABLE} AS st
LEFT JOIN counts AS c
ON c.{STOP_ID_COL} = st.{STOP_ID_COL}
{"WHERE c."+STOP_ID_COL+" IS NOT NULL" if not include_all else ""}
ORDER BY st.{STOP_ID_COL};
"""
out = io.StringIO()
w = csv.writer(out)
w.writerow(["name", "lat", "lng", "board", "alight"])
with db_connect() as c:
with c.cursor() as cur:
cur.execute(sql, {"s": dr.start, "e": dr.end_exclusive})
for name, lat, lng, board, alight in cur.fetchall():
w.writerow([name, lat, lng, board, alight])
return csv_response("bus_stops.csv", out.getvalue(), request.args.get("download") == "1")
# ---------- trips.csv ----------
@app.get("/trips.csv")
def trips_csv():
dr = get_range(request)
sql = f"""
WITH with_stop AS (
SELECT
l.psg_id,
l.{LOG_TIME_COL} AS ride_time,
l.{LOG_ONOFF_COL} AS on_off,
(s.stop_num::text || '. ' ||
regexp_replace(s.name, '^[0-9]+[．\\.]\\s*', '')
) AS stop_name
FROM {LOG_TABLE} l
JOIN LATERAL (
SELECT stop_num, name, geom
FROM {STOP_TABLE}
ORDER BY l.{LOG_GEOM_COL} <-> geom
LIMIT 1
) s ON true
WHERE l.{LOG_TIME_COL} >= %(s)s
AND l.{LOG_TIME_COL} < %(e)s
),
seq AS (
SELECT
psg_id,
ride_time,
on_off,
stop_name,
SUM(CASE WHEN on_off = 1 THEN 1 ELSE 0 END)
OVER (PARTITION BY psg_id ORDER BY ride_time) AS trip_no
FROM with_stop
),
od AS (
SELECT
psg_id,
trip_no,
(array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 1))[1] AS "from",
(
array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 0)
)[
array_length(
array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 0)
, 1)
] AS "to"
FROM seq
WHERE trip_no > 0
GROUP BY psg_id, trip_no
)
SELECT
"from",
"to",
COUNT(*) AS trips
FROM od
WHERE "from" IS NOT NULL
AND "to" IS NOT NULL
AND "from" <> "to"
GROUP BY "from", "to"
ORDER BY "from", "to";
"""
out = io.StringIO()
w = csv.writer(out)
w.writerow(["from", "to", "trips"])
with db_connect() as c:
with c.cursor() as cur:
cur.execute(sql, {"s": dr.start, "e": dr.end_exclusive})
for f, t, n in cur.fetchall():
w.writerow([f, t, n])
return csv_response("trips.csv", out.getvalue(), request.args.get("download") == "1")
# ---------- HTML ----------
@app.get("/sample2.html")
def sample2_html():
return send_from_directory(BASE_DIR, "sample2.html")
@app.get("/rides.html")
def rides_html():
return send_from_directory(BASE_DIR, "rides.html")
@app.get("/bus_map.html")
def bus_map_html():
return send_from_directory(BASE_DIR, "bus_map_from_csv_fix.html")
@app.get("/trips_map.html")
def trips_map_html():
return send_from_directory(BASE_DIR, "trips_map.html")
# ---------- その他（静的ファイル） ----------
@app.get("/<path:filename>")
def any_file(filename):
p = os.path.join(BASE_DIR, filename)
if os.path.exists(p):
return send_from_directory(BASE_DIR, filename)
return Response("not found", 404)
if __name__ == "__main__":
app.run(host=APP_HOST, port=APP_PORT)6. 動作確認手順（再現手順）
-
DB（PostgreSQL）が起動していること（既存の前提）
-
Playwright を導入（初回のみ）
pip install playwright
playwright install chromium3. server.py 起動
python3 server.py-
ブラウザでアクセス
-
http://127.0.0.1:18080/（ポートが違う場合は読み替え）
-
開始日・終了日を入力して「OK（表示）」
-
4画面が更新される（iframeが
?v=...で強制リロードされる） -
「選択した画面をPNG保存」または各パネルの「PNG保存」を押す
-
表示内容と一致するPNGがローカルに保存される
7. 最終的に効いたポイント（短縮メモ）
-
/shotに cookie を渡す（Playwrightへ add_cookies） -
iframe は必ず再読み込みさせる（
?v=start_end_timestamp） -
保存対象は iframe の現在表示URL（pathname + search）