<jupyter_start><jupyter_text><img src="https://colab.research.google.com/assets/colab-badge.svg"      title="Open this file in Google Colab" alt="Colab"/>  Chinook sample database using SQLAlchemyin this exercise we're going to experiment with the [Chinook sample DB](http://www.sqlitetutorial.net/sqlite-sample-database/). while using SQLAlchemy module![Chinook](http://www.sqlitetutorial.net/wp-content/uploads/2015/11/sqlite-sample-database-color.jpg)First, run the code below to download the database locally<jupyter_code>### 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')<jupyter_output><empty_output><jupyter_text>Helper methodsthe helper methods below will help<jupyter_code>### 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)<jupyter_output><empty_output><jupyter_text>1. open the database1. open  the database using `sqlalchemy` module interface. create an engine object in a variable named `engine`2. call the `connect()` method to obtain a connection and place in a variable named `cur` > now run the code below to to run reflecton on the database, prepare classes that map to the database and create an orm session<jupyter_code>### 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()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10244648318042814, 'fraction_numerical': 0.00764525993883792, 'mean_word_length': 4.828507795100223, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 15, 'https://': 1, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '310901', 'license': 'MIT', 'n_tokens_mistral': 811, 'n_tokens_neox': 769, 'n_words': 245, 'path': 'content/_build/html/_sources/11_db_access/exercise/sqlalchemy_orm-questions.ipynb', 'repo_name': 'aviadr1/learn-python3', 'size': '2616'}