assignments/Ind_Assignment_3_Querying_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Individual Assignment 3 - Querying data\n",
    "In this assignment, students are going to focus on getting data out of a database, using a few different methods\n",
    "\n",
    "## Goal\n",
    "The goal of this assignment is to understand how to get data out of a database using SQL queries directly, pandas and also directly into our objects using SQLAlchemy \n",
    "\n",
    "## Deliverables\n",
    "1. A PDF of this notebook OR a link to the repository where the final version of the notebook is store.\n",
    "\n",
    "## Background\n",
    "Relational databases are super helpful way to store data and settings for applications.  They are efficient at managing space, easy to maintain and provide an extra layer of security to the data. Being able to load and save data to a database from a program brings a great deal of flexibility to our ability to automate.\n",
    "\n",
    "## Instructions\n",
    "**BEFORE YOU BEGIN**, if you haven't forked/cloned this from github you will need to ensure that you also get the `chinook.db` and `laptopsales.db` file as well as the entire `images` folder and put these in the same directory as the notebook.  See below.  If you have forked/cloned this repository, everything you need is already in the right place.\n",
    "\n",
    "```text\n",
    "assignments\n",
    "│   ├── Ind Assignment 3 - Querying Data.ipynb\n",
    "│   ├── images\n",
    "│   │   ├── a1-image-1.png\n",
    "│   │   ├── a1-image-2.png\n",
    "│   │   ├── a1-image.png\n",
    "│   │   ├── a3-invoices.png\n",
    "│   │   └── sqlite-sample-database-color.jpg\n",
    "|   ├── chinook.db\n",
    "│   └── laptopsales.db\n",
    "```\n",
    "For this assignment, you should work through this notebook.  Executing each of the cells and writing code in the various YOUR TURN cells in this notebook.\n",
    "\n",
    "You have a choice of either submitting a PDF of the notebook output, or a link to the repository where the completed notebook is stored."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1 - Querying data using SQL commands\n",
    "Since you already know quite a bit about querying a SQL database, this is the easiest way to get data from a database.\n",
    "\n",
    "The basic steps are \n",
    "1. *Connect to the database*. This may require some setup work depending on database type (SQLite, MS SQL, Teradata, etc).  Often times an intermediate piece of software called a database driver is required. This piece of software knows how to make a connection to the database server and send your SQL commands to the database and return the results to you. For SQLite, the driver is built-into Python and so there is no extra software to setup other than the standard Python libraries.\n",
    "2. *Create a cursor* that can be used to send commands to the server. A database cursor is a pointer that moves around the database and points to the data you want to retrieve from the database.\n",
    "3. *Execute a command*. Once the setup has been completed, we can finally execute a command. A command is the SQL statement you want to be sent to the database.  If the command returns data (as in a SELECT statement), then the cursor will point to the data that is to be returned (but keep in mind, only 1 row of data at a time).\n",
    "\n",
    "The next cell shows the basics of creating a connection, establishing a cursor and executing a command to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Set the name of the database file to be used for this exercise\n",
    "db_file = 'laptopsales.db'\n",
    "\n",
    "# Make a connection to the database\n",
    "cnn = sqlite3.connect(db_file)\n",
    "\n",
    "# Next make a cursor that can be used to run a query on the new connection\n",
    "cur = cnn.cursor()\n",
    "\n",
    "# See what tables are available in the database\n",
    "tables = cur.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "print(tables.fetchall())\n",
    "\n",
    "\n",
    "# get the first 10 records from the sales table\n",
    "result = cur.execute('SELECT * from sales LIMIT 10')\n",
    "\n",
    "# Get the names of the columns from a sqlite database\n",
    "names = list(map(lambda x: x[0], cur.description))\n",
    "print(names)\n",
    "\n",
    "# Print the first 10 records\n",
    "for row in result:\n",
    "    print(row)  \n",
    "\n",
    "# It's good practice to ensure that we close our connection to the database when we are done using it\n",
    "cnn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that each row of the query returns the result in a tuple.  A tuple similar to a list - it's a collection of values.  If we want to access a particular item in the results - we have to do so with an index.  Let's start by getting some data.\n",
    "\n",
    "### Fetching Data Methods Explained:\n",
    "- **`fetchall()`**: This method retrieves all the rows from the query result and loads them into memory. Be cautious when using this with large datasets as it can consume a lot of memory.\n",
    "- **`fetchone()`**: Retrieves the next row from the query result. Useful when you expect a single result or want to iterate through results one at a time.\n",
    "- **`fetchmany(size)`**: Retrieves the next 'size' number of rows from the query result. This is useful when you want to fetch data in manageable chunks rather than all at once or one at a time.\n",
    "\n",
    "In this example, we're using `fetchall()` to get the first 10 records from the 'sales' table. Remember that the 'LIMIT 10' in the SQL query also restricts the output to 10 rows.\n",
    "\n",
    "We are also using `fetchmany(5)` this will limit the results to just the first 5 even though the query will have access to 10 rows of data.  If we run `fetchmany(5)` again, we would get the second 5 results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We have to reopen the database and create a new cursor to run queries on it\n",
    "cnn = sqlite3.connect(db_file)\n",
    "cur = cnn.cursor()\n",
    "\n",
    "# We can also use the fetchall() method to get all the results at once\n",
    "results = cur.execute('SELECT * from sales LIMIT 10').fetchall()\n",
    "print(results)\n",
    "    \n",
    "# We can also use the fetchmany() method to get a specific number of results\n",
    "results = cur.execute('SELECT * from sales LIMIT 10').fetchmany(5)\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now lets get just a single result and print out the SalesID, and the Sale Date.\n",
    "results = cur.execute('SELECT * from sales LIMIT 10').fetchone()\n",
    "print(results)\n",
    "# Print the SalesID and Sale Date.  The SalesID is the first column, and the Sale Date is the 17th column\n",
    "print(f'SalesID: {results[0]}, Sale Date: {results[16]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding Database Cursors and Data Fetching\n",
    "\n",
    "When you execute a SQL query, the database doesn't immediately return all the data. Instead, it returns a **cursor** object. Think of the cursor as a pointer that navigates through the result set returned by a query. The cursor starts before the first row of the result set, and you can think of it as being positioned \"above\" the first row initially.\n",
    "\n",
    "#### Cursor Movements\n",
    "\n",
    "- **`fetchone()`**: When you call this method, the cursor moves down one row and returns that row's data. If you call `fetchone()` again, it will move down another row and return that row's data, and so on.\n",
    "  \n",
    "- **`fetchmany(size)`**: Calling this moves the cursor down by `size` number of rows and returns those rows as a list. For example, if you call `fetchmany(3)`, the cursor moves down three rows and returns those three rows.\n",
    "\n",
    "- **`fetchall()`**: This method moves the cursor to the end of the table, fetching all remaining rows and returning them as a list. Note that this could be memory-intensive for large result sets.\n",
    "\n",
    "#### Example\n",
    "\n",
    "Imagine your query returns a result set like this, where the arrow (`-->`) indicates the cursor's position:\n",
    "```\n",
    "    (Row 1)\n",
    "--> (Row 2)\n",
    "    (Row 3)\n",
    "```\n",
    "\n",
    "After calling `fetchmany(2)`:\n",
    "```\n",
    "    (Row 1)\n",
    "    (Row 2)\n",
    "--> (Row 3)\n",
    "```\n",
    "\n",
    "After calling `fetchall()`:\n",
    "```\n",
    "    (Row 1)\n",
    "    (Row 2)\n",
    "    (Row 3)\n",
    "--> (End)\n",
    "```\n",
    "\n",
    "\n",
    "#### Important Note\n",
    "- Once the cursor reaches the end, subsequent calls to `fetchone()` or `fetchmany()` will return an empty result. You would need to re-execute the query to reset the cursor position.\n",
    "\n",
    "Remember, the cursor is not just a Python concept; it's a database feature. So, when you execute a query, the cursor is maintained on the database server, not just in your Python script."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN - Queries\n",
    "Now that we know which columns are available to us we can query the database let's run a few queries.\n",
    "Answer the questions in the following cells.  Ensure that your code and the result are available before saving to PDF.\n",
    "\n",
    "As a reference, here are the column names:\n",
    "```\n",
    "sale_id\n",
    "Configuration\n",
    "Customer Postcode\n",
    "Store Postcode\n",
    "Retail Price\n",
    "Screen Size (Inches)\n",
    "Battery Life (Hours)\n",
    "RAM (GB)\n",
    "Processor Speeds (GHz)\n",
    "Integrated Wireless?\n",
    "HD Size (GB)\n",
    "Bundled Applications?\n",
    "customer X\n",
    "customer Y\n",
    "store X\n",
    "store Y\n",
    "sale_date\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "notebookRunGroups": {
     "groupValue": "1"
    }
   },
   "outputs": [],
   "source": [
    "# The following steps are provided as a reminder of how to execute the query\n",
    "\n",
    "# Open the database connection\n",
    "# Create the cursor for which to execute the query\n",
    "# Execute the query\n",
    "# Print the results\n",
    "#  HINT: Since we are only looking for a single value - fetchone() is a good method to use\n",
    "# Close the connection\n",
    "\n",
    "# Q1: What is the total revenue from sales in the sales table? (the answer should be 144373314.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "notebookRunGroups": {
     "groupValue": "1"
    }
   },
   "outputs": [],
   "source": [
    "# Q2: Sum the sales by Store Postcode include the postcode in the results\n",
    "# HINT: Now we are looking for multiple values - fetchall() is a good method to use"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using the data from the queries\n",
    "In all the examples the data comes back from the query as a tuple.  This means that we are getting all the values in a sort of collection of variables.  We can access the data in tuple just like it were a list.\n",
    "\n",
    "Now, \n",
    "1. Create a class (LaptopSale) that has a property for each of the following columns in the database\n",
    "   - SaleId, Configuration, SaleDate, HasIntWireless, RetailPrice (you are welcome to rename these property names however you see fit)\n",
    "2. Query the database to get the first 20 items in the database, \n",
    "3. Create an object from each one and put it in a list\n",
    "4. Then loop through the list you created\n",
    "   * Print the SaleId, Configuration, HasIntWireless, RetailPrice and SaleDate for each items in list\n",
    "\n",
    "***HINT:*** Here's code you can use for the query statement.  Make sure to copy the `\"`\n",
    "```python\n",
    "   \"SELECT sale_id, Configuration, sale_date, [Integrated Wireless?], [Retail Price] FROM sales LIMIT 20\"\n",
    "```\n",
    "as in\n",
    "```python\n",
    "   cur.execute(\"SELECT sale_id, Configuration, sale_date, [Integrated Wireless?], [Retail Price] FROM sales LIMIT 20\")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "notebookRunGroups": {
     "groupValue": "1"
    }
   },
   "outputs": [],
   "source": [
    "# Define the LaptopSale class\n",
    "# This class will be used to store the results of the query\n",
    "\n",
    "# Open the database connection\n",
    "# Create the cursor for which to execute the query\n",
    "# Execute the query\n",
    "\n",
    "# Create an empty list to store the results\n",
    "# Loop through the results and create a new LaptopSale object for each row\n",
    "# Add the new LaptopSale object to the list\n",
    "\n",
    "# Close the connection\n",
    "\n",
    "# Loop through the list of objects\n",
    "# Print the object's Sale Id, Configuration and Sale date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2 - Using pandas to connect to the database\n",
    "If we are just looking to grab some data from a database and use it for analytics especially, then the [pandas](https://pandas.pydata.org/) library is a great choice.\n",
    "\n",
    "To get data from SQL using `pandas` all we need to do is create the connection and execute the query\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create connection\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "# Set the name of the database file to be used for this exercise\n",
    "db_file = 'laptopsales.db'\n",
    "\n",
    "# Make a connection to the database\n",
    "cnn = sqlite3.connect(db_file)\n",
    "\n",
    "data = pd.read_sql_query(\"SELECT * FROM sales LIMIT 10\", cnn)\n",
    "\n",
    "# In Jupyter, the last line of a cell will automatically be printed out without having to call the print() function\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that our data is in a dataframe we can do all the things we know how to do with pandas already - like aggregate, filter, group and slice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the average battery life\n",
    "mean_battery_life = data['Battery Life (Hours)'].mean()\n",
    "print(f'Average Battery Life: {mean_battery_life} hours')\n",
    "\n",
    "# Get all sales where the laptop has integrated wireless\n",
    "wireless_sales = data[data['Integrated Wireless?'] == 'Yes']\n",
    "print(f'wireless sales count: {len(wireless_sales)}')\n",
    "\n",
    "# Price difference between items with and without integrated wireless\n",
    "wireless = data[data['Integrated Wireless?'] == 'Yes']['Retail Price'].mean()\n",
    "no_wireless = data[data['Integrated Wireless?'] == 'No']['Retail Price'].mean()\n",
    "print(f'Price difference between items with and without integrated wireless: {wireless - no_wireless}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN - Using pandas to query data source\n",
    "Now that you have seen how to connect to a database using pandas, we are going to move to a new database with a few more tables.  You can read about the chinook example [here](https://www.sqlitetutorial.net/sqlite-sample-database/).\n",
    "\n",
    "![chinook database](images/sqlite-sample-database-color.jpg)\n",
    "\n",
    "Complete the following queries and show your results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create connection\n",
    "# Write a query that will return the artist name, album title, and genre name into a dataframe\n",
    "# Close the connection\n",
    "# Show the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dynamic Query\n",
    "For this next exercise you need to ask the user for a band name and then use this name to get all the albums for that band into a dataframe.\n",
    "\n",
    "***HINTS:***\n",
    "1. Consider building the query with string manipulation\n",
    "2. It might be difficult to get the band name spelled exactly right, so use the SQL operator `LIKE` to help with getting close\n",
    "3. To get user input you will use the `input()` function\n",
    "4. Review [this lesson](https://www.py4analytics.info/book/lessons/250-Database_Connections.html#dynamic-queries) on dynamic queries if you need some additional help"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "artist_name = input(\"What band are you interested in?  Put a partial name if you don't know the full name: \")\n",
    "# Your code for the dynamic query goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 3 - Using an SQLAlchemy (Object Relational Mapping)\n",
    "So far you have learned how to work with databases by querying directly, using dynamic queries and getting the data back in the form of a dataframe.  What would be super handy in some cases is to be able to map database objects directly onto Python objects. Being able to treat database tables like other Python objects is very valuable.  Expecting each row to be represented by a particular Python object allows us to access the columns as properties of the class and handles the insert/update/delete functions for us without having to depend on fragile SQL strings.  (Fragile SQL strings means that we are putting code logic into a string which isn't checked by Python before being run, so if there is a change in the database, we have to search through strings in our code to update the logic rather than being able to depend on refactoring tools provided by our IDEs.  *Are you glad you asked what 'fragile SQL strings' means?*)  The library most often used to do ORM (object relational mapping) is a library called SQLAlchemy.\n",
    "\n",
    "Going back to our `chinook` dataset.  It would be helpful to be able to consider customers as Python objects, if we could query for them and then use the objects to build our front-end forms or maybe just export them to a semi-strucured file format like json or xml for transport.  We'll start with an example and then break it down.\n",
    "\n",
    "**NOTE: If you haven't installed [SQLAlchemy](https://www.sqlalchemy.org/) yet you are going to need to do so.  You can reference this site for the [installation guide](https://docs.sqlalchemy.org/en/20/intro.html#installation).  Try not to get to mired in the details of the rest of the library just yet**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import sqlalchemy\n",
    "# install SQLAlchemy using pip install SQLAlchemy\n",
    "from sqlalchemy import create_engine, text\n",
    "\n",
    "if sqlalchemy.__version__ < '2.0.0':\n",
    "    raise ValueError('Please upgrade your version of SQLAlchemy to 2.0.0 or greater')\n",
    "\n",
    "engine = create_engine('sqlite:///chinook.db')\n",
    "with engine.connect() as conn:\n",
    "    # Get the top 10 albums\n",
    "    results = conn.execute(text(\"SELECT * FROM albums LIMIT 10\")).fetchall()\n",
    "\n",
    "for album in results:\n",
    "    print(album)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far we see we can use SQLAlchemy in the same way we used connections to the SQLite3 database already.  Let's take it one step further.\n",
    "\n",
    "**NOTE: It may be helpful to review the [ORM Quick Start](https://docs.sqlalchemy.org/en/20/orm/quickstart.html) provided by SQLAlchemy in conjunction with the following**\n",
    "\n",
    "**NOTE2: If you are not using the UofA Jupyter environment you may need to install SQLAlchemy.  See the [installation guide](https://docs.sqlalchemy.org/en/20/intro.html#installation)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import List, Optional \n",
    "from sqlalchemy import Integer, String, DateTime, Float, ForeignKey\n",
    "from sqlalchemy.orm import DeclarativeBase\n",
    "from sqlalchemy.orm import Mapped\n",
    "from sqlalchemy.orm import mapped_column\n",
    "from sqlalchemy.orm import Session, relationship\n",
    "from sqlalchemy import select\n",
    "\n",
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "# Notice we are naming the class in the singular, even though the table name is plural\n",
    "# This is because each instance of the class represents a single row in the table\n",
    "class Invoice(Base):\n",
    "    __tablename__ = 'invoices'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('InvoiceId', primary_key=True)\n",
    "    invoiceDate = mapped_column(DateTime)\n",
    "    billingAddress = mapped_column(String)\n",
    "    billingCity= mapped_column(String)\n",
    "    billingState= mapped_column(String)\n",
    "    billingCountry = mapped_column(String)\n",
    "    billingPostalCode = mapped_column(String)\n",
    "    total= mapped_column(Float)\n",
    "\n",
    "    # Create a list to hold the invoice details associated with this invoice\n",
    "    invoice_items: Mapped[List['Invoice_Item']] = relationship(back_populates='invoice')\n",
    "\n",
    "    # This is a special method that is called when we print an instance of the class\n",
    "    def __repr__(self):\n",
    "        return f'Invoice(id={self.id}, invoiceDate={self.invoiceDate}, billingAddress={self.billingAddress}, billingCity={self.billingCity}, billingState={self.billingState}, billingCountry={self.billingCountry}, billingPostalCode={self.billingPostalCode}, total={self.total})'\n",
    "    \n",
    "\n",
    "# A class to represent the invoice items table\n",
    "class Invoice_Item(Base):\n",
    "    __tablename__ = 'invoice_items'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('InvoiceLineId', primary_key=True)    \n",
    "    # This provides us a link back to the invoice table\n",
    "    # Notice we are using the more Python appropriate invoice_id instead of InvoiceId which is the column name in the table\n",
    "    invoice_id : Mapped[int]= mapped_column('InvoiceId', ForeignKey('invoices.InvoiceId'))\n",
    "    invoice : Mapped[Invoice]= relationship('Invoice', back_populates='invoice_items')\n",
    "    \n",
    "    track_id : Mapped[int]= mapped_column('trackId', ForeignKey('tracks.TrackId'))\n",
    "    track: Mapped['Track']= relationship('Track')\n",
    "    \n",
    "    unit_price : Mapped[float]= mapped_column('unitPrice')\n",
    "    quantity : Mapped[int]= mapped_column('quantity')\n",
    "\n",
    "# Let's add a few more classes so we can get all the details of the orders from the database\n",
    "class Track(Base):\n",
    "    __tablename__ = 'tracks'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('TrackId', primary_key=True)\n",
    "    name: Mapped[str]= mapped_column('Name')\n",
    "    \n",
    "    # Define the album id column and link it to the album table\n",
    "    album_id: Mapped[int]= mapped_column('AlbumId', ForeignKey('albums.AlbumId'))\n",
    "    album: Mapped['Album']= relationship('Album')\n",
    "    \n",
    "    # Define the media type id column and link it to the media type table\n",
    "    media_type_id: Mapped[int]= mapped_column('MediaTypeId', ForeignKey('media_types.MediaTypeId'))\n",
    "    media_type : Mapped['MediaType']= relationship('MediaType')\n",
    "    \n",
    "    # Define the genre id column and link it to the genre table\n",
    "    genre_id: Mapped[int]= mapped_column('GenreId', ForeignKey('genres.GenreId'))\n",
    "    genre : Mapped['Genre']= relationship('Genre')\n",
    "    \n",
    "    composer: Mapped[str]= mapped_column('Composer')\n",
    "    milliseconds: Mapped[int]= mapped_column('Milliseconds')\n",
    "    bytes: Mapped[int]= mapped_column('Bytes')\n",
    "    unit_price: Mapped[float]= mapped_column('UnitPrice')\n",
    "    \n",
    "# Class that maps to MediaType\n",
    "class MediaType(Base):\n",
    "    __tablename__ = 'media_types'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('MediaTypeId', primary_key=True)\n",
    "    name: Mapped[str]= mapped_column('Name')\n",
    "\n",
    "# Class that maps to Genre\n",
    "class Genre(Base):\n",
    "    __tablename__ = 'genres'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('GenreId', primary_key=True)\n",
    "    name: Mapped[str]= mapped_column('Name')\n",
    "\n",
    "# Class that maps to Album\n",
    "class Album(Base):\n",
    "    __tablename__ = 'albums'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('AlbumId', primary_key=True)\n",
    "    title: Mapped[str]= mapped_column('Title')\n",
    "\n",
    "    artist_id: Mapped[int]= mapped_column('ArtistId', ForeignKey('artists.ArtistId'))\n",
    "    artist: Mapped['Artist']= relationship('Artist')\n",
    "\n",
    "# Class that maps to Artist\n",
    "class Artist(Base):\n",
    "    __tablename__ = 'artists'\n",
    "    \n",
    "    id: Mapped[int]= mapped_column('ArtistId', primary_key=True)\n",
    "    name: Mapped[str]= mapped_column('Name')\n",
    "\n",
    "session = Session(engine)\n",
    "result = session.query(Invoice).limit(10)\n",
    "for invoice in result:\n",
    "    print(invoice)\n",
    "\n",
    "# We want to ensure that we are always closing the session after we are done using it\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's going on here. In the cell above, we create a class called ```Invoice``` which maps directly to the ```Invoice``` table in the database.  SQLAlchemy knows how to convert *objects* of type ```Invoice``` into rows in the database.  This saves us the effort and fragility of updating custom sql statements.\n",
    "\n",
    "What's more, we can establish the relationship between ```invoice_items``` and ```invoices```, meaning we can easy find the ```invoice``` related to an ```invoice_items``` and likewise, we have a list of ```invoice_items``` represented on an ```invoice```.\n",
    "\n",
    "Furthermore, we can leverage Python to do many of the same things we could do in SQL using SQL like functions.  Take a look at the next cell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new session\n",
    "session = Session(engine)\n",
    "\n",
    "# Get the first invoice using the id\n",
    "invoice_1 = session.query(Invoice).filter(Invoice.id == 1).first()\n",
    "print (f'Invoice 1: {invoice_1.invoiceDate}')\n",
    "\n",
    "# Get the first 10 invoices, using something more like SQL\n",
    "statement = select(Invoice).where(Invoice.id <= 10)\n",
    "for invoice in session.scalars(statement):\n",
    "    print(f'Invoice {invoice.id}: {invoice.invoiceDate}')\n",
    "\n",
    "# Close the session\n",
    "session.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The real value of the ORM (object relational model) mode of SQLAlchemy is when there are relationships between items in the database.  For instance, if we look closer at the `invoice` and `invoice_items` tables we see they are related.  It would be super handy to be able to load up our invoice_items when we load up a specific invoice.  \n",
    "\n",
    "<p align=\"center\"><img src=\"./images/a3-invoices.png\" alt=\"close-up of the invoices and invoice_items tables\"/>\n",
    "</p>\n",
    "<span style=\"color:red\">***BEWARE: The image shows that the key to the ```invoice_items``` table is called ```InvoiceItemId``` in reality it is called ```InvoiceLineId```.***</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new session\n",
    "session = Session(engine)\n",
    "\n",
    "# Get the first invoice using the id\n",
    "invoice_1 = session.query(Invoice).filter(Invoice.id == 1).first()\n",
    "print (f'Invoice 1: {invoice_1.invoiceDate}')\n",
    "    \n",
    "invoice_items = invoice_1.invoice_items\n",
    "print(f'Invoice 1 has {len(invoice_items)} invoice items')  \n",
    "for invoice_item in invoice_items:\n",
    "    print(f'Invoice Item {invoice_item.id}: {invoice_item.unit_price}, {invoice_item.quantity}')\n",
    "\n",
    "# Close the session to ensure that we are not keeping a connection open\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So just by querying the invoice we also have access to the details of the invoice. Since we are interested in all the details there is no need to create complex join statements like we would before to get the information we needed.  \n",
    "\n",
    "Now, let's add the `Track` information to round out the picture.  We are going to skip `playlists` and `customers` because they introduce some additional complexity which isn't as easy to map, but which definitely can be done.  The next cell demonstrates the pattern we've seen earlier where we define the *id* field as a foreign key and then immediately follow this with a property which holds (one or more) of the objects represented by the row in the parent table.  For instance,\n",
    "\n",
    "```python\n",
    "    media_type_id: Mapped[int]= mapped_column('MediaTypeId', ForeignKey('media_types.MediaTypeId'))\n",
    "    media_type : Mapped['MediaType']= relationship('MediaType')\n",
    "```\n",
    "\n",
    "SQLAlchemy will get the media_type_id for us, but also, it is smart enough to load an object of type `MediaType` for us.  Allowing us to get the useful part, the name of the media_type rather quickly\n",
    "```python\n",
    "    track.media_type.name\n",
    "```\n",
    "*While the order of the property definitions doesn't matter, the code is easier to read if these two lines are kept together.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's look for the artist name and album for the first 10 albums in the database\n",
    "session = Session(engine)\n",
    "statement = select(Album).limit(10)\n",
    "for album in session.scalars(statement):\n",
    "    print(f'Album: {album.title}, Artist: {album.artist.name}')\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YOUR TURN - Querying data using SQLAlchemy\n",
    "Now we are going to use a bit of what we know to get some information from the database using SQLAlchemy.  If you haven't done so a quick review of the [ORM Quickstart](https://docs.sqlalchemy.org/en/20/orm/quickstart.html) for SQLAlchemy may be helpful.  Also, you may find the [ORM Querying Guide](https://docs.sqlalchemy.org/en/20/orm/queryguide/index.html) helpful.\n",
    "\n",
    "- Always start by writing in English (in comments) what you want to complete prior to writing code.\n",
    "- Refer to the previous cells for examples\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the track name for the first 10 tracks in the database\n",
    "\n",
    "# Create the session\n",
    "# Query the tracks\n",
    "# For each track, print the track name\n",
    "# Close the session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the track name and genre name for the first 10 tracks in the database\n",
    "\n",
    "# Create the session\n",
    "# Query the tracks\n",
    "# For each track, print the track name and genre name\n",
    "# Close the session\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}