Référentiel articles criticité haute internal

Dataset ref_articles · responsable Jean Dupont (Méthodes), @jdupont · projet flender/dataplateforme/dataset/ref_articles

Environnement prod. OpenAPI : https://data-prod.flturbo.dev/ref_articles/. Les exemples utilisent les vraies colonnes ; remplacez VALEUR. Même code pour dev / staging / prod, seule l'URL de base change (DATA_API_URL).

`/ref_articles/articles`

import os
import requests

DATA_API_URL = os.environ.get("DATA_API_URL", "https://data-prod.flturbo.dev")   # dev / staging / prod : seule l'URL change
headers = {}

def lire_articles(filtres: dict | None = None, limit: int = 1000) -> list[dict]:
    """Lit ref_articles/articles page par page. Filtres PostgREST : {"ref_article": "eq.VALEUR"}."""
    rows, offset = [], 0
    while True:
        params = {"select": "ref_article,designation,famille,type_article", "order": "ref_article", "limit": limit, "offset": offset, **(filtres or {})}
        r = requests.get(f"{DATA_API_URL}/ref_articles/articles", params=params, headers=headers, timeout=30)
        r.raise_for_status()          # 401 : jeton absent/expiré, 403 : pas lecteur de ce dataset
        page = r.json()
        rows.extend(page)
        if len(page) < limit:
            return rows
        offset += limit

lignes = lire_articles({"ref_article": "eq.VALEUR"})
print(len(lignes), "lignes")

const DATA_API_URL = process.env.DATA_API_URL ?? "https://data-prod.flturbo.dev"; // dev / staging / prod

export async function lireArticles(filtres: Record<string, string> = {}, limit = 1000) {
  const rows: any[] = [];
  for (let offset = 0; ; offset += limit) {
    const params = new URLSearchParams({ select: "ref_article,designation,famille,type_article", order: "ref_article", limit: String(limit), offset: String(offset), ...filtres });
    const r = await fetch(`${DATA_API_URL}/ref_articles/articles?${params}`, {
      headers: {
        Accept: "application/json",
      },
    });
    if (!r.ok) throw new Error(`API ${r.status} : ${await r.text()}`); // 401 jeton, 403 pas lecteur
    const page = await r.json();
    rows.push(...page);
    if (page.length < limit) return rows;
  }
}

const lignes = await lireArticles({ ref_article: "eq.VALEUR" });
console.log(lignes.length, "lignes");

using System.Net.Http.Headers;
using System.Net.Http.Json;
using System.Text.Json;

// dotnet add package System.Net.Http.Json
public static class DataPlateforme
{
    static readonly string BaseUrl = Environment.GetEnvironmentVariable("DATA_API_URL") ?? "https://data-prod.flturbo.dev";

    public static async Task<List<JsonElement>> LireArticlesAsync(string filtre = "ref_article=eq.VALEUR", int limit = 1000)
    {
        using var http = new HttpClient();
        var rows = new List<JsonElement>();
        for (var offset = 0; ; offset += limit)
        {
            var url = $"{BaseUrl}/ref_articles/articles?select=ref_article,designation,famille,type_article&order=ref_article&limit={limit}&offset={offset}&{filtre}";
            using var resp = await http.GetAsync(url);
            resp.EnsureSuccessStatusCode();   // 401 : jeton absent/expiré, 403 : pas lecteur de ce dataset
            var page = await resp.Content.ReadFromJsonAsync<List<JsonElement>>() ?? new();
            rows.AddRange(page);
            if (page.Count < limit) return rows;
        }
    }
}

let
    Base = "https://data-prod.flturbo.dev",
    // Pagination : PostgREST renvoie au plus "limit" lignes par appel
    Page = (offset as number) =>
        Json.Document(Web.Contents(Base, [
            RelativePath = "ref_articles/articles",
            Query = [ select = "ref_article,designation,famille,type_article", order = "ref_article", limit = "1000", offset = Text.From(offset), ref_article = "eq.VALEUR" ]
        ])),
    Pages = List.Generate(() => [i = 0, p = Page(0)], each List.Count([p]) > 0, each [i = [i] + 1000, p = Page([i] + 1000)], each [p]),
    Lignes = List.Combine(Pages),
    Table = Table.FromRecords(Lignes)
in
    Table

# 20 premières lignes, colonnes choisies, filtre sur ref_article
curl "https://data-prod.flturbo.dev/ref_articles/articles?select=ref_article,designation,famille,type_article&ref_article=eq.VALEUR&order=ref_article&limit=20"

# total de lignes sans les lire
curl -I "https://data-prod.flturbo.dev/ref_articles/articles?select=ref_article" -H "Prefer: count=exact"

# export CSV
curl "https://data-prod.flturbo.dev/ref_articles/articles?select=ref_article,designation,famille,type_article" -H "Accept: text/csv" -o articles.csv

Même table en SQL (clé utilisateur) :

import os
import psycopg   # pip install "psycopg[binary]"

# Clé utilisateur : identifiant u_<login>, mot de passe remis une fois (portail > mes accès)
conn = psycopg.connect(host="sql.data.flturbo.dev", port=5432, dbname="data_prod", user="u_<login>", password=os.environ["DATA_SQL_PASSWORD"], sslmode="require")
with conn.cursor() as cur:
    cur.execute("SELECT * FROM ref_articles.articles LIMIT 20")
    for row in cur:
        print(row)

import pg from "pg";   // npm i pg

const client = new pg.Client({ host: "sql.data.flturbo.dev", port: 5432, database: "data_prod", user: "u_<login>", password: process.env.DATA_SQL_PASSWORD, ssl: { rejectUnauthorized: false } });
await client.connect();
const { rows } = await client.query("SELECT * FROM ref_articles.articles LIMIT 20");
console.log(rows);
await client.end();

using Npgsql;   // dotnet add package Npgsql

var cs = $"Host=sql.data.flturbo.dev;Port=5432;Database=data_prod;Username=u_<login>;Password={Environment.GetEnvironmentVariable("DATA_SQL_PASSWORD")};SSL Mode=Require;Trust Server Certificate=true";
await using var conn = new NpgsqlConnection(cs);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand("SELECT * FROM ref_articles.articles LIMIT 20", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) Console.WriteLine(reader[0]);

let
    // Excel / Power BI : Données > Obtenir des données > Base de données PostgreSQL (connecteur natif)
    // Serveur : sql.data.flturbo.dev:5432   Base : data_prod   Identifiants : u_<login> / mot de passe (clé utilisateur)
    Source = PostgreSQL.Database("sql.data.flturbo.dev:5432", "data_prod"),
    articles = Source{[Schema="ref_articles", Item="articles"]}[Data]
in
    articles

psql "postgresql://u_<login>@sql.data.flturbo.dev:5432/data_prod?sslmode=require" -c "SELECT * FROM ref_articles.articles LIMIT 20"