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>
108 lignes
4.6 KiB
Python
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
|