Laboratorio di Sistemi Informativi

Importazione ed esportazione dei dati

Inserire i dati con i comandi INSERT INTO ... VALUES o con un client grafico è abbastanza complesso, ed in genere adatto quando i valori da inserire non sono eccessivamente numerosi. Se i dati sono però già disponibili, magari in un altro formato, esistono dei modi per importarli direttamente, senza doverli riscrivere.

Analogamente, è possibile esportare in formato testo i dati presenti nelle tabelle, in modo da renderli utilizzabili da altri programmi.

Script SQL

1. DROP TABLE IF EXISTS <nometabella> : cancella la tabella <nometabella>, se esiste. A differenza di DROP TABLE <nometabella>, la variante con IF EXISTS  non da errore nel caso <nometabella> non esista. Per questo è particolarmente utile per script che ricreano uno schema da zero, senza che si sappia in anticipo se alcune tabelle esistono o meno.
2. INSERT INTO <nometabella> VALUES (<lista1>), (<lista2>), ... : il comando INSERT consente di inserire più righe nella tabella. Inserire più righe con un singolo comando comporta una maggiore velocità di esecuzione e un risparmio sul numero di caratteri da digitare sulla tastiera.
   

Si tratta di due estensioni non standard di MySQL, quindi è bene non utilizzarle all'interno di programmi che potrebbero essere portati a DBMS differenti.

1. dal prompt del monitor di MySQL eseguire il comando source <nomefile> (o il sinonimo \. <nomefile>);
   
2. dal prompt della shell di Linux, eseguire il programma monitor mysql con i seguenti parametri: mysql <nome_schema> < <nomefile>, dove <nome_schema> è il nome dello schema all'interno del quale si vuole eseguire lo script. Il simbolo < tra <nome_schema> e <nomefile> va inserito così com'è, e prende il nome di operatore di redirezione dell'input: dice al sistema operativo che l'input del programma mysql non deve provenire da tastiera, come è normalmente, ma dal file indicato.

   In aula informatizzata il comando deve essere ovviamente mysql -h goemon -p <nome_schema> < <nomefile>, dove <nome_schema> è il vostro numero di matricola. Nel seguito, daremo sempre per scontato l'uso delle opzioni -h e -p quando necessarie.

Dump degli schemi

Per mysqldump, come per mysql, occorre utilizzare le opzioni -h e -p quando si lavora in aula informatizzata.

Il carattere > prende il nome di operatore di redirezione dell'output. È il duale dell'operatore <. Indica al sistema operativo di non inviare l'output sulla finestra del terminale, ma su di un file. Il file che si è ottenuto può essere letto con il comando SOURCE da dentro il monitor di MySQL. Bisogna prima ricordarsi di selezionare lo schema in cui si vogliono inserire i dati con il comando USE. Alternativamente, dalla shell di Linux possiamo usare mysql <nome_schema> < <nome_file>.

Con la sintassi

mysqldump <nome_schema> --databases <db1>...<dbn>

si può effettuare il dump di più schemi contemporaneamente. In questo modo mysqldump genera anche i comandi CREATE DATABASE (che, se ricordate, è un sinonimo di CREATE SCHEMA) necessari per la creazione degli schemi ed i comandi USE. Il file generato può essere letto con il monitor di MySQL, sempre con il comando SOURCE, o dalla shell con mysql < <nomefile>.

Esportare i dati con mysqldump e rileggerli da un altra macchina è il modo migliore per trasferire dati da un sistema ad un altro, in quanto funziona indipendentemente dal tipo di sistema operativo o processore utilizzato nei due sistemi.

Redirezione dell'input e dell'output

Prima di andare avanti, facciamo notare brevemente che gli operatori di redirezione < e > non sono specifici di MySQL, ma si possono applicare a qualsiasi programma eseguibile dalla shell di Linux. Ad esempio, mentre normalmente il programma ls visualizza su schermo il contenuto di una directory, un comando come ls > catalogo non visualizza nulla, ma crea un file di nome catalogo il cui contenuto è l'elenco generato da ls.

Notare che alcune volte l'uso della redirezione cambia leggermente il funzionamento dei programmi. Ad esempio, l'output di ls e di ls > catalogo non è esattamente uguale. L'output di ls è a colori, e i file sono visualizzati su più colonne. Il file catalogo ottenuto da ls > catalogo non ha colori e presenta i file su di una sola colonna.

