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.

bikeComputer con Arduino

Premessa

Finalmente è arrivata l’estate e con essa il caldo; la voglia di uscire e stare all’aria aperta si fa sentire ogni giorno con maggiore forza e, talvolta, nei pigri pomeriggi cittadini, nelle assolate domeniche fuori porta o sulla passeggiata del lungo mare decidiamo di abbandonare la tanto amata automobile per tornare a mezzi di locomozione più tradizionali: le nostre gambe ma, soprattutto, la bicicletta.


I più curiosi, in queste occasioni, vorrebbero sapere quanti chilometri riescono a percorrere, quale sia la velocità di punta toccata e quante pedalate riescono a fare in un minuto; magari per vantarsi con gli amici :-). E’ per far fronte a queste esigenze che io ed Enkel abbiamo pensato di progettare il bikeComputer con Arduino. In questo articolo vi racconterò come abbiamo fatto.

L'immagine che vedete qui sopra è il prototipo funzionante che abbiamo avvolto nello scotch sul manubrio della bici di Enkel per fare il test "su strada" :-)
Potete trovare tutte le immagini del progetto in questa pagina.

L'idea

L’idea è semplice: tramite arduino si visualizza la velocità di punta e la velocità media su un display 16X2.
Per contare i giri della ruota abbiamo utilizzato un reed switch. Questo sensore si comporta come un interruttore: esso rimane normalmente aperto (e quindi non passa corrente); arduino lo vede come un segnale digitale LOW.
Quando passa un magnete vicino (posizionato sui raggi della ruota), il sensore si chiude facendo passare corrente; in questo modo restituisce un HIGH digitale ad arduino riconosce il passaggio del magnete; il tempo trascorso tra un passaggio e l’altro ci permette di calcolare la velocità istantanea (e successivamente quella media) e, conoscendo il raggio della ruota, si può calcolare il numero di metri percorsi.

Analogo discorso si può fare per contare le pedalate piazzando il magnete sulla corona della bicicletta ed un secondo reed switch sul telaio della bicicletta.

Per ragioni di compattezza abbiamo utilizzato uno schermo LCD 16x2 ma abbiamo meditato l’uso di un 20x4 in modo da vedere tutte le informazioni contemporaneamente.

Ciò che ci interessava molto, però, era anche la possibilità di poter fruire dei dati dopo aver terminato la scampagnata (si pensi ad una gita: vado in un luogo in bici, mangio, gioco a freesbie e riprendo la bici fino a casa dove voglio analizzare le mie performance); per questo motivo abbiamo usato uno shield microSD con memoria da 1Gb.
La memoria ha un duplice scopo: l'utente dovrà inserire un file da denominare config.txt (qualsiasi operativo può creare un file di testo semplice) dove inserisce un numero di 4 cifre che esprime il diametro della ruota della propria bicicletta in mm (serve per il calcolo della distanza percorsa); dal canto suo arduino aggiorna il file log.txt.
Tale file sarà creato ed alimentato da arduino con tante righe quanti sono i passaggi del magnete; ogni riga contiene il numero di millisecondi passati dall’accensione di arduino (tramite la funzione millis()) nel momento in cui si rileva il passaggio del magnete vicino al reed (da questi è poi semplice ricavarsi tutto il resto magari effettuando un grafico con Processing).
L’alimentazione del sistema è stata affidata ad un pacco batterie composto da 4 pile in serie. Conteggiando circa 1.2-1.5V per ciascuna si arriva ad una tensione di alimentazione di 4.8-6V che abbiamo inserito direttamente nel pin Vin per evitare inutili dispersioni di energia.
Per quanto riguarda il “case” ci siamo appoggiati al FabLab di Torino (nelle immagini non è ancora presente ma è "coming soon") grazie all’aiuto di Davide G. ed Enrico C. che ci hanno aiutato con il laser-cutter a progettare un adeguato alloggiamento per il sistema.
Una esigenza con la quale ci siamo scontrati, inoltre, era il desiderio di rendere indipendente il reed sensor e il “case” con arduino dentro: l’idea è quella di arrivare a destinazione (magari davanti un supermarket per fare la spesa) e poter staccare tutto il sistema (per non lasciarlo in balia di occhi indiscreti e mani disoneste); a questo proposito è stata ingegnosa l’intuizione di Enkel che ha pensato di riutilizzare delle vecchie cuffie stereo per walkman: abbiamo tagliato gli auricolari dai quali prelevare due fili (massa e segnale); dall’altra estremità il jack da 3.5mm era già pronto per essere inserito durante l’utilizzo ed estratto a destinazione.

