SQL (avec Python et SQLite) et clefs étrangères

Bonjour,

Je rencontre actuellement un problème en SQL avec les clefs étrangères.

Je souhaite créer une table Auteurs comportant trois paramètres :

• id_auteur, une clé primaire attachée à l’auteur
• prenom, le prénom de l’auteur
• nom, le nom de l’auteur

Je voudrais pouvoir créer une seconde table livre comportant :

• id_livre, une clé primaire attachée au livre
• titre, le titre du livre
• auteur, une clef étrangère correspondant à id_auteur

Voici le code SQL que j’ai réalisé pour cela :

CREATE TABLE Auteurs(
id_auteur INTEGER PRIMARY KEY,
prenom TEXT,
nom TEXT
);

CREATE TABLE Livres(
id_livre INTEGER PRIMARY KEY,
titre TEXT,
auteur INTEGER,
FOREIGN KEY(auteur) REFERENCES Auteurs(id_auteur)
);

J’utilise le module python sqlite3 de Python ; voici mon code complet :

import sqlite3

connexion = sqlite3.connect("database2.db")
curseur = connexion.cursor()

curseur.execute("PRAGMA foreign_keys = ON;")

curseur.executescript(
'''
DROP TABLE IF EXISTS Auteurs;
DROP TABLE IF EXISTS Livres;

CREATE TABLE Auteurs(
id_auteur INTEGER PRIMARY KEY,
prenom TEXT,
nom TEXT
);

CREATE TABLE Livres(
id_livre INTEGER PRIMARY KEY,
titre TEXT,
auteur INTEGER,
FOREIGN KEY(auteur) REFERENCES Auteurs(id_auteur)
);

''')
data_auteurs = [("Emile","Zola"),\
                ("J.K.","Rowling"),\
                ("Lewis","Caroll"),\
                ("J.R.R","Tolkien"),\
                ("Christopher","Paolini")]
curseur.executemany("INSERT INTO Auteurs(prenom,nom) VALUES (?,?)",data_auteurs)

data_livres = [("Thérèse Raquin",1),\
               ("Harry Potter",2),\
               ("Alice au pays des merveilles",3),\
               ("Bilbo le hobbit",4),\
               ("Eragon",5)]

curseur.executemany("INSERT INTO Livres(titre,auteur) VALUES (?,?)",data_livres)

for auteur in curseur.execute("SELECT * FROM Auteurs"):
    print("Auteur :",auteur)

for livre in curseur.execute("SELECT * FROM Livres"):
    print("Livre :",livre)
    

connexion.commit()
connexion.close()

J’obtiens l’erreur suivante et n’arrive pas à m’en débarrasser :

