Python: importare CSV in SQLite

Premessa

Dopo aver:

1. Introdotto il sistema di database SQLite
2. Visualizzato alcuni comandi a riga di comando per la gestione di questo sistema
   
3. Approfondito la riga di comando

Vediamo come interagire con python, CSV e SQLite

Importazione di un file csv con python in sqlite

Nel seguente esempio ci connettiamo ad un database sqlite (se non esiste viene creato) e creiamo una tabella.
Successivamente andiamo a leggere un file CSV (che abbia come separatore il ";") e lo importiamo nella tabella appena creata.
Per facilità non viene fatto alcun controllo.

import csv
import sqlite3

con = sqlite3.Connection('fileprova.sqlite')
cur = con.cursor()
cur.execute('CREATE TABLE "nominativi" ("Cognome" varchar(20), "Nome" varchar(20));')

f = open('nominativi.csv')
csv_reader = csv.reader(f, delimiter=';')

cur.executemany('INSERT INTO nominativi VALUES (?, ?)', csv_reader)
cur.close()
con.commit()
con.close()
f.close()

SQLite da riga di comando (2)

Premessa

Dopo una veloce carrellata dei comandi di SQLite visti nell'articolo precedente denominato SQLite da riga di comando (1), in questo articolo andremo un pochino più nei dettagli sebbene non è scopo di questo articolo, essere una guida al linguaggio SQL..

Creiamo/modifichiamo un database

Dopo aver scaricato e scompattato SQLite, la prima cosa da fare è modificare un database o, se questo non esiste, dobbiamo crearlo:

sqlite3 prova.db

A questo punto ci verrà presentata la shell già vista nel precedente articolo. Per essere sicuri di lavorare sul database sopra citato possiamo digitare il comando .database con relativo output in rosso.

sqlite- .database

seq name file
--- ----- -------------------
0 main j:\sqlite\prova.db

Creiamo una tabella

Come ho già detto, dato che si parla di comandi SQL, rimando ad una guida più esaustiva. In questa sede si vuol solo dare una idea di massima. La prima cosa da fare, però, è proprio la costruzione di una tabella dove immagazzinare i nostri dati. A titolo di esempio, pensiamo ad un elenco di prodotti.

sqlite- CREATE TABLE Prodotti (IDProdotto INTEGER PRIMARY KEY, Colore varchar(25), Prezzo double);

Il programma non ci restituirà alcun output che significa che tutto è andato a buon fine.
Quando un numero intero (integer) è dichiarato chiave primaria come nell'esempio, sqlite lo identifica come autoincrementale.

Viste ed indici

Le viste sono come tabelle virtuali. Permettono di vedere le singole tabelle o più tabelle in join, con criteri differenti tipo "due campi di A, 1 campo di B e 3 di C come se fossero un'unica tabella".
Gli indici, al contrario, sono dei campi privilegiati. Essi, se interrogati, hanno dei tempi di risposta decisamente minori.
In altre parole se chiedete tutti gli abitanti di una città e il campo città è indicizzato, la ricerca su vasta scala sarà decisamente più veloce.
La differenza non si nota con qualche centinaio di record ma con migliaia o milioni di record...
Vediamo un esempio di vista e di indice:

sqlite- CREATE VIEW vistaRosso AS select Colore, Prezzo from Prodotti where Colore="Rosso";

sqlite- CREATE INDEX indiceProva on Prodotti (IDProdotto);

Elenco tabelle, viste, indici, schema

Può essere necessario "ricordarsi" la lista degli oggetti elencati nel sottotitolo. Di seguito i comandi con i relativi output in rosso. Una nota di riguardo allo schema: è il termine con il quale SQLite indica gli schemi di creazione degli oggetti come tabelle, viste.

sqlite- .tables
Prodotti

sqlite- .tables P%
Prodotti

sqlite- .indices
indiceProva

sqlite- .indices Prodotti
indiceProva

sqlite- .schema
CREATE TABLE Prodotti (IDProdotto INTEGER PRIMARY KEY, Colore varchar(25), Prezzo double);
CREATE VIEW vistaRosso AS select Colore, Prezzo from Prodotti where Colore="Rosso";
CREATE INDEX indiceProva on Prodotti (IDProdotto);

Inserimento record

A questo punto è arrivato il momento di inserire un po' di record.
Nel precedente articolo ho già fatto vedere come si fa ed ho anche evidenziato l'opportunità di importare dei files di testo (CSV) creati in Excel o OpenOffice Calc. Rimando quindi all'articolo.

Visualizzare i dati

La parte più affascinante è senza dubbio l'interrogazione del database. Anche qui rimando ad un manuale più esaustivo per la sintassi del comando SELECT nel linguaggio SQL.

sqlite- SELECT * FROM Prodotti;
1|Rosso|44.55
2|Rosso|33.33
3|Giallo|34.33

Eventualmente si può rendere più gradevole l'ouput si possono utilizzare delle opzioni come mode e/o headers. Personlamente, prima del select, consiglio le seguenti opzioni:

