Looking for Computer Science & Information Technology online
courses ?
Check my new web site: https://www.yesik.it !
Dans cet article, nous allons voir comment il est possible de créer et peupler une base de données à partir de données brutes stockées dans un fichier texte au format CSV.
| Objectifs | A la fin de ce tutoriel, vous saurez:
|
|---|---|
| Prérequis |
|
| Moyens |
|
Sommaire
Le format CSV
Le format CSV (Comma-separated values) est un format classique d'échange de données tabulaires. La plupart des tableurs et des SGBD peuvent importer ou exporter des données sous ce format.
Un fichier CSV est simplement un fichier dans lequel les champs de chaque enregistrement sont séparés par une virgule (d'où le nom: CVS pour comma separated values). Dans la pratique, il est possible d'utiliser n'importe quel caractère comme séparateur de champ ou de ligne. Si traditionnellement les lignes sont séparées par un "retour à la ligne", il existe plus de variété pour les séparateurs de champs: point-virgule, tabulation, etc. L'essentiel étant qu'il n'y ait pas d'occurrence du caractère séparateur dans les données.
Dans l'exemple ci-dessous de fichier CSV, les lignes sont séparées par un "retour à la ligne" et les champs par un point-virgule (;):
9781590595336;DOM Scripting: Web Design with JavaScript and the Document Object Model;2617;Friends of ED;Dave Shea;Préface 9781590595336;DOM Scripting: Web Design with JavaScript and the Document Object Model;2617;Friends of ED;Jeremy Keith;Auteur 9781590598146;Microformats: Empowering Your Markup for Web 2.0;2617;Friends of ED;John Allsop;Auteur 9781932394757;PHP in Action: Objects, Design, Agility;2991;Manning Publications;Chris Shiflett;Auteur 9781932394757;PHP in Action: Objects, Design, Agility;2991;Manning Publications;Dagfinn Reiersol;Auteur 9781932394757;PHP in Action: Objects, Design, Agility;2991;Manning Publications;Marcus Baker;Auteur
Contexte
Nous imaginons dans ce tutoriel que nous participons au développement d'une application autour d'une base de données pour informatiser une librairie. Actuellement, l'ensemble des données est géré dans une feuille de calcul Excel. Les données de celles-ci ont été exportées au format CSV et enregistrées dans le fichier livres.csv.
Remarque:
En réalité, les données qui servent d'exemple pour ce tutoriel ont été extraites du site http://www.amazon.fr.
Ces données reprennent pour chaque livre:
- son isbn,
- son titre,
- son prix (en centimes),
- le nom de l'éditeur
- le nom de l'auteur
- le rôle de l'auteur
La notion de rôle de l'auteur est utilisée car le libraire souhaite faire la distinction entre les auteurs originaux, les traducteurs, les auteurs de préface, etc.
La partie de la base de données concernant ces informations a déjà été modélisée et peut se représenter par le diagramme UML suivant:
Travail préliminaire
Créer la base de données
La première étape va être de créer la base de données. Tout d'abord, lancez le client en mode texte de MySQL. Ensuite, à partir de l'invite de commande, nous allons utiliser les requêtes suivantes:
CREATE DATABASE `MaLibrairie` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `MaLibrairie`;
La commande CREATE DATABASE va créer une nouvelle base de données. Notez les clauses DEFAULT CHARACTER et COLLATE: elles indiquent à MySQL que le jeux de caractère et l'ordre de tri par défaut seront respectivement utf8 et utf8_general_ci (ordre de tri générique insensible à la casse). Le choix de l'encodage en utf8 n'est pas anodin: en effet, lors de l'export du fichier livres.csv, c'est ce même encodage qui a été utilisé: pour éviter tout problème, nous utilisons donc de l'utf8 partout.
La commande use indique ensuite simplement à MySQL que la base de donnée active sera MaLibrairie.
Importer les données brutes
L'import des données brutes va se faire à l'aide de la commande LOAD DATA. Celle-ci permet d'importer le contenu d'un fichier texte au format CVS dans une table existante ... ce qui implique que nous allons tout d'abord devoir créer cette table:
CREATE TABLE `Brut` ( `isbn` DECIMAL(13) NOT NULL, `titre` VARCHAR(255) NOT NULL, `prix` DECIMAL(6) NOT NULL, `editeur` VARCHAR(255) NOT NULL, `auteur` VARCHAR(255) NOT NULL, `role` VARCHAR(255) NOT NULL );
Vous le remarquez, cette table reprend dans l'ordre les champs enregistrés dans le fichier CSV. Vous remarquez également l'absence de clé: cette table ne nous servant que temporairement pour l'importation des données, elle n'en a tout simplement pas besoin.
Comme notre fichier est encodé en utf8, il faut aussi s'assurer que le client est bien configuré avec ce jeu de caractère: en effet, c'est le jeu de caractères du client qui est utilisé pour l'importation:
mysql> charset utf8 Charset changed
Vous pouvez vérifier à tout moment le jeu de caractère utilisé par le client MySQL en mode texte à l'aide de la commande status. Dans le résultat, il faut repérer la ligne Client characterset:
mysql> status
...
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
...
Passons maintenant à l'importation à proprement parler:
LOAD DATA LOCAL INFILE '/chemin/vers/fichier/livres.csv' INTO TABLE `Brut` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';
Piège:
Le fichier livres.csv a été créé en environnement Unix. Et dans le monde Unix/Linux, la fin de ligne est déterminée par la caractère '\n' (0x0A en hexadécimal).
Par contre, Windows utilise 2 caractères pour désigner la fin de ligne: '\r' suivi de '\n' (0x0D 0x0A en hexadécimal). Si vous importez des données crées sous Windows, il vous faudra certainement modifier la clause LINES TERMINATED BY en conséquence!
Extraire les données
Table des auteurs
La première table dans laquelle nous allons extraire des données est celle des auteurs. La technique que nous allons utiliser ici consiste tout d'abord à créer une table, puis à utiliser une requête INSERT ... SELECT pour peupler cette table.
La création de la table se fait comme d'habitude:
CREATE TABLE `Auteur` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
Remarquez au passage l'utilisation de l'attribut auto_increment pour le champ id: cela signifie que lors de l'insertion de données, si la valeur du champ id n'est pas donnée, MySQL le renseignera automatiquement avec la prochaine valeur libre dans cette colonne. Et ceci va être bien pratique dans très peu de temps!
Vous savez certainement faire une requête SELECT. Vous connaissez aussi peut-être la requête SELECT DISTINCT qui élimine les lignes identiques dans le résultat. Ainsi, la requête suivante extrait de la table Brute tous les auteurs en s'assurant de ne pas donner deux fois le même:
SELECT DISTINCT `Auteur` FROM `Brut`;
Ce que vous ne savez peut-être pas, par contre, c'est qu'il est possible d'utiliser le résultat d'une requête SELECT comme source de données pour une requête INSERT.
SQL
Contrairement à une idée commune, le S de SQL ne signifie pas standard mais structured. SQL était à l'origine un langage purement déclaratif, et dans ce contexte, le terme structuré signifie que les requêtes peuvent être emboîtées les unes dans les autres: les résultats des unes servant d'arguments pour les autres.
Cette possibilité de SQL qui est relativement mal connue, tout simplement parce qu'elle n'est pas correctement (voir pas du tout) implémentée par certains grands SGBD-R (dont MySQL dans ses versions antérieures).
Une requête INSERT ... SELECT commence comme une requête INSERT classique, mais au lieu de fournir les données à insérer dans une clause VALUES, celles-ci sont obtenues grâce à une requête SELECT:
INSERT INTO `Auteur` (`nom`) SELECT DISTINCT `Auteur` FROM `Brut`;
Et voilà, après cette requête la table Auteur est peuplé avec le nom des auteurs extraits de la table Brut. On peut facilement vérifier:
mysql> SELECT * FROM `Auteur`; +----+---------------------+ | id | nom | +----+---------------------+ | 1 | John Clement | | 2 | Murray Marks | | 3 | Michael Yuan | | 4 | Thomas Heute | | 5 | Cay Horstmann | ...
Au passage, remarquez comme l'auto_increment de la colonne id a permis d'attribuer automatiquement un identifiant unique à chaque auteur.
Table des éditeurs
Il est possible d'utiliser exactement la même technique que pour les auteurs pour peupler la table des éditeurs. Mais histoire de varier les plaisirs, nous allons présenter ici une autre solution, basée sur la requête CREATE TABLE ... SELECT.
Nous avons donc vu précédemment qu'il était possible d'insérer des données à partir du résultat d'une requête SELECT. Et bien, il est aussi possible de tout simplement créer une table à partir du résultat d'une telle requête.
La syntaxe est relativement intuitive:
CREATE TABLE `Editeur` SELECT DISTINCT `Editeur` AS `nom` FROM `Brut`;
Il est possible de facilement vérifier la structure de la table créée (requête EXPLAIN) et le contenu de cette table (requête SELECT):
mysql> EXPLAIN `Editeur`; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | nom | varchar(255) | NO | | | | +---------+--------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> SELECT * FROM `Editeur`; +-----------------------------+ | nom | +-----------------------------+ | Elsevier Academic Press | | Prentice Hall PTR | | Addison-Wesley Professional | | Peachpit Press | | Houghton Mifflin Company | ...
Vous remarquez au passage que la colonne de la table créée a hérité du type et des attributs de la colonne extraite par la requête SELECT. Quand à son nom, il provient de la clause AS qui permet de renommer une colonne dans une requête SELECT.
Vous notez également que la table crée ne correspond pas exactement au schéma défini pour cette table. Quelques retouches sont nécessaires:
- La colonne nom doit être unique (clé alternative: pas de doublons);
- Il manque la colonne des id;
- Cette dernière qui doit aussi servir de clé primaire.
tout ceci se corrige à l'aide d'une requête ALTER TABLE:
ALTER TABLE `Editeur` ADD UNIQUE INDEX (`nom`), ADD COLUMN `id` int UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`);
A nouveau, on peut facilement vérifier que les modifications ont bien été prises en compte:
mysql> DESCRIBE `Editeur`; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | nom | varchar(255) | NO | UNI | | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM `Editeur`; +-----------------------------+----+ | nom | id | +-----------------------------+----+ | Elsevier Academic Press | 1 | | Prentice Hall PTR | 2 | | Addison-Wesley Professional | 3 | | Peachpit Press | 4 | ...
Table des livres
Pour les éditeurs et les auteurs, l'extraction des données a été relativement simple car ces deux tables ne contiennent pas de clé étrangère. Les choses sont différentes avec la table des livres qui contient une clé étrangère vers la table des éditeurs. Tout ceci doit certainement vous laisser entrevoir l'utilisation d'une jointure.
D'après le schéma de la base de données, la table des livres doit contenir les informations suivantes:
- Le titre, à extraire de la table Brut
- Le prix, à extraire de la table Brut
- L'isbn, à extraire de la table Brut
- La quantité en stock, initialisée à 0 lors de la création de la base de données
- L'id_editeur, à extraire de la table Editeur
C'est l'extraction de ce dernier champ, l'id_editeur, qui va nécessiter une jointure. Quand à la création à proprement parler de la table, nous allons la faire avec une requête CREATE TABLE ... SELECT qui nous épargnera la définition des colonnes.
Procédons par étapes: si nous ne voulions que les données qui peuvent être extraites de la table Brut la requête suivante suffirait:
mysql> SELECT
-> `titre`, `prix`, `isbn`
-> FROM `Brut`;
+-------------------------------------------------+-------+---------------+
| titre | prix | isbn |
+-------------------------------------------------+-------+---------------+
| Computer-Graphic Facial Reconstruction | 10300 | 9780124730519 |
| Computer-Graphic Facial Reconstruction | 10300 | 9780124730519 |
| JBoss Seam: Simplicity and Power Beyond Java EE | 2991 | 9780131347960 |
| JBoss Seam: Simplicity and Power Beyond Java EE | 2991 | 9780131347960 |
...
Immédiatement, un problème apparaît: les mêmes lignes apparaissent plusieurs fois. C'est dû à notre fichier CVS original qui contenait plusieurs lignes pour le même livre, en particulier lorsqu'il y avait plusieurs auteurs. Un SELECT DISTINCT devrait faire l'affaire:
mysql> SELECT DISTINCT
-> `titre`, `prix`, `isbn`
-> FROM `Brut`;
+-------------------------------------------------+-------+---------------+
| titre | prix | isbn |
+-------------------------------------------------+-------+---------------+
| Computer-Graphic Facial Reconstruction | 10300 | 9780124730519 |
| JBoss Seam: Simplicity and Power Beyond Java EE | 2991 | 9780131347960 |
...
Maintenant que nous arrivons à extraire les données de la table Brut sans doublon, ajoutons la quantité en stock. C'est une constante, or il est possible d'ajouter une constante à la liste des champs à extraire dans une requête SELECT:
mysql> SELECT DISTINCT
-> `titre`, `prix`, `isbn`,
-> 0 AS `stock`
-> FROM `Brut`;
+-------------------------------------------------+-------+---------------+-------+
| titre | prix | isbn | stock |
+-------------------------------------------------+-------+---------------+-------+
| Computer-Graphic Facial Reconstruction | 10300 | 9780124730519 | 0 |
| JBoss Seam: Simplicity and Power Beyond Java EE | 2991 | 9780131347960 | 0 |
...
Remarquez à nouveau l'utilisation de AS pour donner un nom à la colonne contenant la constante 0 (zéro).
Il nous manque toujours l'id_editeur. Une jointure entre les tables Brut et Editeur nous permet de récupérer cette information:
mysql> SELECT DISTINCT
-> `Brut`.`titre`, `Brut`.`prix`, `Brut`.`isbn`,
-> 0 AS `stock`,
-> `Editeur`.`id` AS `id_editeur`
-> FROM `Brut`, `Editeur`
-> WHERE `Brut`.`editeur` = `Editeur`.`nom`;
+-------------------------------------------------------------------------+-------+---------------+-------+------------+
| titre | prix | isbn | stock | id_editeur |
+-------------------------------------------------------------------------+-------+---------------+-------+------------+
| Computer-Graphic Facial Reconstruction | 10300 | 9780124730519 | 0 | 1 |
| JBoss Seam: Simplicity and Power Beyond Java EE | 2991 | 9780131347960 | 0 | 2 |
...
Et voilà, nous avons toutes les informations. Ne reste plus qu'à utiliser le résultat de ce SELECT pour créer notre table. A final, la requête complète est:
CREATE TABLE `Livre` SELECT DISTINCT `Brut`.`titre`, `Brut`.`prix`, `Brut`.`isbn`, 0 AS `stock`, `Editeur`.`id` AS `id_editeur` FROM `Brut`, `Editeur` WHERE `Brut`.`editeur` = `Editeur`.`nom`;
Nous pouvons vérifier la table créee:
mysql> DESCRIBE `Livre`; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | titre | varchar(255) | NO | | | | | prix | decimal(6,0) | NO | | | | | isbn | decimal(13,0) | NO | | | | | stock | int(1) | NO | | 0 | | | id_editeur | int(10) unsigned | NO | | 0 | | +------------+------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
Vous le constatez, plusieurs choses ne sont pas en accord avec le schéma prévu pour cette table:
- Le type de la colonne stock n'est pas bon;
- D'après le schéma, un livre peut ne pas avoir d'éditeur (relation 0..*), or la colonne id_editeur ne peut pas être nulle;
- Il manque la clé primaire!
Tout ceci se corrige à l'aide de la requête ALTER TABLE:
ALTER TABLE `Livre` CHANGE COLUMN `stock` `stock` smallint UNSIGNED NOT NULL, CHANGE COLUMN `id_editeur` `id_editeur` int(10) UNSIGNED NULL DEFAULT NULL, ADD PRIMARY KEY (`isbn`);
Table d'association livre-auteur
Le principe appliqué à la table des livres est également valide pour cette table d'association. Le rôle et l'isbn peuvent être extraits de la table Brut, et quand à l'id_auteur, ce champ nécessite une jointure avec la table auteur.
Cette fois, nous allons utiliser la méthode qui consiste à d'abord créer la table (CREATE TABLE) puis ensuite à insérer les données (INSERT ... SELECT). Créons donc tout d'abord la table:
CREATE TABLE `Livre-Auteur` ( `isbn` decimal(13) NOT NULL, `id_auteur` int(10) UNSIGNED NOT NULL, `role` varchar(255) NOT NULL DEFAULT 'Auteur', PRIMARY KEY (`isbn`,`id_auteur`,`role`) );
La population de la table peut maintenant se faire:
INSERT INTO `Livre-Auteur` (`isbn`, `role`, `id_auteur`) SELECT DISTINCT `Brut`.`isbn`, `Brut`.`role`, `Auteur`.`id` FROM `Brut`, `Auteur` WHERE `Auteur`.`nom` = `Brut`.`auteur`;
Histoire de se rassurer, faisons tout de même un petit SELECT pour vérifier que l'on retrouve bien nos données:
mysql> SELECT `Livre`.`titre`, `Auteur`.`nom`, `Editeur`.`nom`, `Livre-Auteur`.`role`
-> FROM `Livre`, `Editeur`, `Livre-Auteur`, `Auteur`
-> WHERE `Livre`.`id_editeur` = `Editeur`.`id`
-> AND `Livre`.`isbn` = `Livre-Auteur`.`isbn`
-> AND `Livre-Auteur`.`id_auteur` = `Auteur`.`id`
-> AND `Livre`.`titre` LIKE 'Groovy%';
+------------------+-------------------+----------------------+----------+
| titre | nom | nom | role |
+------------------+-------------------+----------------------+----------+
| Groovy in Action | Andrew Glover | Manning Publications | Auteur |
| Groovy in Action | Dierk Koenig | Manning Publications | Auteur |
| Groovy in Action | Guillaume Laforge | Manning Publications | Auteur |
| Groovy in Action | James Gosling | Manning Publications | Préface |
| Groovy in Action | Jon Skeet | Manning Publications | Auteur |
| Groovy in Action | Paul King | Manning Publications | Auteur |
+------------------+-------------------+----------------------+----------+
6 rows in set (0.00 sec)
Vérifications
Dans la section précédente, nous avons terminé par une requête pour nous rassurer. Néanmoins, une vérification plus formelle est nécessaire pour s'assurer que toutes les données de la table brute ont bien été importées dans notre base.
Nous allons procéder en deux temps. Tout d'abord, nous allons nous assurer que l'on peut retrouver autant de données qu'au départ dans notre base:
mysql> SELECT COUNT(*) FROM (SELECT DISTINCT * FROM `Brut`) AS BrutUnique;
+----------+
| COUNT(*) |
+----------+
| 61 |
+----------+
mysql> SELECT COUNT(*) FROM `Livre`, `Editeur`, `Livre-Auteur`, `Auteur`
-> WHERE `Livre`.`id_editeur` = `Editeur`.`id`
-> AND `Livre`.`isbn` = `Livre-Auteur`.`isbn`
-> AND `Livre-Auteur`.`id_auteur` = `Auteur`.`id`;
+----------+
| COUNT(*) |
+----------+
| 61 |
+----------+
Ok: les deux résultats sont identiques. Reste à vérifier que ce sont bien les mêmes lignes dans les deux cas. Comme à ce stade nous avons relativement confiance, nous allons tout simplement supprimer de la table Brut les enregistrements qui peuvent être reconstitués à partir de notre base de données. La requête correspondante est un peu complexe et utilise une sous-requête SELECT dans une requête DELETE. La voici:
DELETE FROM `Brut` WHERE (`isbn`, `titre`, `prix`, `editeur`, `auteur`, `role`) IN ( SELECT `Livre`.`isbn`, `Livre`.`titre`, `Livre`.`prix`, `Editeur`.`nom`, `Auteur`.`nom`, `Livre-Auteur`.`role` FROM `Livre`, `Editeur`, `Auteur`, `Livre-Auteur` WHERE `Livre`.`id_editeur` = `Editeur`.`id` AND `Livre`.`isbn` = `Livre-Auteur`.`isbn` AND `Livre-Auteur`.`id_auteur` = `Auteur`.`id` );
Au départ, nous savions que dans la table des données brutes il y avait autant d'enregistrements distincts que peut en reconstituer la jointure. Donc, après la suppression des lignes identiques, s'il reste encore des entrées dans la table des données brutes, c'est que:
- soit certaines données n'ont pas pu être reconstituées,
- soit que notre jointure a reconstitué des données qui n'étaient pas dans la table originale.
C'est maintenant le moment de vérité:
mysql> SELECT count(*) FROM `Brut`; +----------+ | count(*) | +----------+ | 0 | +----------+
Toutes les données récupérées ont bien été incorporées dans notre base de données. La table des données brutes ne sert plus à rien, et la toute dernière étape va donc être de la faire disparaître de la base de données:
DROP TABLE `Brut`;
Remarque:
Juste une dernière remarque: nous avons validé le fait que la base contient les mêmes données que le fichier CSV original. Ceci ne signifie pas que la base est parfaitement valide. En effet, rien ne garantit que ces données initiales étaient tout à fait cohérentes.
Par exemple, vous pourrez constater que le nom de l'auteur des aventures d'un certain jeune sorcier est orthographié de différentes manières, ce qui a eu pour conséquence de créer dans notre base 4 auteurs différents alors qu'il s'agit de toute évidence du même:
mysql> SELECT * FROM `Auteur` WHERE `nom`LIKE '%Rowling%'; +----+---------------+ | id | nom | +----+---------------+ | 19 | J. K. Rowling | | 20 | J.K. Rowling | | 21 | J.K Rowling | | 22 | J-K Rowling | +----+---------------+
C'est le genre de problèmes que l'on rencontre avec les bases qui ont déjà un certain vécu ou lorsque des données ont été importées à partir de plusieurs sources. Quelques requêtes UPDATE et DELETE (laissées à titre d'exercice) devraient suffire pour corriger cela...
Conclusion
Vous avez vu dans cet article qu'il est possible d'utiliser des requêtes SQL pour déplacer des données de tables en tables. Vous avez pu également constater que ces opérations nécessitent des requêtes assez impressionnantes car elles mettent en jeu des sous-requêtes.
Néanmoins, et malgré cette complexité, il doit vous apparaître très clairement que la maîtrise de ces manipulations est importante en situation réelle: en effet, s'il est envisageable de ressaisir ou de faire du "copier-coller" quand la base ne contient que quelques dizaines d'enregistrements répartis en deux ou trois tables, c'est une autre affaire quand on parle en milliers ou dizaines de milliers de lignes et en dizaines de tables!
Enfin, et au delà de la simple importation de données ou de la migration d'un modèle de base de données à un autre, avoir la possibilité d'utiliser ces quelques requêtes pour modifier le schéma d'une base existante ouvre aussi la possibilité au développeur d'adopter approche évolutive: la base de données n'est plus figée et elle n'a donc pas à être entièrement et définitivement définie dans les phases préliminaires du projet. Au contraire, elle peut être modifiée au fur et à mesure des nouveaux besoins ou de l'évolution du projet. Et tout cela sans craindre de perte de données. Ainsi grâce à ce genre d'outil, le développeur de base de données peut avoir une approche agile de son travail.