Traceback (most recent call last):
  File "/Users/christophe_mayeux/Documents/tests sur sqlite3.py", line 8, in <module>
    curseur.executescript(
sqlite3.IntegrityError: FOREIGN KEY constraint failed

Joyeuses fêtes,

@flopy78

Tu pars du postulat que Emile Zola aura le n°1. De quoi tu te mêles ? Ces identifiants sont gérés par SQL, SQl va mettre les numéros qu’il a envie de mettre.

Thérèse Raquin a été écrit par Emile Zola ; c’est tout ce qu’on sait.

Donc

data_livres = [("Thérèse Raquin","Emile", "Zola"),\
               ("Harry Potter","J.K.","Rowling"),\
               ("Alice au pays des merveilles","Lewis","Caroll"),\
               ("Bilbo le hobbit","J.R.R","Tolkien"),\
               ("Eragon","Christopher","Paolini")]

Et il faut se débrouiller pour insérer ça …

Peut-être :

curseur.executemany("INSERT INTO Livres(titre,auteur) select ? , id_auteur from auteur a where a.nom= ? and a.prenom = ?)",data_livres)

En terme de normalisation, ce serait mieux si cette colonne s’appelait id_auteur, et non auteur.

Pour ton erreur, comme dit ailleurs, c’est normal parce-que tu fais refais systématiquement et inutilement les mêmes opérations qui vont passer la première fois que tu lances le script mais pas les fois d’après. Tu peux le reproduire en refaisant manuellement la même chose dans une console sqlite.

Au départ, quand ta base est vide :

$ ls -og database2.db
ls: database2.db: No such file or directory
$ # il faut faire un `rm database2.db` sinon
$ sqlite3 database2.db
SQLite version 3.39.4 2022-09-07 20:51:41
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE IF EXISTS Auteurs;
sqlite> DROP TABLE IF EXISTS Livres;
sqlite> CREATE TABLE Auteurs(
   ...> id_auteur INTEGER PRIMARY KEY,
   ...> prenom TEXT,
   ...> nom TEXT
   ...> );
sqlite> CREATE TABLE Livres(
   ...> id_livre INTEGER PRIMARY KEY,
   ...> titre TEXT,
   ...> auteur INTEGER,
   ...> FOREIGN KEY(auteur) REFERENCES Auteurs(id_auteur)
   ...> );
sqlite> INSERT INTO Auteurs(prenom,nom) VALUES
   ...> ("Emile","Zola"),
   ...> ("J.K.","Rowling"),
   ...> ("Lewis","Caroll"),
   ...> ("J.R.R","Tolkien"),
   ...> ("Christopher","Paolini");
sqlite> INSERT INTO Livres(titre,auteur) VALUES
   ...> ("Thérèse Raquin",1),
   ...> ("Harry Potter",2),
   ...> ("Alice au pays des merveilles",3),
   ...> ("Bilbo le hobbit",4),
   ...> ("Eragon",5);
sqlite> .quit
$ ls -og database2.db
-rw-r--r--  1   12288 Dec 25 14:12 database2.db

Ensuite, quand tu relances ton script sur ta base déjà remplie :

$ sqlite3 database2.db
SQLite version 3.39.4 2022-09-07 20:51:41
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE IF EXISTS Auteurs;
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> .quit

…ton script tente de supprimer une table dont les lignes sont référencées dans l’autre table. CQFD

Bonjour,

Je crois que je viens de percuter grâce à vous.

Merci infiniment pour votre précieuse aide,

Bonne soirée,

@flopy78

Bonjour,

Je m’attaque actuellement au SQL via Python et sqlite3.

Je bute sur un point : comment manier les clefs étrangères ?

Par exemple, si je crée les deux tables suivantes :

PRAGMA foreign_keys = ON;

CREATE TABLE Clients ( id_client INTEGER PRIMARY KEY, nom TEXT );

CREATE TABLE Commandes ( id_commande INTEGER PRIMARY KEY, id_client INTEGER, FOREIGN KEY (id_client) REFERENCES Clients(id_client) );

Je me demande comment sélectionner les commandes passées par un client donné (en utilisant la clef étrangère, donc).

Merci d’avances pour votre aide,

@flopy78

Normalement il faut avoir des scripts différents :

• Un d’initialisation pour créer la structure la première fois… Éventuellement le même, avec les DROP TABLE IF EXISTS, si on prévoit de pouvoir réinitialiser la base, mais ça peut être un autre qui fait juste les DROP TABLE puis enchaine sur l’autre.
• Un de désinstallation avec juste les DROP TABLE… En fait celui-ci peut servir pour la réinitialisation aussi.
• Un pour remplir les données d’exemple
• etc.

Maintenant, supposons qu’on a les tables suivantes :

• T₁ sans lien ailleurs
• T₂ avec une clé dans T₁
• T₃ sans lien ailleurs
• T₄ avec une clé dans T₃
• T₅ avec une clé dans T₃
• T₆ avec une clé dans T₂ et une autre dans T₄
• etc.

Cela donne un schéma de dépendance qui ressemble à ceci :

Au moment de la création, il faut d’abord déclarer la première colonne puis la seconde (en tout cas T₃ avant T₄ et T₅ par exemple.) Au moment de la suppression, il faut à l’inverse partir de la dernière colonne vers la première (en tout cas T₆ avant T₄ et T₅ qui eux-même doivent se faire avant T₃ par exemple.) Dans le cas présent, il faut créer Auteurs avant de créer Livres; mais il faut supprimer Livres avant de pouvoir supprimer Auteurs …

Je fais un post plus tard sur le point pertinent soulevé par elegance plus tôt.

Salut,

Pour sélectionner les commandes réalisées par un client précis, il faut que tu aies l’id de ce client. Tu peux ensuite faire ta requête SELECT en ajoutant une condition WHERE. Tu trouveras facilement des explications pour ça sur le net, n’hésite pas à revenir si tu n’y arrives pas.

Petit test illustratif (vu que je n’ai pas encore effacé la base)

$ sqlite3 database2.db
SQLite version 3.39.4 2022-09-07 20:51:41
Enter ".help" for usage hints.
sqlite> SELECT * FROM Auteurs;
1|Emile|Zola
2|J.K.|Rowling
3|Lewis|Caroll
4|J.R.R|Tolkien
5|Christopher|Paolini
sqlite> SELECT * FROM Livres;
1|Thérèse Raquin|1
2|Harry Potter|2
3|Alice au pays des merveilles|3
4|Bilbo le hobbit|4
5|Eragon|5
sqlite> DELETE FROM Livres WHERE titre='Eragon';
sqlite> SELECT * FROM Livres;
1|Thérèse Raquin|1
2|Harry Potter|2
3|Alice au pays des merveilles|3
4|Bilbo le hobbit|4
sqlite> SELECT * FROM Livres;
1|Thérèse Raquin|1
2|Harry Potter|2
3|Alice au pays des merveilles|3
4|Bilbo le hobbit|4
sqlite> INSERT INTO Auteurs(prenom,nom) VALUES ('Anne','Onyme');
sqlite> DELETE FROM Auteurs WHERE id_auteur=5;
sqlite> SELECT * FROM Auteurs;
1|Emile|Zola
2|J.K.|Rowling
3|Lewis|Caroll
4|J.R.R|Tolkien
6|Anne|Onyme
sqlite> INSERT INTO Auteurs(prenom,nom) VALUES ('Christopher','Paolini');
sqlite> SELECT * FROM Auteurs;
1|Emile|Zola
2|J.K.|Rowling
3|Lewis|Caroll
4|J.R.R|Tolkien
6|Anne|Onyme
7|Christopher|Paolini

Il n’a plus le numéro 5… D’où la remarque

Au moment où tu fais tes INSERTs (lignes 27 à 32), tu dois toujours indiquer les numéros …si ce sont des constantes que tu vas réutiliser.
Si tu laisses le système les générer pour toi, alors tu ne peux pas faire comme si tu sais quelles valeurs tu auras toujours. Ce n’est pas un problème ; il suffit de penser à récupérer ce numéro avant pour le mettre dans la requête

sqlite> SELECT id_auteur FROM Auteurs WHERE prenom='Christopher' AND nom='Paolini';
7
sqlite> SELECT * FROM Livres;
1|Thérèse Raquin|1
2|Harry Potter|2
3|Alice au pays des merveilles|3
4|Bilbo le hobbit|4
sqlite> INSERT INTO Livres(titre,auteur) SELECT 'Eragon',id_auteur FROM Auteurs WHERE prenom='Christopher' AND nom='Paolini' ;
sqlite> SELECT * FROM Livres;                                                   
1|Thérèse Raquin|1
2|Harry Potter|2
3|Alice au pays des merveilles|3
4|Bilbo le hobbit|4
5|Eragon|7
sqlite> .quit
$ 

Au passage, c’est la même problématique :

• Comment sélectionner les livres écrits par un auteur = sélectionner les livres correspondants à son id_auteur…
• Comment sélectionner les commandes passées par un client = sélectionner les commandes correspondants à son id_client…

Bonjour,

Merci beaucoup pour vos conseils !

Bonne année,

@flopy78