File size: 2,415 Bytes
96d64c6 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | 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()
|