gbfs insert sql

fbfs.py

#!/usr/bin/env python3
"""
Script pour générer des INSERT SQL à partir d'un flux GBFS JSON.

Usage :
    python3 gbfs.py <url> <system_id> <id_meca> <id_electrical>

Exemple :
    python3 gbfs.py https://media.ilevia.fr/opendata/station_status.json v_lille bike NULL | psql service=work
    python3 gbfs.py https://api.cyclocity.fr/contracts/amiens/gbfs/v2/station_status.json velam mechanical electrical | psql service=work
    
Paramètres :
    <url>          : URL du JSON GBFS
    <system_id>    : identifiant du système de vélos (ex : "v_lille")
    <id_meca>      : identifiant du type vélo mécanique (ex : "bike")
    <id_electrical>: identifiant du type vélo électrique ou NULL si non utilisé
"""

"""
SQL 

CREATE TABLE public.velos_gbfs (
	id serial4 NOT NULL,
	station_id text NOT NULL,
	system_id text NOT NULL,
	num_bikes_available int4 NULL,
	num_bikes_disabled int4 NULL,
	num_docks_available int4 NULL,
	num_docks_disabled int4 NULL,
	is_installed bool NULL,
	is_renting bool NULL,
	is_returning bool NULL,
	last_reported timestamptz NOT NULL,
	nb_mecabike int4 NULL,
	nb_ebike int4 NULL,
	CONSTRAINT velos_gbfs_pkey PRIMARY KEY (id)
);


"""

import requests
import sys
from datetime import datetime, timezone


if len(sys.argv) < 5:
    print("Usage: python gbfs.py <url> <system_id> <id_meca> <id_electrical>")
    sys.exit(1)

url = sys.argv[1]
system_id = sys.argv[2]
id_meca = sys.argv[3]
id_ebike = sys.argv[4]

table = "public.velos_gbfs"

# --- Récupération du JSON ---

data = requests.get(url).json()




stations = []
for s in data["data"]["stations"]:
    meca_list = [v["count"] for v in s.get("vehicle_types_available", []) if v["vehicle_type_id"] == id_meca]
    ebike_list = [v["count"] for v in s.get("vehicle_types_available", []) if v["vehicle_type_id"] == id_ebike]
    station = {
        "station_id": s.get("station_id"),
        "num_bikes_available": s.get("num_bikes_available"),
        "num_bikes_disabled": s.get("num_bikes_disabled"),
        "num_docks_available": s.get("num_docks_available"),
        "num_docks_disabled": s.get("num_docks_disabled"),
        "is_installed": s.get("is_installed"),
        "is_renting": s.get("is_renting"),
        "is_returning": s.get("is_returning"),
        "last_reported": datetime.fromtimestamp(s["last_reported"], tz=timezone.utc).isoformat(),
        "system_id": system_id,
        "nb_mecabike": sum(meca_list) if meca_list else None,
        "nb_ebike": sum(ebike_list) if ebike_list else None
    }
    stations.append(station)

# --- Génération des commandes SQL ---
def sql_value(val):
    if val is None:
        return "NULL"
    if isinstance(val, bool):
        return "TRUE" if val else "FALSE"
    if isinstance(val, str):
        # Échapper les apostrophes simples
        return "'" + val.replace("'", "''") + "'"
    return str(val)

columns = ["station_id","num_bikes_available","num_bikes_disabled","num_docks_available","num_docks_disabled",
           "is_installed","is_renting","is_returning","last_reported","system_id","nb_mecabike","nb_ebike"]

for s in stations:
    values = ", ".join(sql_value(s[col]) for col in columns)
    print(f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({values});")