Schema

Seguono lo schema di connessione sulla breadboard e lo schema elettrico. Entrambi realizzati con fritzing.

Schema sulla breadboard:
Schema elettrico:

Codice

Di seguito il codice utilizzato

/*
////////////////////////////////////////////////
 
 [ Arduino Cycle Computer ] Version A0.2
 
 >> Original written by Adam O'Hern
 >> Modified by Alexdlp for Instructables 2011
 >> Modified by Enkel Bici
 >> and Vittorio Zuccala' for Wired.it 2011
 
 ////////////////////////////////////////////////
 */

#include <LiquidCrystal.h>
//#include <LcdBarGraph.h>
// initialize the library with the numbers of the interface pins
LiquidCrystal lcd( 7,6, 5, 4, 3, 2);

#include <SD.h>
File myFile;


//byte lcdNumCols = 15; // -- number of columns in the LCD
//LcdBarGraph lbg(&lcd, lcdNumCols);  // -- creating

int LED = 13;                    //pin for the LED
int SensorPin = 15;              //input for REED SensorPin (analog 0 as digital) 
int rim = 2035;                  //circumference of wheel in mm (28" rim)

int val = 0;                     // used to store input value
int previousVal = 0;             // lets not be too repetitious

int debounce = 10;               // the debounce time, increase if the output flickers
int cycles = 1;                  // total number of revolutions
float currentSpeed = 0;          // current speed in MPH
float averageSpeed = 0;          // average speed since "newRide" was true

unsigned long revTimer;          // create a timer that tells us how long we go between pulses,
unsigned long serialTimer;       // and one for how long it's been since we sent anything over serial
unsigned long rideTimer;         // total time since "newRide" was true

boolean activeRiding = false;    // is the bike moving?
boolean activityChange = true;   // just a way of keeping track of how long we're inactive
long inactivityTimer;            // millis() on which we began inactivity
long activeRidingOffset = 0;     // time subtracted from total riding time when calculating average speed
boolean newRide = true;          // true if we haven't moved in half an hour or more
float rideDistance = 0;          // total distance traveled since "newRide" was true

void setup() {
lcd.begin(16, 2);
pinMode(LED, OUTPUT);           // tell arduino LED port is an output,
pinMode(SensorPin, INPUT);         // and SensorPin port is input

lcd.setCursor(0,0);
lcd.print("Init SD...");
lcd.setCursor(0,1);
if (!SD.begin(8)) {
lcd.print("Init failed!");
delay(20000);
return;
}
else{
delay(2000);
lcd.print("Init OK!!");
delay(10000);
lcd.clear();
myFile = SD.open("setup.txt");
if (myFile) {
lcd.setCursor(0,0);
lcd.print("wheel:");
lcd.setCursor(0,1);
char c[5];
int count=0;
// read from the file until there's nothing else in it:
while (myFile.available()) {
c[count]=myFile.read();
count++;
}
// close the file:
myFile.close();
delay(2000);
rim=0;                          //reset the wheel diameter
for(count=0;count<4;count++){    //it supposes the diameter is expressed in mm and composed of 4 characters written in a text file on the sd card
rim=rim+(c[count]-48)*pow(10,(3-count));  //each character reappresents a number. 0 is equal 48 in ascii code. From then on you add 1 to get the next numbers. 
}
rim=rim+1;    //magic number
lcd.print(rim);
delay(2000);
}
else {
// if the file didn't open, print an error:
lcd.print("error reading...");
delay(20000);
}
}

Serial.begin(9600);             // start a serial session
revTimer = millis();            // start pulse timer
serialTimer = millis();         // start serial timer
rideTimer = millis();           // start ride timer
}

