🎶 RhythmiQuery

RhythmiQuery utilise la base de données musicale Chinook pour effectuer diverses requêtes SQL et visualiser les résultats.
SQL
Python
Auteur·rice

Corentin DUCLOUX

Date de publication

20/03/2024

from itables import init_notebook_mode
from pathlib import Path
import polars as pl
import sqlite3

init_notebook_mode(all_interactive=True)
conn = sqlite3.connect("rhytmiquery/chinook.db")
Code de la fonction 
def table_query_results(query_file: str, conn: sqlite3.Connection):
    """
    Exécute une requête SQL à partir d'un fichier et retourne les résultats sous forme de tableau.

    Args:
        query_file (str): Chemin vers le fichier contenant la requête SQL.
    """
    query_path = Path(query_file)
    with query_path.open("r", encoding="utf-8") as file:
        query = file.read()

    results = conn.execute(query)
    column_names = [col[0] for col in results.description]
    dataframe = pl.DataFrame(results.fetchall(), schema=column_names)

    return dataframe

ERD

ARTISTSArtistIDINTEGERPKNameVARCHARALBUMSArtistIDINTEGERFKAlbumIDINTEGERPKTitleVARCHARTRACKSTrackIDINTEGERPKNameVARCHARAlbumIDINTEGERFKMediaTypeIDINTEGERFKGenreIDINTEGERFKComposerVARCHARMillisecondsINTEGERBytesINTEGERUnitPriceNUMERICINVOICE_ITEMSInvoiceLineIDINTEGERPKInvoiceIDINTEGERFKTrackIDINTEGERFKUnitPriceNUMERICQuantityINTEGERPLAYLIST_TRACKPlaylistIDINTEGERFKTrackIDINTEGERFKMEDIA_TYPESMediaTypeIDINTEGERPKNameVARCHARPLAYLISTSPlaylistIDINTEGERPKNameVARCHARGENRESGenreIDINTEGERPKNameVARCHARINVOICESInvoiceIDINTEGERPKCustomerIDINTEGERFKInvoiceDateDATETIMEBillingAddressVARCHARBillingStateVARCHARBillingCountryVARCHARBillingPostalCodeVARCHARTotalNUMERICCUSTOMERSCustomerIDINTEGERPKFirstNameVARCHARLastNameVARCHARCompanyVARCHARAddressVARCHARCityVARCHARStateVARCHARCountryVARCHARPostalCodeVARCHARPhoneVARCHARFaxVARCHAREmailVARCHARSupportRepIDINTEGERFKEMPLOYEESEmployeeIDINTEGERPKLastNameVARCHARFirstNameVARCHARTitleVARCHARReportsToINTEGERFKBirthDateDATETIMEHireDateDATETIMEAddressVARCHARCityVARCHARStateVARCHARCountryVARCHARPostalCodeVARCHARPhoneVARCHARFaxVARCHAREmailVARCHARArtistIDAlbumIDTrackIDPlaylistIDMediaTypeIDGenreIDTrackIDInvoiceIDCustomerID

Requete 1

SELECT
    Name,
    Composer
FROM
    Tracks
ORDER BY
    Name
LIMIT
    10
table_query_results("rhytmiquery/queries/tracks.sql", conn=conn)

Requete 2

SELECT
    alb.Title AS Album,
    art.Name AS Artist
FROM
    albums AS alb
    INNER JOIN artists AS art ON art.artistID = alb.artistID
ORDER BY
    artist;
table_query_results("rhytmiquery/queries/test.sql", conn=conn)

Nombre d’albums par artiste

SELECT
    art.Name AS ArtistName,
    COUNT(alb.albumID) AS NumberOfAlbums
FROM
    albums AS alb
    INNER JOIN artists AS art ON alb.artistID = art.artistID
GROUP BY
    ArtistName
ORDER BY
    COUNT(alb.albumID) DESC
table_query_results("rhytmiquery/queries/albums_by_artist.sql", conn=conn)
Retour au sommet