Les bases de données
- Bases de SQLite -

sqlite3 est un module Python permettant de manipuler des bases de données directement dans un programme, sans avoir à passer par un SGBD.

SQLite3

I. Import et création de la base de données

On l’importe classiquement :

import sqlite3

L’une des premières différences de SQLite et d’une gestion classique apr un SGBD est que dans le cas présent, la base de données est stockée dans un unique fichier .db. Pour l’ouvrir on fait simplement :

connexion = sqlite3.connect("database.db")

Notez bien que cette action ouvre une connexion vers la base de donnéessi elle existe mais peut aussi la créer (et donc le fichier qui va avec) si elle n’existe pas encore.

La table étant créée, il faut créer un curseur qui permet d’interagir avec elle :

curseur = connexion.cursor()

II. Création des tables

Nous pouvons allons exécuter du code SQL afin de créer des tables. Nous allons créer la table décrite ci-dessous :

Structure

Afin d’exécuter une ligne on fait toujours deux actions :

cuseur.execute(requete, arguments)
connexion.commit()

La première ligne exécute la requête exprimée en langage SQL, la seconde met à jour la base de données. Tant que la deuxième ligne n’est pas exécutée, le fichier database.db n’est pas mis à jour.

Commençons par créer les tables (on supprime les tables préexistantes au cas où…) :

# La table customers
curseur.execute("DROP TABLE IF EXISTS customers")

curseur.execute("""
CREATE TABLE customers
(
    CustomerID INT,
    AccountNumber TEXT,
    Title TEXT,
    FirstName TEXT,
    LastName TEXT,
    PRIMARY KEY(CustomerID)
);
""")

connexion.commit()
# La table products
curseur.execute("DROP TABLE IF EXISTS products")

curseur.execute("""
CREATE TABLE products
(
    ProductID INT,
    Name TEXT,
    ProductNumber TEXT,
    PRIMARY KEY(ProductID)
);
""")

connexion.commit()
# La table sales
curseur.execute("DROP TABLE IF EXISTS sales")

curseur.execute("""
CREATE TABLE sales
(
    Date REAL,
    SalesID INT,
    OrderQty INT,
    ProductID INT,
    UnitPrice REAL,
    LineTotal REAL,
    BuyerID INT,
    PRIMARY KEY(SalesID),
    FOREIGN KEY (BuyerID) REFERENCES customers(CustomerID)
    FOREIGN KEY (ProductID) REFERENCES products(ProductID)
);
""")

connexion.commit()

III. Insertion des valeurs

L’ajout des valeurs peut se faire à partir de fichiers csv. On doit tout d’abord lire les contenus dans une liste de liste (attention à l’ordre des attributs, il faudra le conserver pour l’insertion) :

customers = []

with open('customers.csv', 'r', encoding='utf-8') as f :
    print(f.readline())
    for line in f :
        customers.append(line.strip().split(';'))
CustomerID;AccountNumber;Title;FirstName;LastName
products = []

with open('products.csv', 'r', encoding='utf-8') as f :
    print(f.readline())
    for line in f :
        products.append(line.strip().split(';'))
ProductID;Name;ProductNumber
sales = []

with open('sales.csv', 'r', encoding='utf-8') as f :
    print(f.readline())
    for line in f :
        sales.append(line.strip().split(';'))
Date;SalesID;OrderQty;ProductID;UnitPrice;LineTotal;BuyerID

Nous pouvons donc importer les valeurs. Deux options s’offrent à nous : * itérer sur la liste des valeurs et faire un curseur.execute à chaque fois * utiliser la fonction curseur.executemany en lui passant la liste en second argument. Le code fera autant d’insertion que la liste contient de groupes de valeurs

# Première méthode
for valeurs in customers :
    curseur.execute("""
INSERT INTO customers (CustomerID,AccountNumber,Title,FirstName,LastName)
VALUES (?, ?, ?, ?, ?);
""", valeurs)