Di contro, l'output su schermo di mysqldump è esattamente identico a quello che si ottiene se lo si redirige su un file.

Dati in formato CSV

Il formato CSV e il comando LOAD DATA LOCAL INFILE

Supponiamo ora di voler aggiungere altri elementi alla tabella aerei, e di avere i dati in un file datiaerei.txt. Notare che nel file sono presenti gli stessi campi della tabella (eccetto id e commento): ogni campo è separato da un altro da una tabulazione (attenzione una tabulazione, non uno spazio), e ogni riga rappresenta una nuova istanza.

• il nome di file: datiaerei.txt
• la tabella dove inserire i dati: aerei
• le colonne che intendiamo inserire nella tabella (produttore,modello,dataimm,numposti);

• FIELDS ENCLOSED BY '<carattere>': per indicare che i valori possoni essere racchiusi tra una coppia di caratteri uguali;
  
• FIELDS TERMINATED BY '<carattere>': per indicare che i valori sono separati tra loro dal carattere specificato, piuttosto che dalla tabulazione;
  
• IGNORE <number> LINES: per ignorare le prime <number> righe.

+----+------------+------------------+------------+----------+---------+
| id | produttore | modello          | dataimm    | numposti | charter |
+----+------------+------------------+------------+----------+---------+
|  1 | boeing     | 747              | 2000-10-10 |      350 | n       |
|  2 | MDD        | Super80          | NULL       |     NULL | n       |
|  3 | boeing     | 747m             | 2000-01-01 |      200 | n       |
|  4 | boeing     | 747m             | 2000-01-01 |      200 | n       |
|  5 | airbus     | b200             | NULL       |     NULL | n       |
|  6 | xyz        | concorde         | 1980-07-03 |      110 | n       |
|  7 | xyz        | columbia shuttle | 1999-06-04 |        5 | n       |
|  8 | boeing     | B600             | 1920-02-23 |     NULL | n       |
+----+------------+------------------+------------+----------+---------+

Un file come datiaerei2.txt si dice essere in formato CSV (Comma Separated Values). In realtà, si usa il nome di formato CSV anche per un file come datiaerei.txt, nel quale i dati non sono separati da virgole ma da tabulazioni. In generale con formati CSV si intende un qualunque formato tabellare in cui ogni riga contiene una istanza, e gli attributi delle istanze sono separate da qualche carattere separatore (che può essere virgola, tabulazione, spazio, punto e virgola o altro).

Esempio: importazione dati da OpenOffice

• È possibile copiare i dati e incollarli in un file di testo, ad esempio dentro una finestra di gedit. Una volta salvato il file di testo, supponiamo col nome aerei.txt, si possono importare i dati in MySQL con
  
  LOAD DATA LOCAL INFILE 'aerei.txt' INTO TABLE aerei (produttore,modello,dataimm,numposti);
  
  
• È possibile salvare il foglio OpenOffice in formato "Text CSV". Il programma chiede il carattere da usare come delimitatore di campo e quello da utilizzare per racchiudere i campi di testo (di default rispettivamente la virgola e le virgolette):
  
  
  
  Se non si modificano i caratteri delimitatori e si utilizza il nome di file aerei.csv, si possono importare i dati in MySQL con il comando
  
  LOAD DATA LOCAL INFILE 'aerei.csv' INTO TABLE aerei FIELDS TERMINATED BY ',' ENCLOSED BY '"' (produttore,modello,dataimm,numposti);
  
  ATTENZIONE. Per essere sicuri che le date siano memorizzate nel file .CSV con il formato gradito a MySQL, accertarsi che esse vengano visualizzate sul foglio di calcolo in maniera appropriata, e selezionare l'opzione "Save as Rendered".

Esportazione di un file CSV

Veniamo ora al problema opposto: vogliamo spostare i dati presenti in una tabella in un foglio di calcolo, o in un altro software che non intepreta il linguaggio SQL ma accetta file in formato CSV. Come fare a generare il tipo di file richiesto? Utilizziamo di nuovo l'operatore < per la redirezione dell'input.

