GENERALITES SUR LES BASES DONNEES. 

On appelle base données un ensemble d'informations organisées dans des structures. Une base de données stockée et accessible depuis un système informatique sera gérée et contolée par l'intermédiaire d'un Système de Gestion de Base de Données, généralement désigné par DBMS, ou SGBD en français, via des applications dédiées à cette pratique.

Grossièrement, une base de donnée moderne, sera composée de tables, tableaux. Une table regroupera des enregistrements, fiches, lignes, entrées. Un enregistrement comportera des champs pour saisir, renseigner, puis mémoriser, enregistrer ses caractéristiques propres. Une table peut être représentée par une grille, où chaque ligne est un enregistrement et chaque colonne un champ de saisie. 

Plusieurs tables, d"une même base de données, peuvent avoir un champs de saisie commun, grace auquel elles pourront associer certaines de leur informations pour créer des tables temporaires et les restituer à l'utilisateur qui aura soumis une requête à la base de données.

Vous trouverez ci-après, une sélection de commandes indispensables pour une utilisation basique de module sqlite3. Cependant Vous êtes invités à une étudier attentivement de la documentation officielle du site officiel de SQLite, pour maximiser les fonctionnalités offertes par les SGBD au-delà d'une utilisation récréative.

INTERAGIR AVEC UNE BASE DE DONNEES.

Pour interagir physiquement avec la base de données :

- connectée à un l'objet-connexion grâce à la méthode objet = sqlite3.connect ( ) ;  

- consultable avec un, ou plusieurs, objet-curseur créé avec la méthode curseur = objet.cursor ( ) ; 

- il faudra faire appel à la méthode curseur.execute ( ) pour effectuer les commandes SQL ;

- et parfois à la méthode objet.commit ( ) pour sauvegarder les modifications.

Les commandes de base, voire indispensables sont :

- CREATE TABLE : créé une table de données dans la base de données.

- INSERT INTO : ajoute des enregistrements dans une table.

- SELECT FROM : effectue et retourne une sélection de fiches selon les critères demandés.

- UPDATE SET : modifie des enregistrements de la table précisée.

- ALTER TABLE : ajoute ou modifie une colonne à une table existante.

PRINCIPAES COMMANDES SQL.

ABS : retourne la valeur absolue de l'élément numérique précisée.

CUR_Curseur.execute ( " SELECT ABS ( nomdelacolonne ) FROM nomdelatable " )

print ( CUR_Curseur.fetchall ( ) )

ALTER TABLE : ajoute une colonne à une table existante, ou modifie le nom de la table ou d'une colonne.

Pour modifier le nom d'une table existante, utilisez l'option RENAME TO.

CUR_Curseur.execute ( " ALTER TABLE Table RENAME TO NouveauNom " )

BDD_Connexion.commit ( )

Pour modifier le nom d'une colonne dans une table existante, utilisez l'option RENAME COLUMN Colonne TO.

CUR_Curseur.execute ( " ALTER TABLE Table RENAME COLUMN Colonne TO NouveauNom " )

BDD_Connexion.commit ( )

Pour ajouter une colonne dans une table existante, utilisez l'option ADD COLUMN. La nouvelle colonne, entre autres :

- ne doit pas être celle qui recevra la clé primaire ; 

- doit avoir une valeur par défaut (DEFAULT valeur) si vous ajouter la contrainte NOT NULL.

CUR_Curseur.execute ( " ALTER TABLE Table ADD COLUMN colonne TYPE " )

BDD_Connexion.commit ( )

AVG : calcule et retourne la moyenne des valeurs de la colonne précisée.

CUR_Curseur.execute ( " SELECT AVG ( nomdelacolonne ) FROM nomdelatable " )

print ( CUR_Curseur.fetchone ( ) )

COUNT : retourne le nombre de lignes présentes dans la table.

CUR_Curseur.execute ( " SELECT COUNT (*) FROM nomdelatable " )

print ( CUR_Curseur.fetchone ( ) )

CREATE TABLE : créé une table de données dans la base de données.

CUR_Curseur.execute ( " CREATE TABLE Table ( colonne1 TYPE , ... , colonneN TYPE ) " )

Si une table de même nom est déjà présente dans la base de données, une exception sera levée.sauf si l'option IF NOT EXISTS est comprise dans la commande.

CUR_Curseur.execute ( " CREATE TABLE IF NOT EXISTS Table ( colonne1 TYPE , ... , colonneN TYPE ) " )

