Avec la base de données suivantes :
DROP TABLE IF EXISTS panier;
DROP TABLE IF EXISTS ligne;
DROP TABLE IF EXISTS commande;
DROP TABLE IF EXISTS article;
DROP TABLE IF EXISTS client;
CREATE TABLE IF NOT EXISTS article (
id_article INT AUTO_INCREMENT
, designation VARCHAR(255)
, prix NUMERIC(19,4)
, stock INT
, PRIMARY KEY (id_article)
)CHARACTER SET 'utf8mb4' ;
CREATE TABLE IF NOT EXISTS client (
id_client INT AUTO_INCREMENT
, nom VARCHAR(25)
, ville VARCHAR(25)
, PRIMARY KEY (id_client)
)CHARACTER SET 'utf8mb4';
CREATE TABLE IF NOT EXISTS commande (
id_commande INT AUTO_INCREMENT
, date_commande DATE
, id_client INT
, PRIMARY KEY (id_commande)
, CONSTRAINT fk_commande_client_exo3 FOREIGN KEY (id_client) REFERENCES client(id_client)
)CHARACTER SET 'utf8mb4';
CREATE TABLE IF NOT EXISTS ligne (
id_commande INT
, id_article INT
, quantite INT
, PRIMARY KEY (id_commande,id_article)
, CONSTRAINT fk_ligne_commande_exo3 FOREIGN KEY (id_commande) REFERENCES commande(id_commande)
, CONSTRAINT fk_ligne_article_exo3 FOREIGN KEY (id_article) REFERENCES article(id_article)
);
CREATE TABLE IF NOT EXISTS panier (
id_client INT
, id_article INT
, quantite INT
, PRIMARY KEY (id_client,id_article)
, CONSTRAINT fk_panier_client_exo3 FOREIGN KEY (id_client) REFERENCES client(id_client)
, CONSTRAINT fk_panier_article_exo3 FOREIGN KEY (id_article) REFERENCES article(id_article)
);
INSERT INTO client VALUES
(1,'Mutz','Ruelisheim'),(2,'Rato','Besancon'),(3,'Goerig','Walheim'),(4,'Walter','Bretten'),
(5,'PAUTOT','Belfort'),(6,'PETIT','Belfort'),(7,'SAINT DIZIER','Sevenans'),(8,'SALVI','Offemont'),
(9,'TERRAT','Belfort'),(10,'TYRODE','Valdoie'),(11,'ALANKAYA','Bavilliers'),(12,'DAROSEY','Essert'),
(13,'duguet','Belfort'),(14,'ESSENBURGER','Belfort'),(15,'JAOUEN','Sevenans'),(16,'molin','Belfort'),
(17,'AMGHAR','Belfort'),(18,'BOUCHAUD','Belfort'),(19,'COTTARD','Belfort'),(20,'LAMOTTE','Belfort'),
(21,'METTEY','Belfort'),(22,'WOLF','Belfort'),(23,'BISMUTH','Belfort'),(24,'chaillet','Belfort'),(25,'DECOCK','Belfort');
INSERT INTO article VALUES
(1,'Gâteaux chocolat ( pâtisserie)',5.25,10),(2,'gâteau fraise (pâtisserie) ',5.25,10),(3,'Mouchoir ( hygiène)',2.54,10),
(4,'Coca cola ( boisson )',1.52,10),(5,'Salade ( légume ) ',2.00,10),(6,'Choux ( légume ) ',2.50,10),
(7,'Pomme de terre ( légume ) ',1.50,10),(8,'Tomate ( légume ) ',1.50,10),(9,'Haricot ( légume ) ',6.50,10),
(10,'Potiron ( légume ) ',3.00,10),(11,'Poireau ( légume ) ',1.50,10),(12,'Fenouil ( légume ) ',3.00,10),
(13,'Pissenlit ( légume ) ',5.00,10),(14,'Petit pois ( légume ) ',6.00,10),(15,'Poivron ( légume ) ',3.00,10),
(16,'Radis ( légume ) ',2.00,10),(17,'Pomme ( fruit ) ',3.00,10),(18,'Perrier ( 1L eau ) ',1.50,10),
(19,'Vittel ( 1L eau ) ',1.00,10);
INSERT INTO commande VALUES
(1,'2023-04-11',3),
(2,'2023-12-11',2),
(3,'2024-01-28',4),
(4,'2023-09-19',1),
(5,'2019-04-11',3),
(6,'2023-12-11',2),
(7,'2023-03-28',4),
(8,'2023-12-09',1);
INSERT INTO ligne VALUES
(1,4,12),(1,9,4),(1,12,4),(1,15,4),
(2,3,6),(2,6,6),(2,7,6),(2,14,6),
(3,1,3),(3,11,3),(3,12,3),(3,16,3),
(4,2,27),(4,8,5),(4,10,5),(4,17,5),
(5,12,4),
(6,11,3),
(7,8,5),
(8,14,6);
INSERT INTO panier(id_client, id_article, quantite) VALUES
(1,4,4),(1,7,4),
(2,4,4),(2,19,4),
(3,7,4),(3,17,4),
(4,4,4),(4,7,4),(4,17,4),(4,19,4),
(5,4,4),(5,7,4),(5,17,4),(5,19,4),
(6,4,4),(6,7,4),(6,17,4),(6,19,4);
Créer une procédure sur le SGBDR MySQL en PL/SQL qui reçoit en paramètre un identifiant de client, cette procédure permet de :
Créer un enregistrement dans la table commande avec comme date la date d’aujourd’hui et comme identifiant de client le paramètre passé.
Recopiez les enregistrements de la table “panier” ayant pour identifiant client l’identifiant client passé en paramètre dans la table ligne avec comme identifiant de commande celui de l’enregistrement créé précédemment.
Pour tester cette procédure, utiliser la commande suivante :
SET @id_client=1;
SELECT * FROM panier WHERE id_client=@id_client;
-- +-----------+------------+----------+
-- | id_client | id_article | quantite |
-- +-----------+------------+----------+
-- | 1 | 4 | 4 |
-- | 1 | 7 | 4 |
-- +-----------+------------+----------+
SELECT * FROM commande WHERE id_client=@id_client;
-- +-------------+---------------+-----------+
-- | id_commande | date_commande | id_client |
-- +-------------+---------------+-----------+
-- | 4 | 2023-09-19 | 1 |
-- | 8 | 2023-12-09 | 1 |
-- +-------------+---------------+-----------+
CALL creer_commande(@id_client); -- commande 1
SELECT * FROM panier WHERE id_client=@id_client;
-- +-------------+---------------+-----------+
-- Empty set
-- +-------------+---------------+-----------+
SELECT * FROM commande WHERE id_client=@id_client;
-- +-------------+---------------+-----------+
-- | id_commande | date_commande | id_client |
-- +-------------+---------------+-----------+
-- | 4 | 2023-09-19 | 1 |
-- | 8 | 2023-12-09 | 1 |
-- | 9 | 2024-05-23 | 1 |
-- +-------------+---------------+-----------+
SELECT MAX(id_commande) INTO @id_commande
FROM commande;
SELECT * FROM ligne WHERE id_commande=@id_commande;
-- +-------------+------------+----------+
-- | id_commande | id_article | quantite |
-- +-------------+------------+----------+
-- | 9 | 4 | 4 |
-- | 9 | 7 | 4 |
-- +-------------+------------+----------+
CALL creer_commande(@id_client); -- commande 2 : pas de commande vide
-- +-------------+---------------+-----------+
-- ERROR 1644 (45000): Le panier est vide.
-- +-------------+---------------+-----------+
SELECT
c.id_commande, c.date_commande, cl.nom AS client_nom, cl.ville AS client_ville,
a.id_article, a.designation, l.quantite, a.prix, a.stock
FROM
commande c JOIN client cl ON c.id_client = cl.id_client
LEFT JOIN ligne l ON c.id_commande = l.id_commande
LEFT JOIN article a ON l.id_article = a.id_article
WHERE c.id_client = @id_client
ORDER BY c.date_commande, c.id_commande, l.id_article;
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | id_commande | date_commande | client_nom | client_ville | id_article | designation | quantite | prix | stock |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 2 | gâteau fraise (pâtisserie) | 27 | 5.2500 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 8 | Tomate ( légume ) | 5 | 1.5000 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 10 | Potiron ( légume ) | 5 | 3.0000 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 17 | Pomme ( fruit ) | 5 | 3.0000 | 10 |
-- | 8 | 2023-12-09 | Mutz | Ruelisheim | 14 | Petit pois ( légume ) | 6 | 6.0000 | 10 |
-- | 9 | 2024-05-23 | Mutz | Ruelisheim | 4 | Coca cola ( boisson ) | 4 | 1.5200 | 6 |
-- | 9 | 2024-05-23 | Mutz | Ruelisheim | 7 | Pomme de terre ( légume ) | 4 | 1.5000 | 6 |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,7,14),(@id_client,17,4);
CALL creer_commande(@id_client); -- commande 3 : gestion du stock
-- +-------------+---------------+-----------+
-- ERROR 1644 (45000): Stock insuffisant pour l'article avec id 7 avec comme stock 6
-- +-------------+---------------+-----------+
DELETE FROM panier WHERE id_client=@id_client;
INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,7,2),(@id_client,17,3);
CALL creer_commande(@id_client); -- commande 3 : gestion du stock
SELECT
c.id_commande, c.date_commande, cl.nom AS client_nom, cl.ville AS client_ville,
a.id_article, a.designation, l.quantite, a.prix, a.stock
FROM
commande c JOIN client cl ON c.id_client = cl.id_client
LEFT JOIN ligne l ON c.id_commande = l.id_commande
LEFT JOIN article a ON l.id_article = a.id_article
WHERE c.id_client = @id_client
ORDER BY c.date_commande, c.id_commande, l.id_article;
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | id_commande | date_commande | client_nom | client_ville | id_article | designation | quantite | prix | stock |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 2 | gâteau fraise (pâtisserie) | 27 | 5.2500 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 8 | Tomate ( légume ) | 5 | 1.5000 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 10 | Potiron ( légume ) | 5 | 3.0000 | 10 |
-- | 4 | 2023-09-19 | Mutz | Ruelisheim | 17 | Pomme ( fruit ) | 5 | 3.0000 | 7 |
-- | 8 | 2023-12-09 | Mutz | Ruelisheim | 14 | Petit pois ( légume ) | 6 | 6.0000 | 10 |
-- | 9 | 2024-05-23 | Mutz | Ruelisheim | 4 | Coca cola ( boisson ) | 4 | 1.5200 | 6 |
-- | 9 | 2024-05-23 | Mutz | Ruelisheim | 7 | Pomme de terre ( légume ) | 4 | 1.5000 | 4 |
-- | 10 | 2024-05-23 | Mutz | Ruelisheim | 7 | Pomme de terre ( légume ) | 2 | 1.5000 | 4 |
-- | 10 | 2024-05-23 | Mutz | Ruelisheim | 17 | Pomme ( fruit ) | 3 | 3.0000 | 7 |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+En testant, le SQL ci-dessus, on peut voir que la procédure PL/SQL autorise des commandes sans articles et que le stock n’est pas géré ; modifier votre script pour éviter ce problème.
Dans votre script, il faut prendre en compte est les opérations suivantes :
Il suffit de modifier la connexion (les paramètres), l’appel de la procédure et supprimer 3 lignes qui ne servent à rien.
#! /usr/bin/python
# -*- coding:utf-8 -*-
import pymysql
import sys
# Vérifier que le script a reçu le bon nombre d'arguments
if len(sys.argv) != 2:
print("Usage: python script.py <num_client>")
sys.exit(1)
# Connexion à la base de données MySQL
conn = pymysql.connect(
host="votre_hôte",
user="votre_utilisateur",
password="votre_mot_de_passe",
database="votre_base_de_données",
port=3306 # Remplacez par le port MySQL utilisé dans votre configuration
)
try:
# Création d un curseur
cursor = conn.cursor()
# Exécution de la procédure stockée
num_client = int(sys.argv[1])
num_ss = 123456789
nom_e = "Doe"
prenom_e = "John"
cursor.callproc("NomDeVotreProcedureMySQL", (num_ss, nom_e, prenom_e))
# Commit pour valider les modifications
conn.commit()
# Affichage du résultat ou de tout message renvoyé par la procédure
result = cursor.fetchall()
print(result)
except pymysql.Error as e:
# Récupération du message d erreur
print(e)
error_message = e.args[1]
print(f"Erreur MySQL: {error_message}")
finally:
# Fermeture du curseur et de la connexion
cursor.close()
conn.close()Exemple de résultat dans un terminal :
$ python script_python_exo3.py 3
(1644, "Stock insuffisant pour l'article avec id 7 avec comme stock 6")
Erreur MySQL: Stock insuffisant pour l'article avec id 7 avec comme stock 6Créez des triggers (déclencheurs) sur la table panier. Ces triggers, lorsque l’on crée/supprime/modifie un enregistrement dans la table panier, mettent à jour le stock dans la table article. (le stock ne peut pas être négatif).
Dans ce cas, la mise à jour du stock dans la procédure PL/SQL précédente n’a plus de sens (il faut la retirer).
SET @id_client=1;
SELECT * FROM panier WHERE id_client=@id_client;
-- +-----------+------------+----------+
-- | id_client | id_article | quantite |
-- +-----------+------------+----------+
-- | 1 | 7 | 14 |
-- | 1 | 17 | 4 |
-- +-----------+------------+----------+
SELECT * FROM article WHERE id_article IN (3,4,7,17);
-- +------------+-----------------------------+--------+-------+
-- | id_article | designation | prix | stock |
-- +------------+-----------------------------+--------+-------+
-- | 3 | Mouchoir ( hygiène) | 2.5400 | 10 |
-- | 4 | Coca cola ( boisson ) | 1.5200 | 6 |
-- | 7 | Pomme de terre ( légume ) | 1.5000 | 6 |
-- | 17 | Pomme ( fruit ) | 3.0000 | 10 |
-- +------------+-----------------------------+--------+-------+
INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,7,14),(@id_client,17,4),(@id_client,3,3),(@id_client,4,3);
-- INSERT INTO panier(id_client, id_article, quantite) VALUES
-- (@id_client,3,3),(@id_client,4,3);
SELECT * FROM panier WHERE id_client=@id_client;
-- +-----------+------------+----------+
-- | id_client | id_article | quantite |
-- +-----------+------------+----------+
-- | 1 | 3 | 3 |
-- | 1 | 4 | 3 |
-- | 1 | 7 | 14 |
-- | 1 | 17 | 4 |
-- +-----------+------------+----------+
SELECT * FROM article WHERE id_article IN (3,4,7,17);
-- +------------+-----------------------------+--------+-------+
-- | id_article | designation | prix | stock |
-- +------------+-----------------------------+--------+-------+
-- | 3 | Mouchoir ( hygiène) | 2.5400 | 10 |
-- | 4 | Coca cola ( boisson ) | 1.5200 | 6 |
-- | 7 | Pomme de terre ( légume ) | 1.5000 | 4 |
-- | 17 | Pomme ( fruit ) | 3.0000 | 7 |
-- +------------+-----------------------------+--------+-------+
INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,8,23);
-- +------------+-----------------------------+--------+-------+
-- ERROR 1644 (45000): Stock insuffisant pour l article.
-- +------------+-----------------------------+--------+-------+
DELETE FROM panier WHERE id_client=@id_client AND id_article=4;
UPDATE panier SET quantite=quantite+2 WHERE id_client=@id_client AND id_article=3;
SELECT * FROM panier WHERE id_client=@id_client;
-- +-----------+------------+----------+
-- | id_client | id_article | quantite |
-- +-----------+------------+----------+
-- | 1 | 3 | 5 |
-- | 1 | 7 | 14 |
-- | 1 | 17 | 4 |
-- +-----------+------------+----------+
SELECT * FROM article WHERE id_article IN (3,4,7,17);
-- +------------+-----------------------------+--------+-------+
-- | id_article | designation | prix | stock |
-- +------------+-----------------------------+--------+-------+
-- | 3 | Mouchoir ( hygiène) | 2.5400 | 8 |
-- | 4 | Coca cola ( boisson ) | 1.5200 | 9 |
-- | 7 | Pomme de terre ( légume ) | 1.5000 | 4 |
-- | 17 | Pomme ( fruit ) | 3.0000 | 7 |
-- +------------+-----------------------------+--------+-------+
UPDATE panier SET quantite=quantite+12 WHERE id_client=@id_client AND id_article=3;
-- +------------+-----------------------------+--------+-------+
-- ERROR 1644 (45000): Stock insuffisant pour l article.
-- +------------+-----------------------------+--------+-------+