<jupyter_start><jupyter_text>Base de données: interroger une base de données MySQL  Imports<jupyter_code>import os
import sqlite3
import zipfile
from pathlib import Path
import requests

# Créer un dossier data s'il n'existe pas encore
if not os.path.exists("../data"):
    os.mkdir("../data")<jupyter_output><empty_output><jupyter_text>Créer une base de données SQLite<jupyter_code># Créer un répertoire DB s'il n'existe pas encore
db_path = '../data/db'
if not os.path.exists(db_path):
    os.mkdir(db_path)
# Récupérer le fichier ZIP qui contient la DB de test
url = "https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip"
filename = url.split("/")[-1]
# Récupérer le fichier zip dans la RAM
response = requests.get(url)
# Ecrire le fichier sur le disque
with open(os.path.join(db_path, filename), 'wb') as f:
    f.write(response.content)
# Extraire le zip pour obtenir la db
with zipfile.ZipFile(os.path.join(db_path, filename), 'r') as zip_ref:
    zip_ref.extractall(db_path)<jupyter_output><empty_output><jupyter_text>Schéma de la base de données  Se connecter à la base de données<jupyter_code>conn = sqlite3.connect(os.path.join(db_path, "chinook.db"))
c = conn.cursor()<jupyter_output><empty_output><jupyter_text>Trouver l'identifiant de <NAME><jupyter_code>artist = '<NAME>'

query1 = f"""
    SELECT
        artistId
    FROM
        artists
    WHERE
        name = '{artist}';
"""
c.execute(query1)
artist_id = c.fetchone()[0]

print(artist_id)<jupyter_output>68<jupyter_text>Trouver les identifiants d'albums de <NAME><jupyter_code>query2 = f"""
    SELECT
        albumId
    FROM 
        albums 
    WHERE 
        artistId = '{artist_id}';
"""
albums_ids = []
for row in c.execute(query2):
    albums_ids.append(str(row[0]))

print(albums_ids)<jupyter_output>['48', '49', '157']<jupyter_text>Trouver les morceaux des albums de Miles Davis<jupyter_code>query3 = f"""
    SELECT
        name,
        albumId
    FROM
        tracks
    WHERE 
        albumId IN ({",".join(albums_ids)});
"""

songs = set()
for row in c.execute(query3):
    songs.add(row[0])

print(f"\n{len(songs)} distinct songs found:\n")
for song in sorted(songs):
    print(song)<jupyter_output>36 distinct songs found:

'Round Midnight
Black Satin
Blues For Pablo
Blues For Pablo (Alternate Take)
Bye Bye Blackbird
Compulsion
E.S.P.
Generique
I Don't Wanna Be Kissed (By Anyone But You)
I Don't Wanna Be Kissed (By Anyone But You) (Alternate Take)
<NAME> (Live)
Jeru
Lament
Little Church (Live)
Miles Ahead
Miles Runs The Voodoo Down
My Funny Valentine (Live)
My Ship
Nefertiti
New Rhumba
Now's The Time
Petits Machins (Little Stuff)
Portia
So What
Someday My Prince Will Come
Springsville
Springsville (Alternate Take)
Summertime
Tempus Fugit
The Duke
The Maids Of Cadiz
The Meaning Of The Blues
The Meaning Of The Blues/Lament (Alternate Take)
The Pan Piper
Time After Time
Walkin'<jupyter_text>En une seule requête...<jupyter_code>query4 = f"""
    SELECT
        tracks.name,
        tracks.albumId
    FROM
        artists
        JOIN albums ON artists.artistId = albums.artistId
        JOIN tracks ON albums.albumId = tracks.albumId
    WHERE 
        artists.name = '{artist}';
"""

songs = set()
for row in c.execute(query4):
    songs.add(row[0])

print(f"\n{len(songs)} distinct songs found:\n")
for song in sorted(songs):
    print(song)<jupyter_output>36 distinct songs found:

'Round Midnight
Black Satin
Blues For Pablo
Blues For Pablo (Alternate Take)
Bye Bye Blackbird
Compulsion
E.S.P.
Generique
I Don't Wanna Be Kissed (By Anyone But You)
I Don't Wanna Be Kissed (By Anyone But You) (Alternate Take)
<NAME> (Live)
Jeru
Lament
Little Church (Live)
Miles Ahead
Miles Runs The Voodoo Down
My Funny Valentine (Live)
My Ship
Nefertiti
New Rhumba
Now's The Time
<NAME> (Little Stuff)
Portia
So What
Someday My Prince Will Come
Springsville
Springsville (Alternate Take)
Summertime
Tempus Fugit
The Duke
The Maids Of Cadiz
The Meaning Of The Blues
The Meaning Of The Blues/Lament (Alternate Take)
The Pan Piper
Time After Time
Walkin'