Il est possible de créer une table qui recevra, directement, le résultat d'une requête, effectuée dans une table existante. 

Dans  ce cas, les colonnes de la table-fille sont identiques aux colonnes de la table-mère.

CUR_Curseur.execute ( " CREATE TABLE IF NOT EXISTS enfant AS SELECT * FROM Table WHERE colonne = valeur " )

N.B. : La requête peut être de n'importe quelle sorte valide : =, < , > , et sur une ou plusieurs colonnes, ... 

TYPE doit être :

- INTEGER si les valeurs des éléments de la colonne sont des nombres entiers ;

- REAL si les valeurs des éléments de la colonne sont des nombres décimaux ;

- TEXT si les valeurs des éléments de la colonne sont des chaines de caractères ;

- BLOB si les valeurs des éléments de la colonne sont des objets BLOB ;

- NULL si les valeurs des éléments de la colonne sont vide.

CUR_Curseur.execute ( " CREATE TABLE Table ( col1 INTEGER , col2 REAL , col3 TEXT , col4 BLOB ) " )

Il est possible d'indiquer que la colonne recevra les clés primaires de la table, avec l'option PRIMARY KEY. Les valeurs des clés primaires sont toujours uniques, chaque ligne recevra une valeur qui n'a pas encore était attribuée pour cette colonne. En général on identifie cette colonne par le nom id, ID ou Id.

CUR_Curseur.execute ( " CREATE TABLE Table ( id INTEGER PRIMARY KEY , col2 TYPE , ... ) " )

La valeur de la clé primaire peut être générée automatiquement avec INTEGER PRIMARY KEY AUTOINCREMENT.

CUR_Curseur.execute ( " CREATE TABLE Table ( id INTEGER PRIMARY KEY AUTOINCREMENT , col2 TYPE , ... ) " )

La documentation officielle de SQLite précise que : "AUTOINCREMENT doit être évité s'il n'est pas strictement nécessaire"

Si la valeur de la ligne, pour une colonne donnée, doit être unique, indiquez la contrainte UNIQUE à la colonne. Il n'est pas utile de préciser cette contrainte pour la colonne recevant la clé primaire de la table.

CUR_Curseur.execute ( " CREATE TABLE Table ( id INTEGER PRIMARY KEY , col2 TYPE UNIQUE , ... ) " )

Il est possible de préciser une valeur par défaut pour une colonne avec la contrainte DEFAULT valeur. Cette valeur sera affectée au champ si celui-ci est NULL (vide) au moment de la validation de l'enregistrement. Cette valeur doit être du même type que celui définie pour la colonne.

CUR_Curseur.execute ( " CREATE TABLE Table ( col1 TYPE DEFAULT valeur , ... ) " )

Si la contrainte de colonne COLLATE MODE est indiquée, les tests de comparaison et le tri des valeurs de la colonnes, tiendront compte du mode de comparaison. MODE peut prendre une, ou une combinaison, des valeurs suivantes :

- BINARY, mode par défaut, compare les chaines de caractères de façon standardisée ;

- NOCASE, les tests de comparaisons ne tiennent pas compte de la case, les majuscules et minuscules ont la même valeurs ;

- RTRIM, les espaces en fin de chaine ne seront pas prit en compte.

CUR_Curseur.execute ( " CREATE TABLE Table ( col1 TYPE COLLATE MODE , ... ) " )

Pour s'assurer qu'une valeur à bien été affectée à un champs (cellule), on peut ajouter la contrainte de colonne NOT NULL.

CUR_Curseur.execute ( " CREATE TABLE Table ( col1 TYPE NOT NULL , ... ) " )

Evidemment, plusieurs contraintes peuvent être associées pour une même colonne. 

DELETE FROM : supprime des lignes (fiches / enregistrements) dans la table précisée.

La syntaxe suivante supprime tous les enregistrements présents dans la table, et conserve une table vide.

Voir la commande DROP TABLE pour supprimer définitivement une table de la base de données.

CUR_Curseur.execute ( " DELETE FROM Table " )

BDD_Connexion.commit ( )

Pour ne supprimer que certaines fiches, utilisez la contrainte WHERE colonne ( =, <, >, ... ) valeur.

CUR_Curseur.execute ( " DELETE FROM Table WHERE colonne = valeur " )

BDD_Connexion.commit ( )

