Data plateforme flturbo.dev

login non configuré

← Datasets

Avis de non-conformité criticité moyenne internal

Dataset anc · responsable Marie Martin (Qualité), @mmartin · projet flender/dataplateforme/dataset/anc

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

/anc/anc

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 = {"Authorization": f"Bearer {os.environ['DATA_API_TOKEN']}"}

def lire_anc(filtres: dict | None = None, limit: int = 1000) -> list[dict]:
    """Lit anc/anc page par page. Filtres PostgREST : {"numero": "eq.VALEUR"}."""
    rows, offset = [], 0
    while True:
        params = {"select": "numero,article__ref,date_ouverture,atelier", "order": "numero", "limit": limit, "offset": offset, **(filtres or {})}
        r = requests.get(f"{DATA_API_URL}/anc/anc", 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_anc({"numero": "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 lireAnc(filtres: Record<string, string> = {}, limit = 1000) {
  const rows: any[] = [];
  for (let offset = 0; ; offset += limit) {
    const params = new URLSearchParams({ select: "numero,article__ref,date_ouverture,atelier", order: "numero", limit: String(limit), offset: String(offset), ...filtres });
    const r = await fetch(`${DATA_API_URL}/anc/anc?${params}`, {
      headers: {
    Authorization: `Bearer ${process.env.DATA_API_TOKEN}`,
        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 lireAnc({ numero: "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>> LireAncAsync(string filtre = "numero=eq.VALEUR", int limit = 1000)
    {
        using var http = new HttpClient();
            http.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", Environment.GetEnvironmentVariable("DATA_API_TOKEN"));
        var rows = new List<JsonElement>();
        for (var offset = 0; ; offset += limit)
        {
            var url = $"{BaseUrl}/anc/anc?select=numero,article__ref,date_ouverture,atelier&order=numero&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
    // Jeton stocké dans un paramètre de classeur ou de rapport, jamais en dur dans une requête partagée
    Jeton = Excel.CurrentWorkbook(){[Name="DATA_API_TOKEN"]}[Content]{0}[Column1],
    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 = "anc/anc",
            Query = [ select = "numero,article__ref,date_ouverture,atelier", order = "numero", limit = "1000", offset = Text.From(offset), numero = "eq.VALEUR" ], Headers = [ Authorization = "Bearer " & Jeton ]
        ])),
    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 numero
curl "https://data-prod.flturbo.dev/anc/anc?select=numero,article__ref,date_ouverture,atelier&numero=eq.VALEUR&order=numero&limit=20" \
  -H "Authorization: Bearer $DATA_API_TOKEN"

# total de lignes sans les lire
curl -I "https://data-prod.flturbo.dev/anc/anc?select=numero" -H "Prefer: count=exact" \
  -H "Authorization: Bearer $DATA_API_TOKEN"

# export CSV
curl "https://data-prod.flturbo.dev/anc/anc?select=numero,article__ref,date_ouverture,atelier" -H "Accept: text/csv" \
  -H "Authorization: Bearer $DATA_API_TOKEN" -o anc.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 anc.anc 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 anc.anc 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 anc.anc 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"),
    anc = Source{[Schema="anc", Item="anc"]}[Data]
in
    anc

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

/anc/anc__lignes

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 = {"Authorization": f"Bearer {os.environ['DATA_API_TOKEN']}"}

def lire_anc__lignes(filtres: dict | None = None, limit: int = 1000) -> list[dict]:
    """Lit anc/anc__lignes page par page. Filtres PostgREST : {"defaut": "eq.VALEUR"}."""
    rows, offset = [], 0
    while True:
        params = {"select": "defaut,quantite", "order": "defaut", "limit": limit, "offset": offset, **(filtres or {})}
        r = requests.get(f"{DATA_API_URL}/anc/anc__lignes", 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_anc__lignes({"defaut": "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 lireAncLignes(filtres: Record<string, string> = {}, limit = 1000) {
  const rows: any[] = [];
  for (let offset = 0; ; offset += limit) {
    const params = new URLSearchParams({ select: "defaut,quantite", order: "defaut", limit: String(limit), offset: String(offset), ...filtres });
    const r = await fetch(`${DATA_API_URL}/anc/anc__lignes?${params}`, {
      headers: {
    Authorization: `Bearer ${process.env.DATA_API_TOKEN}`,
        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 lireAncLignes({ defaut: "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>> LireAncLignesAsync(string filtre = "defaut=eq.VALEUR", int limit = 1000)
    {
        using var http = new HttpClient();
            http.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", Environment.GetEnvironmentVariable("DATA_API_TOKEN"));
        var rows = new List<JsonElement>();
        for (var offset = 0; ; offset += limit)
        {
            var url = $"{BaseUrl}/anc/anc__lignes?select=defaut,quantite&order=defaut&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
    // Jeton stocké dans un paramètre de classeur ou de rapport, jamais en dur dans une requête partagée
    Jeton = Excel.CurrentWorkbook(){[Name="DATA_API_TOKEN"]}[Content]{0}[Column1],
    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 = "anc/anc__lignes",
            Query = [ select = "defaut,quantite", order = "defaut", limit = "1000", offset = Text.From(offset), defaut = "eq.VALEUR" ], Headers = [ Authorization = "Bearer " & Jeton ]
        ])),
    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 defaut
curl "https://data-prod.flturbo.dev/anc/anc__lignes?select=defaut,quantite&defaut=eq.VALEUR&order=defaut&limit=20" \
  -H "Authorization: Bearer $DATA_API_TOKEN"

# total de lignes sans les lire
curl -I "https://data-prod.flturbo.dev/anc/anc__lignes?select=defaut" -H "Prefer: count=exact" \
  -H "Authorization: Bearer $DATA_API_TOKEN"

# export CSV
curl "https://data-prod.flturbo.dev/anc/anc__lignes?select=defaut,quantite" -H "Accept: text/csv" \
  -H "Authorization: Bearer $DATA_API_TOKEN" -o anc__lignes.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 anc.anc__lignes 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 anc.anc__lignes 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 anc.anc__lignes 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"),
    anc__lignes = Source{[Schema="anc", Item="anc__lignes"]}[Data]
in
    anc__lignes

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