void loop(){

if(!activeRiding) {
if(activityChange) {
inactivityTimer = millis();
activityChange = false;
}
}
else {
if(activityChange) {
activeRidingOffset += millis() - inactivityTimer;
activityChange = false;
}
}


val = digitalRead(SensorPin);
if (val==LOW) {                 // check whether input is LOW (magnet is NOT in range of reed SensorPin)
digitalWrite(LED, LOW);       // turn LED off
previousVal = LOW;            // allow the next "pulse" to be counted
}
else{
digitalWrite(LED, HIGH);     // turn LED off
lcd.setCursor(1, 0);         // Set cursor to the top right of LCD
lcd.print("*");              // Print a blank character or 'space'
//delay(100);                // debounce timer
lcd.setCursor(1, 0);         // Set cursor to the top right of LCD
lcd.print(" ");              // Print a blank character or 'space'
if (previousVal != HIGH && millis() - revTimer > debounce) { // we've got a pulse!
pulse();
}
previousVal = HIGH;             // (in case the magnet is in range of the SensorPin while sitting still)
}

// if it's been too long since the last pulse, assume we're not moving.
if(millis()-revTimer > 2000) {
currentSpeed = 0;
sendStats();
if(activeRiding) {
activityChange = true;
activeRiding = false;
}
}

// if it's been more than fifteen minutes...
if (millis() - revTimer > 15*60*1000) {
// we'll assume it's a new riding session & zero everything out at next pulse.
newRide = true;     
}
}


void pulse() {
if(newRide) {
lcd.clear();
cycles = 0;
averageSpeed = 0;
rideTimer = millis();
rideDistance = 0;

//log data to file
// open the file. note that only one file can be open at a time,
// so you have to close this one before opening another.
File dataFile = SD.open("log.txt", FILE_WRITE);

// if the file is available, write to it:
if (dataFile) {
dataFile.print("newride");
dataFile.println(",");
dataFile.close();
// print to the serial port too:
//Serial.println("newride");
}
// if the file isn't open, pop up an error:
else {
lcd.setCursor(1, 0);
lcd.println("can't write");
}
}

cycles++;                                             // The wheel has obviously turned another revolution

rideDistance = (float) rim*cycles/1000;               // distance in meters, 
//rideDistance = rideDistance * 0.000621371192;       // converted to miles

currentSpeed = (float) (millis() - revTimer)*0.001;   // Convert time elapsed to milliseconds to seconds
currentSpeed = rim/currentSpeed;                      // S = d/t: Rim circumference divided by time elapsed
//currentSpeed = currentSpeed*0.002237;               // MPH Conversion: 1 mm/s = 0.001 m/s * 3600 s/hr * 1 mile / 1609 m = 0.002237 
i/hr
currentSpeed = currentSpeed*0.0036;                     // 1Km/hr Conversion: 1 mm/s = 0.001 m/s * 3600 s/hr * 1Km/1000m  = 0,0036 Km/hr

// time ridden since "newRide", in hours, not including inactive time
unsigned long activeRidingMillis = millis() - rideTimer - activeRidingOffset;
//float activeRidingHours = (float) activeRidingMillis/1000/60/60; // convert to hours
float activeRidingSeconds = (float) activeRidingMillis*0.001; // convert to hours
averageSpeed = rideDistance / activeRidingSeconds;
averageSpeed = averageSpeed*3,6;

revTimer = millis();       // remember the current moment for speed calculations next time around
sendStats();               // Tell Processing what's going on
newRide = false;



if(!activeRiding) {         //if you start from standin still 
activityChange = true;
activeRiding = true;
}

//log data to file
// open the file. note that only one file can be open at a time,
// so you have to close this one before opening another.
File dataFile = SD.open("log.txt", FILE_WRITE);

// if the file is available, write to it:
if (dataFile) {
dataFile.print(revTimer);
dataFile.println(",");
dataFile.close();
// print to the serial port too:
Serial.println(revTimer);
}
// if the file isn't open, pop up an error:
else {
lcd.setCursor(1, 0);
lcd.println("can't write");
}
}

void sendStats() {
/*
  Serial.print("rideDistance=");
   Serial.print(rideDistance,2);
   Serial.print("&currentSpeed=");
   Serial.print(currentSpeed,1);
   */
lcd.setCursor(0, 1);
lcd.print("Speed = ");
lcd.setCursor(9, 1);
lcd.print(currentSpeed,1);
lcd.setCursor(13, 1);
lcd.print("kmh");

//lbg.drawValue(currentSpeed, 35);
/*
  Serial.print("&averageSpeed=");
   Serial.print(averageSpeed,2);
   
   // send a linefeed character, telling Processing that we're done transmitting.
   Serial.print(10,BYTE);     
   */
serialTimer = millis();
}