### useful: download and extract chinook sample DB
import urllib.request
import zipfile
from functools import partial
import os

chinook_url = 'http://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip'
if not os.path.exists('chinook.zip'):
    print('downloading chinook.zip ', end='')
    with urllib.request.urlopen(chinook_url) as response:
        with open('chinook.zip', 'wb') as f:
            for data in iter(partial(response.read, 4*1024), b''):
                print('.', end='', flush=True)
                f.write(data)

zipfile.ZipFile('chinook.zip').extractall()
assert os.path.exists('chinook.db')

### useful: functions for displaying results from sql queries using pandas
from IPython.display import display
import pandas as pd

def sql(query):
    print()
    print(query)
    print()

def get_results(query):
    global engine
    q = query.statement if isinstance(query, sqlalchemy.orm.query.Query) else query
    return pd.read_sql(q, engine)

def display_results(query):
    df = get_results(query)
    display(df)
    sql(query)

import sqlalchemy
from sqlalchemy import create_engine
engine = create_engine('sqlite:///chinook.db')
cur = engine.connect()

### useful: extract classes from the chinook database
metadata = sqlalchemy.MetaData()
metadata.reflect(engine)

## we need to do this once
from sqlalchemy.ext.automap import automap_base

# produce a set of mappings from this MetaData.
Base = automap_base(metadata=metadata)

# calling prepare() just sets up mapped classes and relationships.
Base.prepare()

# also prepare an orm session
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

engine.table_names()

from sqlalchemy import select 
tracks = Base.classes['tracks']

# using expressions
query = select([tracks]).limit(3)
display_results(query)

# using orm
results = session.query(tracks).limit(3)
display_results(results)

from sqlalchemy import join
albums = Base.classes['albums']

# using expression language
query = select([tracks.Name, albums]).select_from(join(tracks, albums)).limit(20)
display_results(query)

# using orm
results = session.query(tracks.Name, albums).select_from(tracks).join(albums).limit(20)
display_results(results)

items = Base.classes['invoice_items']

# print out the first 10 track sales from the invoice_items table
query = select([items]).limit(10)
display_results(query)

# for these first 10 sales, print what are the names of the track sold, and the quantity sold
query = select([tracks.Name, items.Quantity]).select_from(join(items, tracks)).limit(10)
display_results(query)    

# orm
result = session.query(tracks.Name, items.Quantity).select_from(items).join(tracks).limit(10)
display_results(result)

from sqlalchemy import func, column

query = select([tracks.Name, func.sum(items.Quantity).label('sold')]) \
            .select_from(join(tracks, items)) \
            .group_by(tracks.TrackId) \
            .order_by(column('sold').desc()) \
            .limit(10)

display_results(query)

# orm
results = session.query(tracks.Name, func.sum(items.Quantity).label('sold')) \
            .select_from(tracks) \
            .join(items) \
            .group_by(tracks.TrackId) \
            .order_by(column('sold').desc()) \
            .limit(10)

display_results(results)

artists = Base.classes['artists']

# solution using sqlalchemy expressions
query = select([artists.Name, func.sum(items.Quantity).label('sold')]) \
    .select_from(join(items, tracks).join(albums).join(artists)) \
    .group_by(artists.ArtistId) \
    .order_by(column('sold').desc()) \
    .limit(10)
display_results(query)

# solution using sqlalchemy orm
query = session.query(func.sum(items.Quantity).label('sold'), artists.Name.label('Artist')) \
    .select_from(items) \
    .join(tracks) \
    .join(albums) \
    .join(artists) \
    .group_by(artists.ArtistId) \
    .order_by(column('sold').desc()) \
    .limit(10)
display_results(query)