Pour supprimer une quantité définie de fiches, utilisez la contrainte LIMIT quota, qui ne supprimera que quota fiches, même si la sélection de fiches à effacer est supérieure.

CUR_Curseur.execute ( " DELETE FROM Table WHERE colonne = valeur LIMIT quota " )

BDD_Connexion.commit ( )

La contrainte LIMIT quota, supprime les enregistrements dans l'ordre de saisie. Pour effacer la quantité de fiches souhaitée, selon un autre critère, il est possible d'utiliser la contrainte ORDER BY tri. Dans ce cas les fiches à supprimer seront arranger selon la valeur de tri, puis les fiches de 1 à quota seront effacées.

CUR_Curseur.execute ( " DELETE FROM Table WHERE colonne = valeur ORDER BY tri LIMIT quota " )

BDD_Connexion.commit ( )

DROP TABLE : supprime une table dans la base de données.

CUR_Curseur.execute ( " DROP TABLE Table " )

Si Table n'existe pas dans la base de données, une exception sera levée sauf si l'option IF EXISTS est présente.

CUR_Curseur.execute ( " DROP TABLE IF EXISTS Table " )

INSERT INTO : ajoute de nouveaux enregistrements (lignes / fiches) dans la table précisée.

Ne jamais indiquer les valeurs des champs directement dans l'instruction SQL.

Passez les à l'aide de tuple ( ) ou de list ( ) affectées avant l'appel ou à la suite de la commande. Cela en prévention de tous détournements de données de la part de tiers personnes mal intentionnées. Pour chaque colonnes définies dans la table, il doit y avoir une valeur de type compatible et dans le même ordre de définition.

kvaleurs = ( valeur1 , valeur2 , valeur3 , ... , valeurn )

CUR_Curseur.execute ( " INSERT INTO Table VALUES ( ? , ? , ? , ... ,? ) " , kvaleurs )

CUR_Curseur.execute ( " INSERT INTO Table VALUES ( ? , ? , ? , ... ,? ) " , ( valeurA , valeurB , valeurC , ... , valeurN ) )

BDD_Connexion.commit ( )

Pour éviter de très nombreux problèmes qui pourraient lever des exceptions ( ordre des colonnes, valeur facultative, clé primaire auto incrémentée, ...), utilisez de préférence la syntaxe. Dans ce cas, les éléments du tuple ( ) seront affectés à la colonne dans le même ordre que celui des champs dans la commande. Si des noms de colonnes sont absents, ils recevront la valeur par défaut (DEFAULT) si elle existe ou vide (NULL). Attention cependant aux colonnes dont on a précisé que la valeur ne devait pas être vide (NOT NULL) dans la définition de la table.

CUR_Curseur.execute ( " INSERT INTO Table ( champy , champsx , ... )  VALUES ( ? , ? , ... ) " , ( valeury , valeurx , ... ) )

BDD_Connexion.commit ( )

Il est aussi possible de créer un nouvel enregistrement, sans donner de valeur de saisie, avec la contrainte DEFAULT VALUES. Dans ce cas, chaque colonnes reçoit la valeur par défaut si DEFAULT est présent dans la définition de la colonne ou NULL dans le cas contraire. Attention cependant aux colonnes dont on a précisé que la valeur ne devait pas être vide (NOT NULL). 

CUR_Curseur.execute ( " INSERT INTO Table DEFAULT  VALUES " )

BDD_Connexion.commit ( )

LENGTH : retourne la longueur (nombre de caractères) de la valeur de la colonne précisée.

