le sujet - gondor

R EQUÊTES SQL
AVEC
P YTHON
1 Objectif
L’objectif de ce tp est :
• d’utiliser une application de création et de manipulation de données, offrant une interface graphique, notamment pour
créer une base de données simple, ne comportant pas plus de trois tables ayant chacune un nombre limité de colonnes.
• de lancer des requêtes sur une base de données de taille plus importante, comportant plusieurs tables, que les étudiants
n’auront pas eu à construire, à l’aide d’une application offrant une interface graphique
• d’enchaîner une requête sur une base de données et un traitement des réponses enregistrées dans un fichier.
2 Interroger une base de données Sqlite avec Python
Pour interroger une base de données avec Python, nous utiliserons le module sqlite3 :
import sqlite3
La commande bdd=sqlite3.connect(’/PATH/mabdd.sqlite’) permet de d’ouvrir la connexion à la base de
données mabdd.sqlite rangée dans le répertoire de chemin PATH via la variable bdd. Pour lancer des requêtes SQL sur la
base, il faut faire appel à un curseur cur, objet auquel on passe les commandes SQL en vue d’être exécutées.
db_hotel=sqlite3.connect('Hotel.sqlite') # connexion à la base ou création sinon
db_hotel.text_factory = lambda x: str(x, 'latin1') # pas de bol, elle n'est pas utf8
cur=db_hotel.cursor() # curseur pour les passer commande...
Client=cur.execute("""SELECT * FROM T_CLIENT WHERE CLI_NOM LIKE 'D%' ;""")
print(Client)
for c in Client:
print(c)
db_hotel.close()
Pour refermer l’accès, il suffit d’écrire bdd.close().
Q - 1 : Après avoir placer la base de donnée Hotel.sqlite dans votre répertoire et y avoir créé un fichier
Python , taper le code Python ci-dessus pour faire apparaître le résultat de la requête.
On s’aperçoit alors que si Client est utilisable, en revanche, print(Client) ne l’est pas. Il faut avoir recours à la
commande fetchall() :
cursor.execute("""SELECT * FROM T_CLIENT WHERE CLI_NOM LIKE 'D%' ;""")
Client=cursor.fetchall()
print(Client)
for c in Client:
print(c[2])
Q - 2 : Est ce que cela fonctionne ? Que se passe-t-il si on remplace fetchall() par fetchone() ou
fetchmany(4) ?
A partir de là, vous pouvez reprendre en Python les requêtes du tp précédent mais ce n’est pas très visuel. . .
LYCÉE C ARNOT (D IJON )
1/4
I NFO EN MPSI & PCSI - T P 2 - BDD
3 Créer une base de données et la modifier avec Sqlite
Jusqu’à présent, on n’avait pas modifié la base ; on ne faisait que lui poser des questions.
3.1 Créer et supprimer des tables
Si la base de données n’existe pas, la commande sqlite3.connect(’Musique.sqlite’) la crée.
Pour y avoir en même temps la connexion via bdd, écrire :
bdd=sqlite3.connect('Musique.sqlite')
bdd.close()
Pour créer une table, il faut lui donner son nom oeuvres et définir chacun de ses attributs avec son type.
bdd=sqlite3.connect('Musique.sqlite')
cur = bdd.cursor()
cur.execute("""CREATE TABLE oeuvres(
comp TEXT PRIMARY KEY,
titre TEXT,
duree INTEGER,
interpr TEXT
);""")
Hélas, rien ne se passe car il faut demander l’exécution des lignes. Pour le moment, elles sont stockées dans cur. Pour
appliquer, un ensemble d’instruction reste à ajouter la ligne bdd.commit().
Dans le code précédent, la clé primaire est comp. Elle est déclarée à la création de la base. Si des clés renvoient à
une autre relation relation via l’attribut attribut après le type, on tape alors en SQL REFERENCES relation
(attribut). Aussi, si une clé utilise plusieurs attributs, la déclaration de la clé se fait après les déclaration des attributs.
Par exemple, dans le schéma relationnel lycée du cours, nous avons :
CREATE TABLE e l e v e (
num_p
num_etudiant
lycee_origine
num_classe
PRIMARY KEY
)
INTEGER REFERENCES p e r s o n n e ( num_p ) ,
INTEGER ,
TEXT ,
INTEGER REFERENCES c l a s s e ( no ) ,
( num_p )
CREATE TABLE e n s e i g n e (
n u m _ c l a s s e INTEGER ,
num_prof INTEGER ,
m a t i e r e TEXT ,
PRIMARY KEY ( n u m _ c l a s s e , num_prof )
)
Q - 3 : Créer une base de donnée Musique.sqlite (ou Musique.db) contenant les relations oeuvres
et compositeurs de schéma relationnels suivant :
• oeuvres(comp (chaîne), titre (chaîne), duree (chaîne), interpr (chaîne))
• compositeurs(comp(chaîne), a_naiss (entier), a_mort(entier)) ou comp fait référence à l’attribut comp de la relation
oeuvres
LYCÉE C ARNOT (D IJON )
2/4
I NFO EN MPSI & PCSI - T P 2 - BDD
Pour supprimer une table :
cursor.execute("DROP TABLE compositeur;")
bdd.commit()
3.2 Insérer des données dans la table
Pour insérer un tuple dans la relation table, la commande SQL est :
INSERT INTO t a b l e VALUES ( . . .
, ....
, ....
);
Pour y insérer plusieurs tuples :
cur.execute("
cur.execute("
cur.execute("
cur.execute("
bdd.commit()
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
table
table
table
table
VALUES
VALUES
VALUES
VALUES
(
(
(
(
...
...
...
...
,
,
,
,
....
....
....
....
,
,
,
,
....
....
....
....
);")
);")
);")
);")
ou bien :
cur.execute("""
( ... ,
( ... ,
( ... ,
( ... ,
bdd.commit()
INSERT
.... ,
.... ,
.... ,
.... ,
INTO
....
....
....
....
table VALUES
),
),
),
);""")
ou encore :
eleves = [(3, "Hallyday", "Johnny", "5iemeB"), (4, "Mitchelle", "Eddy", "4ieme3"), (5, "Rivers"
cursor.executemany("""INSERT INTO eleve VALUES (?,?,?,?);""", eleves)
3.3 Mettre à jour et supprimer des enregistrements
On utilise les clauses UPDATE (resp. DELETE) de SQL pour mettre à jour la base (resp. supprimer des lignes)
E XEMPLE :
cursor.execute("""INSERT INTO eleve VALUES (?,?,?,?);""", (8, "andrix", "gimi", "4ieme3"))
bdd.commit()
Zut ! Raté :
cursor.execute("""UPDATE eleve SET name = ?, first_name=?
WHERE id = ? """,("Hendrix", "Jimmy", 8))
bdd.commit()
et pour supprimer :
cursor.execute("DELETE FROM eleve WHERE id = 8 ;")
ou complètement vider la table :
cursor.execute("DELETE FROM eleve;")
LYCÉE C ARNOT (D IJON )
3/4
I NFO EN MPSI & PCSI - T P 2 - BDD
3.4 Créer toi même ta base. . .
Q - 4 : A partir des deux fichiers oeuvres.csv et compositeur.csv remplir intelligemment la base de
donnée Musiques.sqlite.
LYCÉE C ARNOT (D IJON )
4/4
I NFO EN MPSI & PCSI - T P 2 - BDD