sqlite- .headers on
sqlite- .mode column
sqlite- SELECT * FROM Prodotti;

IDProdotto Colore Prezzo
----------- ------ -------
1 Rosso 44.55
2 Rosso 33.33
3 Giallo 34.33

Esportare/Importare i dati

Una possibilità molto interessante è la possibilità di esportare il database o anche solo una tabella in formati differenti.

sqlite- .output fileoutput.sql
sqlite- .dump
sqlite- .output stdout

In questo modo verrà creato un file con le istruzioni SQL per la creazione delle tabelle, inserimento dati.
Tale file può quindi essere un backup a tutti gli effetti, importabile in un secondo momento come segue:

sqlite- .read fileoutput.sql

SQLite da shell di sistema

La feature più interessante di tutte, a mio avviso, è la possibilità di dare i medesimi comandi non da shell interattiva ma da shell di sistema.
Questo ci da modo di creare degli script di sistema (file.bat in windows, script in linux e MacOS) in maniera estremamente agevole e veloce.
Non mi dilungherò troppo ma mi limiterò a fare tre esempi:

1) Selezione: sqlite3 -header -column prova.db 'select * from Prodotti;'
2) Visionare lo schema: sqlite3 prova.db '.schema'
3) Inserire un record: sqlite3 prova.db 'insert into Prodotti values (NULL, "Giallo", 33.55)
4) Fare il backup: sqlite3 prova.db '.dump' > fileoupt.sql

Conclusioni

SQLite è, senza dubbio, un sistema di database molto interessante; per degli approfondimenti rimando ad un mio precedente articolo: sqlite, libreria sql in 200kb.
Personalmente lo reputo un alleato estremamente potente ed utile nella vita "informatica" di tutti i giorni come memorizzatore di log ma anche come aiuto nella operatività giornaliera.
Ovviamente non va bene se si intende utilizzarlo come sistema multiutente ma saprà donare moltissime soddisfazioni in tutti quei contesti monoutente dove è richiesta rapidità, semplicità d'uso e poco ingombro.

SQLite da riga di comando (1)

La seconda parte qui: SQLite da riga di comando (2).

Premessa

Come visto in unhttp://www.blogger.com/img/blank.gif precedente articolo, sqlite è un piccolo sistema di database embedded che è stato scelto da molti applicativi per le sue caratteristiche di leggerezza e duttilità come sistema di archiviazione delle informazioni. Giusto per fare qualche nome cito il browser Firefox e il visualizzatore di immagini F-Spot .
SQLite ha diverse interfacce grafiche e via web (scritte in php) per la gestione ma, in questo articolo, vediamo come utilizzarlo da riga di comando.
Personalmente trovo questa modalità estremamente comoda perchè permette di fare degli script in maniera estremamente rapida.
Dopo averlo scaricato dalla pagina del download ed averlo scompattato è pronto per essere utilizzato.
Su windows si può entrare nel prompt del MSDOS (Start->Esegui... digitare cmd e premere invio) mentre su Linux si entra in una shell come gterm o xterm.
A questo punto dobbiamo entrare nella cartella nella quale si è scompattato sqlite e digitando sqlite3 seguito da un invio si avvia la shell interattiva del programma come si può vedere nella immagine sopra.
Vediamo alcuni comandi...

Comandi per iniziare sulla shell interattiva

I comandi che iniziano con il punto sono propri di sqlite e non del linguaggio SQL. Tramite questi comandi si può dialogare con il sistema. Ad esempio digitando:

.help

si entra nell'help del programma e si vede la lista dei comandi come nella schermata riportata sopra.

Supponiamo di voler fare una piccola rubrica personale.
A questo proposito apriamo Excel o OpenOffice Calc e sulla cella A1 del Foglio1 scriviamo "Cognome", nella cella B1 "Nome", nella cella C1 "Telefono".
Nelle celle sottostanti si scrivano una serie di cognomi, nomi, telefoni.
A questo punto si salvi il file in formato CSV (è necessario, nella finestra del salvataggio file scegliere come tipo di file il formato CSV) nella medesima cartella nella quale c'è l'eseguibile di sqlite.
La nostra base dati sarà pronta per essere importata.

Per poter interagire con il programma è bene creare, prima di tutto una tabella.
Sulla shell, dunque, si digiti il comando:

CREATE TABLE Nominativi (IDNominativo INTEGER PRIMARY KEY, Cognome VARCHAR(30), Nome VARCHAR(30), Telefono VARCHAR(15));

In questo modo abbiamo creato la tabella che conterrà i nostri dati.
Non ci resta che inserire alcuni dati con l'apposita istruzione:

INSERT INTO Nominativi (Cognome, Nome, Telefono) VALUES ('Rossi','Luca','333444555');

Ovviamente le istruzioni di INSERT possono essere molte e consecutive. Se poi vogliamo importare la tabella creata precedentemente con Excel o Calc possiamo fare come segue:

