IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Séb.

Export SQL et noms de colonnes

Noter ce billet
par , 30/04/2022 à 12h59 (179 Affichages)
Vous avez des données à extraire massivement de votre base données, SELECT ... INTO OUTFILE est tout indiqué. La première déconvenue sera l'absence des noms de colonnes. Voici comment y remédier.

Travaillons sur une table cities, un extrait du Code Officiel Géographique :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
| id    | type | code  | name                    | tncc |
+-------+------+-------+-------------------------+------+
|     1 | COM  | 01001 | L'Abergement-Clémenciat |    5 |
|     2 | COM  | 01002 | L'Abergement-de-Varey   |    5 |
|     3 | COM  | 01004 | Ambérieu-en-Bugey       |    1 |
...
| 37901 | COM  | 97617 | Tsingoni                |    0 |
Si vous effectuez une requête comme :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
SELECT ALL id, type, code, name, tncc
FROM cities
INTO OUTFILE '/var/lib/mysql-files/cities.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

Vous obtiendrez le CSV suivant :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
"1","COM","01001","L'Abergement-Clémenciat","5"
"2","COM","01002","L'Abergement-de-Varey","5"
"3","COM","01004","Ambérieu-en-Bugey","1"
...
"37901","COM","97617","Tsingoni","0"
Et remarquerez que les colonnes ne sont pas nommées, ce qui est une perte d'information et peut poser des problèmes de compréhension. En effet, que représentent les derniers chiffres de chaque ligne ? Rien n'indique qu'il s'agit des TNCC (Type de Nom en Clair).

Nommer systématiquement les colonnes est une bonne pratique. Voici comment nommer les colonnes d'un extract :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
(SELECT ALL 'id', 'type', 'code', 'name', 'tncc')
UNION ALL
(SELECT ALL id, type, code, name, tncc FROM cities) -- Attention tout tri doit être effectué *dans* cette 2nde sous-requête
INTO OUTFILE '/var/lib/mysql-files/cities.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

La 1re sous-requête permet d'extraire des littéraux, ici des noms de colonnes. La 2nde sous-requête les données. Le résultat produit sera :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
"id","type","code","nom","tncc"
"1","COM","01001","L'Abergement-Clémenciat","5"
"2","COM","01002","L'Abergement-de-Varey","5"
"3","COM","01004","Ambérieu-en-Bugey","1"
...
"37901","COM","97617","Tsingoni","0"
Et voilà comment effectuer un extract SQL muni d'une en-tête

Envoyer le billet « Export SQL et noms de colonnes » dans le blog Viadeo Envoyer le billet « Export SQL et noms de colonnes » dans le blog Twitter Envoyer le billet « Export SQL et noms de colonnes » dans le blog Google Envoyer le billet « Export SQL et noms de colonnes » dans le blog Facebook Envoyer le billet « Export SQL et noms de colonnes » dans le blog Digg Envoyer le billet « Export SQL et noms de colonnes » dans le blog Delicious Envoyer le billet « Export SQL et noms de colonnes » dans le blog MySpace Envoyer le billet « Export SQL et noms de colonnes » dans le blog Yahoo

Tags: mysql, sql
Catégories
Programmation

Commentaires

  1. Avatar de sevyc64
    • |
    • permalink
    Petite précision pour cette astuce, on peut ne pas avoir tout à fait le résultat souhaité.

    En effet, certains SGBD ne retournent pas les blocs de lignes dans l'ordre des unions, mais dans l'ordre inverse, voire dans un ordre aléatoire.

    Personnellement, j'ai eu le cas avec SQL Server qui me retournait les blocs dans l'ordre inverse des unions (ça m'avait semblait systématique).
    De ce fait, la ligne qui doit servir de ligne d’entête de colonnes, issue de la première requête se retrouvait être la dernière ligne du résultat retourné.
  2. Avatar de Séb.
    • |
    • permalink
    Citation Envoyé par sevyc64
    j'ai eu le cas avec SQL Server qui me retournait les blocs dans l'ordre inverse des unions (ça m'avait semblait systématique).
    De ce fait, la ligne qui doit servir de ligne d’entête de colonnes, issue de la première requête se retrouvait être la dernière ligne du résultat retourné.
    Je ne connais pas MS SQL Server, mais ne serait-ce pas dû au ORDER BY appliqué sur le résultat du UNION et pas spécifiquement sur la data ?

    Ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT ...
    UNION
    SELECT ...
    ORDER BY ...
    Equivaut à :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    (SELECT ... UNION SELECT ...)
    ORDER BY ...
    D'où l'intérêt de spécifier les sous-requêtes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    (SELECT ...)
    UNION
    (SELECT ... ORDER BY ...)
    Pour être sûr que le résultat du UNION est trié comme on l'entend on peut ajouter une clef de tri :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    (SELECT 1 AS sort_key, ...)
    UNION
    (SELECT 2 AS sort_key, ...)
    ORDER BY sort_key ASC, ...