Raspberry Cars – Les Technos (3/4) – Manipuler une base de données en Python

Une base de données locale sur le Raspberry

Pendant les voyages, le Raspberry Pi n’est pas toujours connecté à l’internet. C’est pour ça qu’on va utiliser une base de données locale.

Dans cette base de données, on va enregistrer les positions GPS, les différentes informations de la voiture et ceci à période régulière.

La base de données va être simple et facile à modifier dans python.

Quand on cherche sur internet il y en a une qui s’appelle SQL Lite. Elle correspond à mon besoin, je vais donc apprendre à l’utiliser :

• Travailler avec SQLLite
  • Savoir l’installer sur le Raspberry
  • Manipuler directement la base de données

Pour cette première partie, j’ai trouvé un tutorial intéressant, qui ressemble à mon projet : https://iotbytes.wordpress.com/sqlite-db-on-raspberry-pi/

• Manipuler la base de données en Python
  • Installer le driver SQLLite pour Pyhton
  • Programmer en Python les accès

Installer SQLLite sur le Raspberry

Pour installer sqllite sur le raspberry PI, il faut utiliser le programme d’installation de package, en demandant sqllite :

sudo apt-get install sqlite3

Nota : On utilise sudo pour agir comme ‘super utilisateur’ du Raspberry.

Créer à la main la base de données SQLLite

Une base de données sqlite est un fichier sur le disque du Raspberry.

Pour le créer et y accéder, on va se placer là où on veut que soit stocké le fichier. Et lancer la commande de création de DB.

sqlite3 raspberrycars.db

On se retrouve avec l’interface de la DB en ligne de commande.

Manipuler directement la base de données SQLLite

Une base de données est composée de ‘tables’, qui sont des tableaux regroupant les informations sur un même sujet (par exemple une voiture, un enregistrement de position, etc.).

Par exemple, si on veut stocker la liste des positions, la table va ressembler à :

Pour demander à créer cette table car_status , on va utiliser la commande sqlite3 suivante

CREATE TABLE car_status ( gps_datetime integer
, gps_longitude  Decimal(9,6)
, gps_latitude  Decimal(9,6)
, gps_speed  Decimal(9,2)
) ;

Nota : on utilise un integer pour stocker l’heure : ce sera le nombre de secondes depuis le 1^er janvier 1970 : c’est une convention sur les système Unix.

On peut regarder ce qu’il y a dans cette nouvelle table en faisant la commande select

(le print screen avec tout ces enchainements de commande est à la fin de cette rubrique)

select * from car_status ;

La commande ne renvoie rien, car il n’y a encore rien dans la table !

On doit insérer un premier enregistrement. Pour le moment on va mettre des valeurs absurdes. Plus tard, on va mettre les vraies valeurs renvoyées par le capteur. La commande qui sert à ajouter des lignes dans une table est INSERT

Insert into car_status  ( gps_datetime , gps_longitude  , gps_latitude  ,  gps_speed  ) values ( 1, 6.109236, 49.756834, 	55.00 )

 Si on relance la commande select, on voit maintenant le nouvel enregistrement.

Rajoutons maintenant les trois autres lignes :

Insert into car_status  ( gps_datetime , gps_longitude  , gps_latitude  ,  gps_speed  ) values ( 2, 6.087241,	49.757059,	50.00)
Insert into car_status  ( gps_datetime , gps_longitude  , gps_latitude  ,  gps_speed  ) values ( 3, 6.085595,	49.760269,	20.00)

Si on relance la commande select, on voit maintenant les trois enregistrements.

Voici le print screen correspondant à la liste des commandes sqlite que nous avons lancées.

Avec une base de données, ce qui est important est de faire des requêtes. On peut ainsi demander à avoir un enregsitrement précis : par exemple celui fait pour l’heure ‘2’.

select * from car_status where gps_datetime = 2;

Ou tout ceux après l’heure ‘2’ incluse.

select * from car_status where gps_datetime >= 2;

Lire en Python le contenu de la base de données

Accéder à une base sqlite en python est facile.

J’ai trouvé des informations dans ce tutorial :

Au début de son programme Python, on doit demander d’utiliser la librairie python pour sqlite.

import sqlite3

Ensuite, on se connecte à la DB (j’ai recopié un code d’exemple…)

conn = sqlite3.connect(db_file)

On demande un curseur pour pouvoir faire une requête

cur = conn.cursor()

On demande d’exécuter la requête 

cur.execute("SELECT * FROM car_status")

On demande d’obtenir les résultats

rows = cur.fetchall()

Et pour vérifier si cela a fonctionné, on lance une boucle sur les lignes renvoyée

for row in rows:
   print(row)

Voici ce que cela donne dans l’IDE python

Et le code complet

import sqlite3

conn = sqlite3.connect('/home/pi/RaspberryCars/raspberrycars.db');

cur = conn.cursor();

cur.execute("select * from car_status");

rows = cur.fetchall();

for row in rows :
    print (row)

6.         Ecrire en Python du contenu dans la base de données

Alors comment fait-on pour insérer une nouvelle ligne dans la table ?

(car on devra insérer les lignes récupérées depuis le capteur).

On utilise la commande insert avec la fonction execute du curseur !

cur.execute(
    "insert into car_status (gps_datetime, gps_longitude, gps_latitude, gps_speed) values ( ?, ?, ? , ? ) ",
    (4, 6.101010, 49.757575, 30)
)

Le premier paramètre est la même instruction que l’on tapait à la main dans sqlite, mais pour laquelle les nouvelles valeurs sont remplacées par des points d’interrogation :

( ?, ?, ? , ? )

Le deuxième paramètre est le contenu que l’on veut insérer, dans le même ordre, sous forme d’un objet en python

(4, 6.101010, 49.757575, 30)

Pour provoquer l’enregsitrement des nouvelles valeurs, on doit demander à la connection de ‘commiter’ les changements faits dans la base par le curseur.

C’est l’instruction commit :

conn.commit();

A la fin du programme, on demande de fermer la connection à la DB .

conn.close();

Voilà ce que cela donne dans l’IDE python :

Et le code complet, avec la partie concernant l’insertion en rouge.

Ici on a fait afficher aussi la liste des lignes avant l’insertion, et après l’insertion, pour vérifier que tout se passe comme imaginé.

import sqlite3

conn = sqlite3.connect('/home/pi/RaspberryCars/raspberrycars.db');
cur = conn.cursor();

print ("Contenu de la table avant l'insertion")
cur.execute("select * from car_status");
rows = cur.fetchall();
for row in rows :
    print (row)

cur.execute(
    "insert into car_status (gps_datetime, gps_longitude, gps_latitude, gps_speed) values ( ?, ?, ? , ? ) ",
    (4, 6.101010, 49.757575, 30)
)

conn.commit();

print ("Contenu de la table après l'insertion")
cur.execute("select * from car_status");
rows = cur.fetchall();
for row in rows :
    print (row);

conn.close();