CUR_Curseur.execute ( " SELECT LENGTH ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

LOWER : converti les valeurs de la colonne précisée en minuscules puis les retourne.

CUR_Curseur.execute ( " SELECT LOWER ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

MAX : retourne la valeur la plus élevée dans la colonne précisée.

CUR_Curseur.execute ( " SELECT MAX ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

MIN : retourne la valeur la moins élevée dans la colonne précisée.

CUR_Curseur.execute ( " SELECT MIN ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

RANDOM ( ) AS : retourne une valeur speudo-aléatoire comprise entre -9223372036854775808 et 9223372036854775807.

Par convention, on utilise aleatoire comme base de données, mais vos pouvez utiliser n'importe quel nom valide.

CUR_Curseur.execute ( " SELECT RANDOM ( ) AS aleatoire " )

print ( CUR_Curseur.fetchone ( ) )

SELECT FROM : effectue et retourne une sélection de fiches selon les critères demandés.

Pour accéder à l'intégralité de la table utilisez le joker *.

CUR_Curseur.execute ( " SELECT * FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

Pour des raisons pratiques, les programmes Python devrez affecter les requêtes SQLite à des variables de type list ( ) pour une exploitation plus aisée : 

CUR_Curseur.execute ( " SELECT * FROM Table " )

LST_Fiches =  CUR_Test.fetchall ( )

for kfiche in LST_Fiches : print ( kfiche ) 

La contrainte WHERE permet des recherches plus sélectives. Le test peut être n'importe quelle comparaison ( =, <, >, ...).

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colonne = ? " , ( valeur , ) )

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

La contrainte ORDER BY SENS, force le tri des enregistrements de la sélection avant sa restitution. Le champ utilisé pour le tri des fiches ne doit par forcément être le champ, ou un des champs, utilisé pour sélectionner les fiches retournées.  SENS peut être : ASC valeur par défaut, pour un tri croissant ou DESC pour un tri décroissant.

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colonne = ? ORDER BY colonne " , ( valeur , ) ) 

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

ou

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colonneA = ? ORDER BY ASC colonneB " , ( valeur , ) ) 

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

ou

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colonneA = ? ORDER BY DESC colonneB " , ( valeur , ) ) 

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

Note :

- la valeur NULL sera toujours considérées comme la plus petite ;

- ( valeur , ) doit être un tuple ( ) avec la méthode CUR_Curseur.execute ( ) ;

- ( valeur , ) doit être une list ( ) de tuple ( ) ( [ ( valeur , ) , ( valeur , ) , ... , ( valeur , ) ] ) avec CUR_Curseur.executemany ( ).

Si la sélection retournée ne doit comprendre qu'un certain nombre, maximum, de lignes, précisez cette quantité avec la contrainte LIMIT quota, dans ce cas seules les lignes de rang 1 à quota, après un tri si nécessaire, seront retournées.

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colonne = ? LIMIT quota " , ( valeur , ) )

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

ou

CUR_Curseur.execute ( " SELECT * FROM Table WHERE colS = ? ORDER BY sens colT LIMIT quota " , ( valeur , ) )

LST_Selection =  CUR_Test.fetchall ( )

for kfiche in LST_Selection : print ( kfiche ) 

SUM : retourne la sommes des valeurs de la colonne précisée. 

La colonne doit être d'un type numérique :  INT ou REAL.

CUR_Curseur.execute ( " SELECT SUM ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )

UPDATE SET : modifie des enregistrements de la table précisée.

CUR_Curseur.execute ( " UPDATE Table SET champ1 = ? " , ( valeur , ) )

BDD_Connexion.commit ( )

ou

CUR_Curseur.execute ( " UPDATE Table SET col1 = ? , col2 = ?  , ... , colN = ? " , ( val1 ,  val2 , ... , valN ) )

BDD_Connexion.commit ( )

Note :

- ( valeur , ) doit être un tuple ( ) avec la méthode CUR_Curseur.execute ( ) ;

- ( valeur , ) doit être une list ( ) de tuple ( ) ( [ ( valeur , ) , ( valeur , ) , ... , ( valeur , ) ] ) avec CUR_Curseur.executemany ( ).

Pour modifier uniquement les fiches correspondantes à certains critères, utilisez la contrainte WHERE.

CUR_Curseur.execute ( " UPDATE Table SET col1 = ? WHERE col1 = ? " , ( nouvelle_valeur , ancienne_valeur ) )

BDD_Connexion.commit ( )

ou

CUR_Curseur.execute ( " UPDATE Table SET colM = ? WHERE colT = ? " , ( ( valeur_M , valeur_T ) )

BDD_Connexion.commit ( )

La contrainte LIMIT quota, modifie uniquement les enregistrements, dans l'ordre dans la sélection, du rang 1 à quota. Pour modifier la quantité de fiches souhaitée, selon un autre critère, il est possible d'utiliser la contrainte ORDER BY tri. Dans ce cas les fiches seront modifier de 1 à quota selon la valeur de tri

CUR_Curseur.execute ( " UPDATE Table SET champ1 = ? ORDER BY tri LIMIT quota " , ( valeur , ) )

BDD_Connexion.commit ( )

UPPER : converti les valeurs de la colonne précisée en majuscules puis les retourne.

CUR_Curseur.execute ( " SELECT UPPER ( colonne ) FROM Table " )

print ( CUR_Curseur.fetchone ( ) )