| import sqlite3 |
| from textwrap import dedent |
|
|
|
|
| class SqlQuery: |
| @staticmethod |
| def query_album(name: str) -> bool: |
| """Check if an album exists |
| |
| Args: |
| name (str): Name of the album |
| |
| Returns: |
| bool: True if the album exists, False otherwise |
| """ |
| conn = sqlite3.connect("data/chinook.db") |
| try: |
| cur = conn.cursor() |
|
|
| cur.execute("SELECT 1 FROM Album WHERE Title = ? LIMIT 1", (name,)) |
| return cur.fetchone() is not None |
| finally: |
| conn.close() |
|
|
| @staticmethod |
| def join_albums() -> list: |
| """Join the Album, Artist, and Track tables |
| |
| Returns: |
| list: |
| """ |
| conn = sqlite3.connect("data/chinook.db") |
| try: |
| cur = conn.cursor() |
|
|
| cur.execute( |
| dedent( |
| """\ |
| SELECT |
| t.Name AS TrackName, ( |
| SELECT a2.Title |
| FROM Album a2 |
| WHERE a2.AlbumId = t.AlbumId |
| ) AS AlbumName, |
| ( |
| SELECT ar.Name |
| FROM Artist ar |
| JOIN Album a3 ON a3.ArtistId = ar.ArtistId |
| WHERE a3.AlbumId = t.AlbumId |
| ) AS ArtistName |
| FROM |
| Track t |
| """ |
| ) |
| ) |
| return cur.fetchall() |
| finally: |
| conn.close() |
|
|
| @staticmethod |
| def top_invoices() -> list: |
| """Get the top 10 invoices by total |
| |
| Returns: |
| list: List of tuples |
| """ |
| conn = sqlite3.connect("data/chinook.db") |
| try: |
| cur = conn.cursor() |
|
|
| cur.execute( |
| dedent( |
| """\ |
| SELECT |
| i.InvoiceId, |
| c.FirstName || ' ' || c.LastName AS CustomerName, |
| i.Total |
| FROM |
| Invoice i |
| JOIN Customer c ON c.CustomerId = i.CustomerId |
| ORDER BY i.Total DESC |
| """ |
| ) |
| ) |
| return cur.fetchall()[:10] |
| finally: |
| conn.close() |
|
|