Supponiamo di voler esportare in formato CSV il contenuto della tabella aerei. Prepariamo uno script SQL contenente il comando SELECT * FROM AEREI; . Supponiamo che questo file si chiami comando.sql. Adesso dalla shell di Linux diamo il seguente comando (con l'aggiunta degli opportuni parametri di connessione):

mysql gamato < comando.sql

Il programma monitor di MySQL si collega allo schema gamato, esegue il comando SELECT * FROM aerei e visualizza il seguente output sullo schermo, ritornando immediatamente alla shell di Linux:

id	produttore	modello	dataimm	numposti	commento
1	boeing	747	2000-10-10	350	
2	MDD	Super80	NULL	NULL	prova
3	boeing	747m	2000-01-01	200	NULL
4	boeing	747m	2000-01-01	200	NULL
5	airbus	b200	NULL	NULL	NULL
6	xyz	concorde	1980-07-03	110	NULL
7	xyz	columbia shuttle	1999-06-04	5	NULL
8	boeing	B600	1920-02-23	NULL	NULL
9	cessna	b71	1920-02-12	2	NULL
10	cessna	b72	1930-02-02	4	NULL

Possiamo combinare la redirezione dell'input con quella dell'output e inviare il risultato ad un file, da utilizzare poi in OpenOffice, MySQL o in qualunque altro programma che legga i file CSV:

mysql gamato < comando.sql > out.csv

Scrivere un file di testo con il comando SQL da eseguire e poi invocare il programma mysql può essere un po' farraginoso. Fortunatamente si può fare tutto con la sola invocazione di mysql utilizzando l'opzione aggiuntiva -e. Questa consente di eseguire un comando SQL, specificato tra virgolette dopo -e, in maniera non interattiva. Ad esempio:

mysql gamato -e "SELECT * FROM aerei" > out.csv

ha esattamente lo stesso effetto del comando mysql gamato < comando.sql > out.csv visto precedentemente.

Se invece l'output di mysql con l'opzione -e non viene rediretto ma viene inviato normalmente sullo schermo, allora avremo questo risulato:

+----+------------+------------------+------------+----------+----------+
| id | produttore | modello          | dataimm    | numposti | commento |
+----+------------+------------------+------------+----------+----------+
|  1 | boeing     | 747              | 2000-10-10 |      350 |          | 
|  2 | MDD        | Super80          | NULL       |     NULL | prova    | 
|  3 | boeing     | 747m             | 2000-01-01 |      200 | NULL     | 
|  4 | boeing     | 747m             | 2000-01-01 |      200 | NULL     | 
|  5 | airbus     | b200             | NULL       |     NULL | NULL     | 
|  6 | xyz        | concorde         | 1980-07-03 |      110 | NULL     | 
|  7 | xyz        | columbia shuttle | 1999-06-04 |        5 | NULL     | 
|  8 | boeing     | B600             | 1920-02-23 |     NULL | NULL     | 
|  9 | cessna     | b71              | 1920-02-12 |        2 | NULL     | 
| 10 | cessna     | b72              | 1930-02-02 |        4 | NULL     | 
+----+------------+------------------+------------+----------+----------+

Le linee orizzontali e verticali aiutano un essere umano nella lettura del file ma non sono interpretate dai programmi che leggono il formato CSV. Questo non è comunque un problema perché, se l'output viene rediretto su un file come è stato fatto prima, allora cambia e diventa un file CSV standard.

Esportazione diretta dei file dati

Tutta quello che è scritto in questa sezione non è applicabile in aula informatizzata, perché la macchina dove risiede il server MySQL non è accessibile direttamente.

1. nella maggior parte dei casi, solo il super-utente ha il diritto di accedere alla directory /var/lib/mysql/<schema>.
2. prima di copiare i dati dal server origine è necessario che il processo server di MySQL venga disattivato, altrimenti si rischia di copiare dei file corrotti. Analogamente, prima di copiare i file nel server destinazione, è bene che anche qui il processo server venga disattivato.

Esercizi e Soluzioni

Esercizio 1

• aereo_id: l'aereo a cui il posto si riferisce
• numposto: il numero del posto
• classe: può essere business class, first class o turistica
• collocazione: finestrino, corridoio, interno