Fichiers
Théo GastineletClaude Opus 5.5 34c1bf6736 Base commune et stockage SQLite
Configuration par variables MC_*, heure de Paris sans zoneinfo,
JSON atomique, journal masquant les secrets, verrou, pause.
SQLite 14 jours, cle unique (source, station, dh_utc).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-16 18:55:48 +02:00

108 lignes
4.6 KiB
Python

"""Stockage SQLite : 14 jours glissants, une ligne par (source, station, instant UTC).
Chaque ligne garde sa source. Aucune fusion ni moyenne entre sources : une même station physique publiée
par deux réseaux donne deux séries distinctes (source différente).
"""
import datetime as dt
import sqlite3
from .commun import UTC, fmt_utc
CHAMPS = ("temperature", "humidite", "point_de_rosee", "pression", "vent_moyen", "vent_rafales",
"vent_direction", "pluie_1h", "pluie_cumul", "rayonnement")
SCHEMA = """
CREATE TABLE IF NOT EXISTS mesures (
source TEXT NOT NULL,
station_id TEXT NOT NULL,
dh_utc TEXT NOT NULL, -- 'AAAA-MM-JJ HH:MM:SS', toujours UTC
%s,
recu_utc TEXT NOT NULL, -- première réception (sert à mesurer le délai de publication)
UNIQUE (source, station_id, dh_utc)
);
CREATE INDEX IF NOT EXISTS mesures_dh ON mesures (dh_utc);
CREATE TABLE IF NOT EXISTS stations (
source TEXT NOT NULL,
station_id TEXT NOT NULL,
nom TEXT, lat REAL, lon REAL, altitude REAL, licence TEXT, lien TEXT, extra TEXT,
PRIMARY KEY (source, station_id)
);
""" % ",\n ".join("%s REAL" % c for c in CHAMPS)
class Stockage:
def __init__(self, chemin):
self.db = sqlite3.connect(str(chemin), timeout=30)
self.db.row_factory = sqlite3.Row
self.db.executescript(SCHEMA)
self.db.commit()
def fermer(self):
self.db.close()
def ajouter(self, source, station_id, mesures, recu):
"""Insère des mesures {dh_utc, champs...}. Les doublons (même clé) sont ignorés, la première
réception est conservée. Renvoie le nombre de lignes nouvelles."""
n = 0
cols = ("source", "station_id", "dh_utc") + CHAMPS + ("recu_utc",)
sql = "INSERT OR IGNORE INTO mesures (%s) VALUES (%s)" % (",".join(cols), ",".join("?" * len(cols)))
for m in mesures:
vals = (source, str(station_id), m["dh_utc"]) + tuple(_num(m.get(c)) for c in CHAMPS) + (fmt_utc(recu),)
n += self.db.execute(sql, vals).rowcount
self.db.commit()
return n
def maj_station(self, source, station_id, **meta):
"""Met à jour les métadonnées d'une station (seuls les champs non vides remplacent l'existant)."""
champs = ("nom", "lat", "lon", "altitude", "licence", "lien", "extra")
meta = {k: v for k, v in meta.items() if k in champs and v not in (None, "")}
self.db.execute("INSERT OR IGNORE INTO stations (source, station_id) VALUES (?, ?)", (source, str(station_id)))
if meta:
self.db.execute("UPDATE stations SET %s WHERE source=? AND station_id=?" %
", ".join("%s=?" % k for k in meta), tuple(meta.values()) + (source, str(station_id)))
self.db.commit()
def stations(self):
return [dict(r) for r in self.db.execute("SELECT * FROM stations ORDER BY source, station_id")]
def derniere(self, source, station_id):
r = self.db.execute("SELECT * FROM mesures WHERE source=? AND station_id=? ORDER BY dh_utc DESC LIMIT 1",
(source, str(station_id))).fetchone()
return dict(r) if r else None
def serie(self, source, station_id, depuis):
return [dict(r) for r in self.db.execute(
"SELECT * FROM mesures WHERE source=? AND station_id=? AND dh_utc>=? ORDER BY dh_utc",
(source, str(station_id), fmt_utc(depuis)))]
def instants(self, source, station_id, n=30):
"""Les n derniers (dh_utc, recu_utc), du plus ancien au plus récent : sert à mesurer le pas réel."""
rows = self.db.execute("SELECT dh_utc, recu_utc FROM mesures WHERE source=? AND station_id=? "
"ORDER BY dh_utc DESC LIMIT ?", (source, str(station_id), n)).fetchall()
return [(r[0], r[1]) for r in reversed(rows)]
def purger(self, maintenant, jours=14):
limite = fmt_utc(maintenant - dt.timedelta(days=jours))
n = self.db.execute("DELETE FROM mesures WHERE dh_utc < ?", (limite,)).rowcount
self.db.commit()
return n
def compter(self, source=None):
if source:
return self.db.execute("SELECT COUNT(*) FROM mesures WHERE source=?", (source,)).fetchone()[0]
return self.db.execute("SELECT COUNT(*) FROM mesures").fetchone()[0]
def _num(v):
if v is None:
return None
if isinstance(v, (int, float)) and not isinstance(v, bool):
return float(v)
try:
s = str(v).strip().replace(",", ".")
if not s or s.upper() == "NULL":
return None
return float(s)
except ValueError:
return None