• Prima di tutto si imposta il separatore dei campi nel file CSV. Personalmente apprezzo maggiormente il ";" che viene scelto di default anche da Excel e OpenOffice. Al contrario gli americani preferiscono la virgola. Ad ogni buon conto meglio definire prima il simbolo di separazione
• Importare il file csv

.separator ";"
.import file.csv tabella

L'effetto di questa operazione sarà l'aggiunta dei nominativi precedentemente inseriti e salvati in CSV nella tabella.

Per salvare il proprio lavoro:

.backup database.sqlite

NB: questa piccola guida non vuole essere una completa guida esplicativa dei comandi.
Seguirà comunque a breve, un ulteriore approfondimento a questo argomento

La seconda parte qui: SQLite da riga di comando (2).

SQLite, libreria SQL in 200kb

Premessa

Qualche tempo fa, sempre su questo blog, ho scritto un articolo sui sistemi di database.
Tra i software citati, nel paragrafo denominato "librerie di database" ho fatto riferimento a sqlite.
L'articolo di oggi vuole approfondire la conoscenza di questo eccellente sistema ed in particolare fare una veloce panoramica che possa far venir voglia di utilizzarlo.

Cosa è sqlite e quando usarlo

SQLite è una libreria che permette di gestire dei database anche molto corposi (fino a 2Tb circa!!!) con molti accessi contemporanei in lettura in maniera rapida.
Esso non necessita di alcuna installazione (basta scaricarlo e si può iniziare ad usarlo!!) e non avvia alcun servizio.
Nonostante questo, può essere utilizzato sia in ambiente Linux, Windows e Mac, gestisce le transazioni, l'utilizzo del linguaggio SQL, delle viste e dei triggers.

Nella prima pagina del sito di riferimento, la cosa che colpisce subito sono gli sponsor che spingono all'uso di questo sistema. Sono aziende del calibro di Oracle, Symbian, Mozilla, Bloomberg e Adobe!

La licenza è decisamente molto aperta e permette la libera copia, modifica, pubblicazione, utilizzo e rivendita sia del codice sia della compilazione per qualsiasi utilizzo commerciale o meno.

SQLite crea un file dal quale legge e scrive direttamente i dati senza creare thread separati gestendo le code in maniera estremamente efficiente.
Questo permette di creare un database su un singolo file, portarlo in giro con una chiavetta o spedirlo via posta ad un amico in spazi estremamente contenuti.
Le dimensioni della libreria, infatti, variano dai 100kb ai 300kb (a seconda del sistema) e la memorizzazione dei dati è davvero contenuta.

Per questi motivi SQLite è particolarmente utile nell'utilizzo all'interno di cellulari, lettori MP3 e sistemi embedded in generale. Si presta ad immagazzinare i dati per siti web, analisi di dati e gestione di demo o piccoli database personali o aziendali.
Al contrario non può essere utilizzato per database che richiedono alti carichi di lettura/scrittura, basi di dati superiori ai 2Tb (!!) o applicazioni che necessitano della tecnologia client/server.

Interfaccia per l'uso di SQLite

SQLite non è dotato nativamente di interfaccia grafica per la gestione dei dati.
Esso fornisce una shell interattiva stile prompt di MSDOS dove è possibile inserire i comandi SQL come in questo esempio o dei comandi propri per l'impaginazione dei dati.
Intorno a questo sistema, però, sono sorte moltissime interfacce di terze parti, alcune opensource, altre proprietarie.
In una pagina del sito di riferimento c'è una lista completa di software tra i quali si può scegliere.
Personalmente utilizzo SQLiteAdministrator e SQLiteManager anche se il mio preferito è un plugin per Firefox SQLite Manager AddOn.
Tutti questi software permettono di gestire i dati di sqlite in maniera grafica se proprio non ci si vuole cimentare con la shell ed il linguaggio SQL.

Api e linguaggi di programmazione

La cosa più impressionante di sqlite è la quantità di linguaggi di programmazione che mettono a disposizione la possibilità di gestire un database con questo sistema.
Python, C++, Java, php, perl, .NET sono solo alcuni di questi.
Si può trovare l'elenco completo in questa pagina: SQLite Wrappers.
Come se non bastasse, sqlite mette a disposizione una shell non interattiva.
Tramite il proprio eseguibile, ifnatti, è possibile lanciare dei comandi anche dal prompt di MSDOS o dalla shell di Linux che creano tabelle, database, inseriscono dati e quant'altro: tutto ciò rendeo questo sistema estremamente utile anche in piccoli script di sistema per il logging.

Conclusioni

Grazie alla sua estrema duttilità, alle sue dimensioni contenute, alla sua licenza permissiva ed ai linguaggi che ne permettono l'utilizzo, SQLite è il candidato ideale per lo sviluppo di molteplici progetti siano essi personali sia professionali.
Nei prossimi articoli copriremo diversi aspetti di questo software.
Per il momento non rimane che scaricarlo ed iniziare ad utilizzarlo!