orm_practice.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "orm_practice.ipynb",
      "private_outputs": true,
      "provenance": [],
      "collapsed_sections": [
        "cB1iky0ekfcZ",
        "xwjOj9CrHBn5",
        "DPWm9WYRQ8ek",
        "ko-hTfHq1VSZ",
        "ATsLK53bvsWU",
        "X5OYRmld2lvc",
        "uN_63iRze-Rn",
        "j93H1v9pfFUa",
        "IOC77fGJh1Wv"
      ],
      "toc_visible": true,
      "authorship_tag": "ABX9TyM+fdQExjqO5Tp7uhYj8eK7",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/LUMC/EfDS_RelDB_SQL/blob/main/orm_practice.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Object Relational Mapper"
      ],
      "metadata": {
        "id": "7QfhAsFJDKAt"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Summary"
      ],
      "metadata": {
        "id": "lMXTpcDAqsiD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### The main goal"
      ],
      "metadata": {
        "id": "g97NqHqxf-GD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The goal of this session is to learn how to provide a Python object oriented interface to a database.  \n",
        "Since this is a complex task, specialised libraries help to provide such functionality.  \n",
        "Here, we will use a part of the `SQLAlchemy` library called [Object Relational Mapper](https://docs.sqlalchemy.org/en/14/orm/index.html) (ORM).\n",
        "\n",
        "*Note:* [The PYSheet cheatsheet](https://www.pythonsheets.com/notes/python-sqlalchemy.html) is useful once you understand the basic SQLAlchemy and Object Relational Mapper concepts.\n"
      ],
      "metadata": {
        "id": "q0l7Hj4HEDEI"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Overview of the steps"
      ],
      "metadata": {
        "id": "loKiyFbNnkno"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This lecture describes a growing code and is intended to be followed sequentially. Subsequent steps have the following goals:\n",
        "- Create a class corresponding to a single database table (relations are ignored).\n",
        "- Query the database and get object(s) of the class. Build simple and more complex queries.\n",
        "- Extend the class with a method for nicer content printing.\n",
        "- Create another class for another related table. Learn how do declare foreign keys and add methods representing relations.\n",
        "- In a new transaction add new content to the database. Commit.\n",
        "- In a new transaction show the newly added content."
      ],
      "metadata": {
        "id": "Wqiz1HMYnp42"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Some details"
      ],
      "metadata": {
        "id": "2Xka8HEtseKT"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Below there is (again) the diagram from [the SQLite Tutorial](https://www.sqlitetutorial.net) of the `chinook` database.\n",
        "\n",
        "![chinook scheme](https://www.sqlitetutorial.net/wp-content/uploads/2015/11/sqlite-sample-database-color.jpg)\n",
        "\n",
        "We will implement classes providing access to a small part of the database:\n",
        "- It should be possible to have classes like `Album` or `Track`.\n",
        "- The class `Album` should have a field like `title`.\n",
        "- The class `Album` should have a method `tracks` which returns respective `Track` objects for \"the current\" `Album` object.\n",
        "- When a field is changed, the database should get updated."
      ],
      "metadata": {
        "id": "wpHEHn7wsA76"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Preparation"
      ],
      "metadata": {
        "id": "e2b3jP7s3VQF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "The following tools/sources will be used and they need to get installed:\n",
        "- `chinook.db` is the database (a file in `SQLite` database format)\n",
        "- `SQLAlchemy` is the Python library which:\n",
        "    - provides unified SQL access to databases of many formats, including `SQLite`\n",
        "    - provides Object Relational Mapper functionality\n"
      ],
      "metadata": {
        "id": "7fXJZpD5kYVb"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Download of the database"
      ],
      "metadata": {
        "id": "cB1iky0ekfcZ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### (For Jupyter or Colab) Download chinook.zip from SQLiteTutorial and unpack it"
      ],
      "metadata": {
        "id": "GnTlYl8fLkDL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following lines have been tested in Jupyter (works) and Colab (mostly works):"
      ],
      "metadata": {
        "id": "RmZYU5BSOoWN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import urllib.request    # needed for download of the example database\n",
        "import shutil            # needed for unziping of the example database\n",
        "import os                # for checking existence/removing of a file\n",
        "\n",
        "if not os.path.isfile(\"chinook.db\"):\n",
        "    urllib.request.urlretrieve(\"https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip\", \"chinook.zip\")\n",
        "    shutil.unpack_archive(\"chinook.zip\")\n",
        "    os.remove(\"chinook.zip\")"
      ],
      "metadata": {
        "id": "wScMXlM-CLSK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### (For Colab only) Download chinook.zip from your Google Drive to Colab temp drive"
      ],
      "metadata": {
        "id": "MGiWWEv1L8Bo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you use Google Colab and the above method fails you may first manually download the `chinook.zip` file to your Google drive and then try this approach:"
      ],
      "metadata": {
        "id": "TBA-IX60O3NF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive    # needed to access your Google drive\n",
        "import shutil            # needed for unziping of the example database\n",
        "import os                # for checking existence/removing of a file\n",
        "\n",
        "if not os.path.isfile(\"chinook.db\"):\n",
        "    drive.mount('/content/gdrive')\n",
        "    shutil.unpack_archive(\"gdrive/MyDrive/chinook.zip\")"
      ],
      "metadata": {
        "id": "D2EGBgZMC_U4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQLAlchemy components"
      ],
      "metadata": {
        "id": "CPNnr4RlGxeW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The demonstrations will use multiple functions and classes of the SQLAlchemy library."
      ],
      "metadata": {
        "id": "3TneEMEjHYSE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, MetaData\n",
        "from sqlalchemy.orm import relationship, declarative_base, sessionmaker"
      ],
      "metadata": {
        "id": "3bhJYhpYyayT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Database connection and transaction"
      ],
      "metadata": {
        "id": "Sa_dguIBF0l4"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "When using the SQLAlchemy library, the following objects are used to interact with the database. \n",
        "- `engine`: Provides a main connection to a database.\n",
        "- `sessionMaker`: An object which provides a method to start a new transaction.\n",
        "- `session`: A newly created transaction. Database changes executed through this object will get written when the object gets destroyed (so therefore the typical use is: `with sessionMaker() as session`, but at the moment let's create a global `session`)."
      ],
      "metadata": {
        "id": "PMQGl5B5F4O9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "engine = create_engine(\"sqlite:///chinook.db\",echo=False)\n",
        "sessionMaker = sessionmaker(bind=engine)\n",
        "session = sessionMaker()"
      ],
      "metadata": {
        "id": "ErJpQ6nOY6Qb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Class describing a table"
      ],
      "metadata": {
        "id": "xwjOj9CrHBn5"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This is a code pattern to describe a (row of a) single table in the database.  \n",
        "This step is independent on the database (does not load the table column names/types from the database).  \n",
        "This code needs to be adjusted for each table which is going to be created or modified.\n",
        "\n",
        "Let's study the following description referring to the table `albums` from the `chinook` database:\n",
        "- `Base`: This object normally is created once and it internally stores all information about the structure of the database. (*Note:* In this notebook we will recreate this object multiple times to allow changes in descriptions of the tables.)\n",
        "- `Album`: This is a newly created class and it will conceptually represent a single row of the table.\n",
        "- `__tablename__`: This field defines the name of the table as in the database.\n",
        "- `AlbumId`: This is a name of a `Column` as in the database. Moreover, it specifies that the column keeps `Integer` numbers, and that the column belogs to the `primary_key` of this table.\n",
        "- `Title`: As above, but the column keeps texts (`String` of max. 160 characters).\n",
        "\n",
        "For more info check: [Column and Data Types](https://docs.sqlalchemy.org/en/14/core/type_basics.html)."
      ],
      "metadata": {
        "id": "Ss09rXQ_HFPK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Base = declarative_base()\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = Column(Integer,primary_key=True)\n",
        "  Title = Column(String(160))\n",
        "  ArtistId = Column(Integer)"
      ],
      "metadata": {
        "id": "_YYB2GItynEw"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Table queries returning objects"
      ],
      "metadata": {
        "id": "tPyyRjamRA-5"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The SQLAlchemy library provides functionality to automatically build SQL queries, execute them, and convert the results to objects of the declared tables. Study the examples below..."
      ],
      "metadata": {
        "id": "RV-sOtG3tS4Z"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQL query is built by the library"
      ],
      "metadata": {
        "id": "BfixmQ1Rvpo3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The `session` object provides a function `query`. The arguments of this function define which table is to be asked for. Observe, how `session.query(Album)` builds automatically the SQL query based on the table description provided in the `Album` class:"
      ],
      "metadata": {
        "id": "ZiqhnX_Yt--G"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print( session.query(Album) )"
      ],
      "metadata": {
        "id": "1-Y6fMWF4-Be"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Using additionally the `limit` function we can obtain an SQL query referring to a few rows only:"
      ],
      "metadata": {
        "id": "g4dNd_7bubDX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print( session.query(Album).limit(3) )"
      ],
      "metadata": {
        "id": "E1Ags4pK536u"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Or, using the `filter` function we may generate SQL `where` clauses to select rows:"
      ],
      "metadata": {
        "id": "JrtVOZZHR6fj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print( session.query(Album).filter(Album.AlbumId == 5) )"
      ],
      "metadata": {
        "id": "tTYsKKEAR7y4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Getting a single object"
      ],
      "metadata": {
        "id": "DPWm9WYRQ8ek"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "We know that `AlbumId` is the primary key of the `albums` table. So, there should be just one row for an album with (for example) `AlbumId` of `5`.  \n",
        "The following code will run the SQL query and produce the `one` corresponding `Album` object:"
      ],
      "metadata": {
        "id": "v9G0axhhRDJa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "print(type(resA))\n",
        "print(resA)"
      ],
      "metadata": {
        "id": "7bdFdOQFS5zW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "You can see that indeed an object of the class `Album` was produced, although its content are not shown (will be discussed next). Note though, that the values from the table columns can be accessed conveniently as the class fields:"
      ],
      "metadata": {
        "id": "8OKd0-y0TpAn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA.AlbumId"
      ],
      "metadata": {
        "id": "DXlxASSFUGE4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "resA.Title"
      ],
      "metadata": {
        "id": "MeYiLo-pUI-j"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Nice printing of object fields"
      ],
      "metadata": {
        "id": "ko-hTfHq1VSZ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Redefine the `Album` object a add a method `__repr__`. This is a function used to get the text representation of an object when it gets `print`ed:"
      ],
      "metadata": {
        "id": "VlwfuuXj1qPX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Base = declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = Column(Integer,primary_key=True)\n",
        "  Title = Column(String(160))\n",
        "  ArtistId = Column(Integer)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Album(AlbumId='%s', Title='%s', ArtistId='%s')\" % (self.AlbumId, self.Title, self.ArtistId)"
      ],
      "metadata": {
        "id": "APX1gZZ06WsC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's again get the object and print it:"
      ],
      "metadata": {
        "id": "uuBRfHplU6h3"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "print(resA)"
      ],
      "metadata": {
        "id": "Xgqsra_-6tsG"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Getting many objects (list)"
      ],
      "metadata": {
        "id": "ATsLK53bvsWU"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "When a query may return any number of objects use the method `.all()` instead of `.one()`. The returned value will be a list with all query results. That elements of the list will be objects representing the table rows:"
      ],
      "metadata": {
        "id": "9yPZBWu-v-S2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resAs = session.query(Album).limit(3).all()\n",
        "print( type( resAs ) )\n",
        "print( resAs )"
      ],
      "metadata": {
        "id": "7_8I60vV5_a2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's consider the first list element in more detail. Check its `type`, print it, and practice access to data from each table column:"
      ],
      "metadata": {
        "id": "99uLWjCAz4mu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA = resAs[1]\n",
        "print( type( resA ) )\n",
        "print( resA )\n",
        "print( resA.Title )"
      ],
      "metadata": {
        "id": "OuwEw6j_w1Y4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Getting many objects (for loop)"
      ],
      "metadata": {
        "id": "X5OYRmld2lvc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following code might be used to access resulting objects one by one:"
      ],
      "metadata": {
        "id": "17nOOLze4uTX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for r in session.query(Album).limit(3):\n",
        "  print( r )"
      ],
      "metadata": {
        "id": "evj2zjUf6yRk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Or using list comprehension:"
      ],
      "metadata": {
        "id": "zeMb9poD46Qm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "[a.Title for a in session.query(Album).limit(3)]"
      ],
      "metadata": {
        "id": "lhwW_rc84eZm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Practice"
      ],
      "metadata": {
        "id": "uTF2JFgldkpL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "1.   Have a look into [the documentation](https://docs.sqlalchemy.org/en/14/orm/tutorial.html#returning-lists-and-scalars) and understand the functions: `one()`, `first()`, `all()`, `one_or_none()`.\n",
        "1.   Calculate the number of `Album`s in the database. Construct the query and use the [`count()`](https://docs.sqlalchemy.org/en/14/orm/tutorial.html#counting) function.\n",
        "\n"
      ],
      "metadata": {
        "id": "zLo2fc4CdtVQ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Another class describing a related table"
      ],
      "metadata": {
        "id": "77RcJ8P2Y3BB"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Class (partially) describing another table"
      ],
      "metadata": {
        "id": "uN_63iRze-Rn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Base = declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = Column(Integer,primary_key=True)\n",
        "  Title = Column(String(160))\n",
        "  ArtistId = Column(Integer)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Album(AlbumId='%s', Title='%s', ArtistId='%s')\" % (self.AlbumId, self.Title, self.ArtistId)\n",
        "\n",
        "class Track(Base):\n",
        "  __tablename__ = \"tracks\"\n",
        "\n",
        "  TrackId = Column(Integer,primary_key=True)\n",
        "  Name = Column(String(200))\n",
        "  AlbumId = Column(Integer)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Track(TrackId='%s', Name='%s', AlbumId='%s')\" % (self.TrackId, self.Name, self.AlbumId)"
      ],
      "metadata": {
        "id": "ha7deS0hZBnt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Observing a relation between two tables"
      ],
      "metadata": {
        "id": "j93H1v9pfFUa"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Check in the database diagramm:\n",
        "- a `Track` refers to zero or one album\n",
        "- an `Album` refers to zero or more tracks"
      ],
      "metadata": {
        "id": "skIOnXTufVw0"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's study the `Album` with `AlbumId` of 5:"
      ],
      "metadata": {
        "id": "mWSUHyonfD60"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "print(resA)"
      ],
      "metadata": {
        "id": "Tt_gDx4UgbUp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "These are the `Track`s which refer to this album:"
      ],
      "metadata": {
        "id": "Ejrx42FharUE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for resT in session.query(Track).filter(Track.AlbumId == 5).all():\n",
        "  print(resT)"
      ],
      "metadata": {
        "id": "9iwsJFYwfStp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "resT = session.query(Track).filter(Track.TrackId == 105).one()\n",
        "resT"
      ],
      "metadata": {
        "id": "hPIPZ3kraXCb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Generating methods representing relations"
      ],
      "metadata": {
        "id": "IOC77fGJh1Wv"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "From the object oriented programming point of view it would be useful to:\n",
        "- Have a method `album()` in the class `Track`.\n",
        "- Have a method `tracks()` in the class `Album`."
      ],
      "metadata": {
        "id": "Ibt9dHW5iJr1"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "To define such a relation the following two changes are needed (look at the code below):\n",
        "- In the `Track` class:\n",
        "\n",
        "  the field `AlbumId` gets declared as a `ForeginKey` to `albums.AlbumId` (so a foreign key to the table `albums` where the key is in column `AlbumId`). With `nullable` you can specify whether it is allowed that a `Track` does not refer to any `Album`.\n",
        "- In the `Album` class:\n",
        "\n",
        "  the field `tracks` gets declared as a relationship to the class `Track`. In the class `Track` the reverse relationship should be called `album`.\n",
        "\n",
        "See [relationship patterns](https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#relationship-patterns) for more details."
      ],
      "metadata": {
        "id": "mImihJy4wHsV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Base = declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = Column(Integer,primary_key=True)\n",
        "  Title = Column(String(160))\n",
        "  ArtistId = Column(Integer)\n",
        "  tracks = relationship(\"Track\", backref=\"album\")               # <<<< HERE\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Album(AlbumId='%s', Title='%s', ArtistId='%s')\" % (self.AlbumId, self.Title, self.ArtistId)\n",
        "\n",
        "class Track(Base):\n",
        "  __tablename__ = \"tracks\"\n",
        "\n",
        "  TrackId = Column(Integer,primary_key=True)\n",
        "  Name = Column(String(200))\n",
        "  AlbumId = Column(ForeignKey('albums.AlbumId'), nullable=True) # <<<< HERE\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Track(TrackId='%s', Name='%s', AlbumId='%s')\" % (self.TrackId, self.Name, self.AlbumId)"
      ],
      "metadata": {
        "id": "oBeAuUT-kB62"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's get an `Album` object `resA` now:"
      ],
      "metadata": {
        "id": "h84KKIBp0UCL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resA = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "print(resA)"
      ],
      "metadata": {
        "id": "MCkCNeTMueqp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "And see the value of the new field `resA.tracks`:"
      ],
      "metadata": {
        "id": "1dqDyvuv0aZ_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for resT in resA.tracks:\n",
        "  print(resT)"
      ],
      "metadata": {
        "id": "VRjl7SEGkye6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "resA.tracks[1]"
      ],
      "metadata": {
        "id": "VkzyYlCWuTVW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "resA.tracks[1].album"
      ],
      "metadata": {
        "id": "LyuhX_dBuYIr"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Adding objects with new data to database"
      ],
      "metadata": {
        "id": "JLgyv3mLG2iD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's consider the goal of adding a new `Album` to the database. Note (check on the database diagramm) that each album must refer to exactly one artist. So it is impossible to add an album without a proper reference to an artist. Consequently, we first need to properly describe the relationship between `albums` and `artists` tables. Then, we will choose a random artist and add a new fictional album of that artist."
      ],
      "metadata": {
        "id": "DfF1qON3pun-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Another table, another relationship"
      ],
      "metadata": {
        "id": "ZQq8g9Rhqt8E"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following modifications are made here (try yourself to introduce these modifications without looking into the code below):\n",
        "- New table description class `Artist`\n",
        "- The `Album` gets a proper foregin key to `Artist`\n",
        "- The `Artist` gets a relationship to `Album` with field names `Artist.albums` and `Album.artist`."
      ],
      "metadata": {
        "id": "2spWhysGsKQf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Base = declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = Column(Integer,primary_key=True)\n",
        "  Title = Column(String(160))\n",
        "  ArtistId = Column(ForeignKey('artists.ArtistId'), nullable=False) # <<<< HERE\n",
        "  tracks = relationship(\"Track\", backref=\"album\")\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Album(AlbumId='%s', Title='%s', ArtistId='%s')\" % (self.AlbumId, self.Title, self.ArtistId)\n",
        "\n",
        "class Track(Base):\n",
        "  __tablename__ = \"tracks\"\n",
        "\n",
        "  TrackId = Column(Integer,primary_key=True)\n",
        "  Name = Column(String(200))\n",
        "  AlbumId = Column(ForeignKey('albums.AlbumId'), nullable=True)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Track(TrackId='%s', Name='%s', AlbumId='%s')\" % (self.TrackId, self.Name, self.AlbumId)\n",
        "\n",
        "class Artist(Base):\n",
        "  __tablename__ = \"artists\"\n",
        "\n",
        "  ArtistId = Column(Integer,primary_key=True)\n",
        "  Name = Column(String(120))\n",
        "  albums = relationship(\"Album\", backref=\"artist\")                  # <<<< HERE\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Artist(ArtistId='%s', Name='%s')\" % (self.ArtistId, self.Name)\n"
      ],
      "metadata": {
        "id": "fR2j0AS2q1i-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's check the new Artist-Album relationship:"
      ],
      "metadata": {
        "id": "8GjGRCd8szuk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print( session.query(Artist).count() )\n",
        "print( session.query(Artist).first() )"
      ],
      "metadata": {
        "id": "p4G6x4hGs4Kt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Adding a new object (related to existing data/objects)"
      ],
      "metadata": {
        "id": "rB9VmnrA4VGn"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "So far the lifetime of the session object was ignored. But it defines the transactions. When changes to objects are made they get written when `commit()` of the session is called. Note, that if multiple session objects exist simulatnously, the data between them are not synchronized. Consequently, checking newly added (modified) content after `commit()` cannot be done through another session object which existed before `commit()`."
      ],
      "metadata": {
        "id": "9LcydMUd4jZc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's list the `Album`s of Metallica existing in the database (through a context-managed session object `querySession` created in a `with` block):"
      ],
      "metadata": {
        "id": "qPl51kY9-6p4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sessionMaker() as querySession:\n",
        "  resArtist = querySession.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "  print(resArtist, \"\\n\") \n",
        "  for a in resArtist.albums: print( a )"
      ],
      "metadata": {
        "id": "HMYOAGKItNie"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Here is an example of adding a new `Album`. Note, that the `AlbumId` will get automatically generated and that the `ArtistId` will be automatically collected when `theArtist` is assigned to the `artist` relationship field. The changes will get written to the database the moment of `commit()`."
      ],
      "metadata": {
        "id": "Z7GW5nbA_HnF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with sessionMaker() as addSession:\n",
        "  theArtist = addSession.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "  newAlbum = Album(Title='Hardwired... to Self-Destruct') # new Album object, only in memory\n",
        "  newAlbum.artist = theArtist                             # building relation to theArtist\n",
        "  addSession.add(newAlbum)                                # adding Album to \"be written\"\n",
        "  addSession.commit()                                     # writing to the database\n",
        "  print(newAlbum)"
      ],
      "metadata": {
        "id": "vL07b1K6zQ8Q"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "with sessionMaker() as querySession:\n",
        "  resArtist = querySession.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "  print(resArtist, \"\\n\") \n",
        "  for a in resArtist.albums: print( a )"
      ],
      "metadata": {
        "id": "V4H7nBhR2mA1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Multistep exercise"
      ],
      "metadata": {
        "id": "Qw2PjfliXr2c"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The final goal of this session is to build a class providing access to a database.  \n",
        "This class should provide all needed database content through a well defined interface.  \n",
        "\n",
        "Follow the points below in the provided order (later points depend on earlier).  \n",
        "Try to implement each step without checking the solution provided below.  \n",
        "Try to type. Do not copy-paste."
      ],
      "metadata": {
        "id": "-H5XxgZ_4k4w"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "1. Define a new class `Chinook`. This class will contain all top-level functions to access the database. The functions will be added gradually.\n",
        "1. In the class `Chinook`: \n",
        "  - Define the class constructor `__init__(self, fileName)`.\n",
        "  - The `fileName` argument will provide the name of the file with the database.\n",
        "1. In the class `Chinook`:\n",
        "  - In the constructor create the engine and store it in attribute `self._engine`. \n",
        "  - Similarly, in `self._sessionMaker` store the result of `sessionmaker` call.\n",
        "1. Outside the class:\n",
        "  - Create an object of the class `Chinook`. \n",
        "  - Use the proper `fileName` argument.\n",
        "  - Store the new object in `db`.\n",
        "1. In the class `Chinook`:\n",
        "  - Define a new method `newSession(self)`. \n",
        "  - Use `self._sessionMaker` to create a new session and return it.\n",
        "1. Outside the class:\n",
        "  - Recreate the object in `db` with the updated class. \n",
        "  - Test whether you get a session object when you call the `newSession` method.\n",
        "1. In the class `Chinook`:\n",
        "  - Define a new method `addArtist(self, name)`. \n",
        "  - The method should add a new artist of `name` to the database. \n",
        "  - Perform adding within a new local session. \n",
        "  - Remember to call `commit` in the session. \n",
        "  - The function should return the `ArtistId` of the newly added artist.\n",
        "1. In the class `Chinook`:\n",
        "  - Define a new method `getArtist(self, artistId)`. \n",
        "  - The method should return an `Artist` object of the provided `artistId`.\n",
        "1. Outside the class:\n",
        "  - Recreate the object in `db` with the updated class. \n",
        "  - Write the code to `addArtist` with a randomly chosen name.\n",
        "  - Store the returned `artistId` value in a variable.\n",
        "  - Check whether you can get the artist back with `getArtist`.\n",
        "1. In the class `Chinook`:\n",
        "  - Define a new method `allArtists(self)`. \n",
        "  - The method should return a list of all `Artist` objects present in the database.\n",
        "1. Outside the class:\n",
        "  - Recreate the object in `db` with the updated class. \n",
        "  - Use the result of `db.allArtists()` in a `for` loop to print all artists from the database.\n",
        "  - Find how to print only the last 10 of the returned objects.\n",
        "1. Outside the class:\n",
        "  - In `firstNames` create a vector with several random popular first names.\n",
        "  - Similar for `surNames`.\n",
        "  - From package `random` use `choice` method. It randomly selects an element from a list.\n",
        "  - Create a random name by concatenating a random first name and a random surname from your lists.\n",
        "  - Insert above into a loop which adds to the database 10 artists with randomly generated names.\n",
        "  - Print last 10 artists to check whether they were indeed added.\n",
        "1. Finally, copy the code to a new Jupyter/Colab notebook:\n",
        "  - Does the code run?\n",
        "  - Add all necessary `import` and class definitions (e.g. `Artist`)."
      ],
      "metadata": {
        "id": "0CusUEgnyQRo"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "class Chinook:\n",
        "  def __init__(self, fileName):\n",
        "    addr = \"sqlite:///\" + fileName\n",
        "    self._engine = create_engine(addr,echo=False)\n",
        "    self._sessionMaker = sessionmaker(bind=engine)\n",
        "\n",
        "  def newSession(self):\n",
        "    return self._sessionMaker()\n",
        "\n",
        "  def addArtist(self, name):\n",
        "    with self.newSession() as ses:\n",
        "      a = Artist(Name=name)\n",
        "      ses.add(a)\n",
        "      ses.commit()\n",
        "      return a.ArtistId\n",
        "\n",
        "  def getArtist(self,artistId):\n",
        "    with self.newSession() as ses:\n",
        "      return ses.query(Artist).where(Artist.ArtistId == artistId).one()\n",
        "\n",
        "  def allArtists(self):\n",
        "    with self.newSession() as ses:\n",
        "      return ses.query(Artist).all()\n",
        "\n",
        "db = Chinook(\"chinook.db\")    \n",
        "aId = db.addArtist(name=\"The Singer\")\n",
        "print( \"The ArtistId of the new artist is: \", aId)\n",
        "print( db.getArtist(artistId=aId))\n",
        "\n",
        "from random import choice\n",
        "\n",
        "firstNames = [ \"John\", \"Johan\", \"Jan\", \"Ivan\" ]\n",
        "lastNames = [ \"Smith\", \"Kowalski\", \"Kovalsky\" ]\n",
        "\n",
        "for i in range(5):\n",
        "  n = choice(firstNames) + \" \" + choice(lastNames)\n",
        "  db.addArtist(name=n)\n",
        "\n",
        "for a in db.allArtists()[-8:-1]:\n",
        "  print(a)"
      ],
      "metadata": {
        "id": "uzmrcpDQYNNe"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}