Remarquez l’astuce : on fournit les valeurs sous forme de points d’interrogations et on les associe aux valeurs. Cette technique permet en programmation web d’éviter (en partie) les injection SQL

# Deuxième méthode
curseur.executemany("""
INSERT INTO products (ProductID,Name,ProductNumber)
VALUES (?, ?, ?);
""", products)
<sqlite3.Cursor at 0x1be40fa7340>

Remplissons la dernière table en utilisant la deuxième technique :

# Deuxième méthode
curseur.executemany("""
INSERT INTO sales (Date,SalesID,OrderQty,ProductID,UnitPrice,LineTotal,BuyerID)
VALUES (?,?,?,?,?,?,?);
""", sales)
<sqlite3.Cursor at 0x1be40fa7340>

Enfin, on fait un commit :

connexion.commit()

IV. Sélection de valeurs

Pour effectuer des requêtes, on utilise la même fonction execute :

clients = curseur.execute("SELECT * FROM customers WHERE 1")

Par contre, le résultat n’est pas sous le format attendu :

clients
<sqlite3.Cursor at 0x1be40fa7340>

Pour le transformer en liste manipulable, on utiliser fetchall :

resultats = clients.fetchall()
resultats[:10]
[(29484, 'AW00029484', 'Mr.', 'Gustavo', 'Achong'),
 (29485, 'AW00029485', 'Ms.', 'Catherine', 'Abel'),
 (29486, 'AW00029486', 'Ms.', 'Kim', 'Abercrombie'),
 (29487, 'AW00029487', 'Sr.', 'Humberto', 'Acevedo'),
 (29488, 'AW00029488', 'Sra.', 'Pilar', 'Ackerman'),
 (29489, 'AW00029489', 'Ms.', 'Frances', 'Adams'),
 (29490, 'AW00029490', 'Ms.', 'Margaret', 'Smith'),
 (29491, 'AW00029491', 'Ms.', 'Carla', 'Adams'),
 (29492, 'AW00029492', 'Mr.', 'Jay', 'Adams'),
 (29493, 'AW00029493', 'Mr.', 'Ronald', 'Adina')]

Si l’on sait que note requête ne va renvoyer q’un résultat, on utilise fetchone :

client = curseur.execute("SELECT * FROM customers WHERE customerID = 29485")

resultat = client.fetchone()
resultat
(29485, 'AW00029485', 'Ms.', 'Catherine', 'Abel')

On peut ainsi faire des traitemens compliqués : quels sont les 20 produits les plus vendus ?

produits = curseur.execute("""
SELECT products.Name, sum(sales.OrderQty) as nombre
FROM products 
INNER JOIN sales ON sales.ProductID = products.ProductID
GROUP BY products.ProductID
ORDER BY nombre DESC;
""")

resultats = produits.fetchmany(20)
resultats
[('ML Headset', 101),
 ('ML Mountain Frame-W - Silver, 40', 94),
 ("Women's Tights, S", 88),
 ('HL Fork', 84),
 ('Mountain-200 Black, 38', 83),
 ('Road-750 Black, 48', 82),
 ('LL Mountain Frame - Black, 44', 80),
 ('Short-Sleeve Classic Jersey, L', 80),
 ("Men's Bib-Shorts, S", 79),
 ('Front Brakes', 78),
 ('LL Road Pedal', 78),
 ('Classic Vest, M', 77),
 ('Patch Kit/8 Patches', 76),
 ('Classic Vest, S', 76),
 ('Half-Finger Gloves, M', 76),
 ("Women's Mountain Shorts, L", 75),
 ('Mountain-300 Black, 48', 75),
 ('Mountain-200 Silver, 42', 75),
 ('Mountain-200 Black, 46', 74),
 ('ML Mountain Frame-W - Silver, 42', 73)]

Et un graphique :

prods = [p[0] for p in resultats]
quantites = [p[1] for p in resultats]

import matplotlib.pyplot as plt

plt.bar(prods, quantites)
plt.xticks(rotation = 90)
([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19],
 <a list of 20 Text xticklabel objects>)
png