11_sql/orm_practice.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "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",
      "metadata": {
        "id": "7QfhAsFJDKAt"
      },
      "source": [
        "# Object Relational Mapper"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "lMXTpcDAqsiD"
      },
      "source": [
        "## Goals"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "q0l7Hj4HEDEI"
      },
      "source": [
        "The overall goal of this session is to learn how to provide an object oriented interface to a database.  \n",
        "Since this is a complex task, specialized 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)."
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "Wqiz1HMYnp42"
      },
      "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",
        "- Understand the concepts of engine, session, transaction, commit, rollback.\n",
        "- Query the database and get results as objects representing rows. Build simple and more complex queries.\n",
        "- Extend the class with a method for nicer content printing.\n",
        "- Create another class for another database table. Learn how do declare foreign keys and add methods representing relations.\n",
        "- Add new content to the database. Commit. Show the newly added content."
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "*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."
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "wpHEHn7wsA76"
      },
      "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",
        "- We want classes `Album` (a row of the table `albums`) or `Track` (a row of the table `tracks`).\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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e2b3jP7s3VQF"
      },
      "source": [
        "## Preparation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7fXJZpD5kYVb"
      },
      "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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cB1iky0ekfcZ"
      },
      "source": [
        "### Download of the database"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "RmZYU5BSOoWN"
      },
      "source": [
        "The following lines download a compressed file `chinook.zip` and unpack it leading to a SQLite database file `chinook.db`.  \n",
        "*Note:* In this session we will modify the database, so the database file will change and some examples may not work anymore. In that case, you can download the database again."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wScMXlM-CLSK"
      },
      "outputs": [],
      "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 removing a file\n",
        "\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\")"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "CPNnr4RlGxeW"
      },
      "source": [
        "### SQLAlchemy toolkit"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3TneEMEjHYSE"
      },
      "source": [
        "The demonstrations will use multiple functions and classes of the SQLAlchemy library."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3bhJYhpYyayT"
      },
      "outputs": [],
      "source": [
        "import sqlalchemy as sa\n",
        "import sqlalchemy.orm as orm"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Sa_dguIBF0l4"
      },
      "source": [
        "### Database connection and transaction"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "PMQGl5B5F4O9"
      },
      "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",
        "- `session`: A newly created transaction. Database changes executed through this object will only get written at the end of the block when the transaction is committed. If the transaction is rolled back, all the changes are discarded."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ErJpQ6nOY6Qb"
      },
      "outputs": [],
      "source": [
        "engine = sa.create_engine(\"sqlite:///chinook.db\",echo=False)\n",
        "with orm.Session(engine) as session:\n",
        "    # query or modify the database through the session object\n",
        "    pass\n",
        "    # when modifying the database, commit or rollback the changes introduced in the block"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xwjOj9CrHBn5"
      },
      "source": [
        "## Class describing a table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ss09rXQ_HFPK"
      },
      "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)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_YYB2GItynEw"
      },
      "outputs": [],
      "source": [
        "Base = orm.declarative_base()\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = sa.Column(sa.Integer,primary_key=True)\n",
        "  Title = sa.Column(sa.String(160))\n",
        "  ArtistId = sa.Column(sa.Integer)"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "tPyyRjamRA-5"
      },
      "source": [
        "## Getting table rows as objects"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RV-sOtG3tS4Z"
      },
      "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..."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BfixmQ1Rvpo3"
      },
      "source": [
        "### SQL query is built by the library"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZiqhnX_Yt--G"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1-Y6fMWF4-Be"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    print( session.query(Album) )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "g4dNd_7bubDX"
      },
      "source": [
        "Using additionally the `limit` function we can obtain an SQL query referring to a few rows only:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "E1Ags4pK536u"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    print( session.query(Album).limit(3) )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JrtVOZZHR6fj"
      },
      "source": [
        "Or, using the `filter` function we may generate SQL `where` clauses to select rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tTYsKKEAR7y4"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    print( session.query(Album).filter(Album.AlbumId == 5) )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DPWm9WYRQ8ek"
      },
      "source": [
        "### Getting a single object"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v9G0axhhRDJa"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7bdFdOQFS5zW"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    a = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "    print(type(a))\n",
        "    print(a)\n",
        "\n",
        "# print(a)                   # the object a may be invalid once the session is closed"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8OKd0-y0TpAn"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DXlxASSFUGE4"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    a = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "    print(a.AlbumId)\n",
        "    print(a.Title)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ko-hTfHq1VSZ"
      },
      "source": [
        "### Nice printing of object fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VlwfuuXj1qPX"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "APX1gZZ06WsC"
      },
      "outputs": [],
      "source": [
        "Base = orm.declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = sa.Column(sa.Integer,primary_key=True)\n",
        "  Title = sa.Column(sa.String(160))\n",
        "  ArtistId = sa.Column(sa.Integer)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Album(AlbumId='%s', Title='%s', ArtistId='%s')\" % (self.AlbumId, self.Title, self.ArtistId)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uuBRfHplU6h3"
      },
      "source": [
        "Let's again get the object and print it:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Xgqsra_-6tsG"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    a = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "    print(a)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ATsLK53bvsWU"
      },
      "source": [
        "### Getting many objects (list)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9yPZBWu-v-S2"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7_8I60vV5_a2"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    albums = session.query(Album).limit(3).all()\n",
        "    print( type( albums ) )\n",
        "    print( albums )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "99uLWjCAz4mu"
      },
      "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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OuwEw6j_w1Y4"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    albums = session.query(Album).limit(3).all()\n",
        "\n",
        "    a = albums[1]\n",
        "    print( type( a ) )\n",
        "    print( a )\n",
        "    print( a.Title )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X5OYRmld2lvc"
      },
      "source": [
        "### Getting many objects (for loop)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "17nOOLze4uTX"
      },
      "source": [
        "The following code might be used to access resulting objects one by one:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "evj2zjUf6yRk"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "  for a in session.query(Album).limit(3):\n",
        "    print( a )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zeMb9poD46Qm"
      },
      "source": [
        "Or using list comprehension:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lhwW_rc84eZm"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    titles = [a.Title for a in session.query(Album).limit(3)]\n",
        "\n",
        "print( titles ) # list of str objects, safe to use outside of the session block"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uTF2JFgldkpL"
      },
      "source": [
        "### Practice"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zLo2fc4CdtVQ"
      },
      "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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "77RcJ8P2Y3BB"
      },
      "source": [
        "## Another class describing a related table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uN_63iRze-Rn"
      },
      "source": [
        "### Class (partially) describing another table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ha7deS0hZBnt"
      },
      "outputs": [],
      "source": [
        "Base = orm.declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = sa.Column(sa.Integer,primary_key=True)\n",
        "  Title = sa.Column(sa.String(160))\n",
        "  ArtistId = sa.Column(sa.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 = sa.Column(sa.Integer,primary_key=True)\n",
        "  Name = sa.Column(sa.String(200))\n",
        "  AlbumId = sa.Column(sa.Integer)\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Track(TrackId='%s', Name='%s', AlbumId='%s')\" % (self.TrackId, self.Name, self.AlbumId)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j93H1v9pfFUa"
      },
      "source": [
        "### Observing a relation between two tables"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "skIOnXTufVw0"
      },
      "source": [
        "Check in the database diagramm:\n",
        "- an `Album` refers to zero or more tracks\n",
        "- a `Track` refers to zero or one album (through the `AlbumId` foreign key)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mWSUHyonfD60"
      },
      "source": [
        "Let's study the `Album` with `AlbumId` of 5:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Tt_gDx4UgbUp"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    a = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "    print(a)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ejrx42FharUE"
      },
      "source": [
        "These are the `Track`s which refer to this album:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9iwsJFYwfStp"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "  for t in session.query(Track).filter(Track.AlbumId == 5).all():\n",
        "    print(t)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IOC77fGJh1Wv"
      },
      "source": [
        "### Generating methods representing relations"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "Ibt9dHW5iJr1"
      },
      "source": [
        "From the object oriented programming point of view it would be useful to:\n",
        "- Have a method `album()` in the class `Track` which returns the `Album` object corresponding to the `AlbumId` of the track.\n",
        "- Have a method `tracks()` in the class `Album` which returns the list of `Track` objects referring to this album."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mImihJy4wHsV"
      },
      "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."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oBeAuUT-kB62"
      },
      "outputs": [],
      "source": [
        "Base = orm.declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = sa.Column(sa.Integer,primary_key=True)\n",
        "  Title = sa.Column(sa.String(160))\n",
        "  ArtistId = sa.Column(sa.Integer)\n",
        "  tracks = orm.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 = sa.Column(sa.Integer,primary_key=True)\n",
        "  Name = sa.Column(sa.String(200))\n",
        "  AlbumId = sa.Column(sa.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)"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "h84KKIBp0UCL"
      },
      "source": [
        "Let's get an `Album` object `a` now and see the corresponding `Track` objects through the `tracks` method:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MCkCNeTMueqp"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    a = session.query(Album).filter(Album.AlbumId == 5).one()\n",
        "    print(a)\n",
        "    print(a.tracks)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JLgyv3mLG2iD"
      },
      "source": [
        "## Adding objects with new data to database"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "DfF1qON3pun-"
      },
      "source": [
        "Let's consider the goal of adding a new `Album` to the database.  \n",
        "*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.  \n",
        "Consequently, we first need to properly describe the relationship between `albums` and `artists` tables.  \n",
        "Then, we will choose a random artist and add a new fictional album of that artist."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZQq8g9Rhqt8E"
      },
      "source": [
        "### Another table, another relationship"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "2spWhysGsKQf"
      },
      "source": [
        "The following modifications are made here (try yourself to introduce these modifications without looking into the code below):\n",
        "- New class `Artist` describing the `artists` table\n",
        "- The `Album` gets updated to have a proper foreign key to `Artist`\n",
        "- The `Artist` gets a relationship to `Album` through fields: `Artist.albums` and `Album.artist`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fR2j0AS2q1i-"
      },
      "outputs": [],
      "source": [
        "Base = orm.declarative_base() # normally present once in a script!\n",
        "\n",
        "class Album(Base):\n",
        "  __tablename__ = \"albums\"\n",
        "  \n",
        "  AlbumId = sa.Column(sa.Integer,primary_key=True)\n",
        "  Title = sa.Column(sa.String(160))\n",
        "  ArtistId = sa.Column(sa.ForeignKey('artists.ArtistId'), nullable=False) # <<<< HERE\n",
        "  tracks = orm.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 = sa.Column(sa.Integer,primary_key=True)\n",
        "  Name = sa.Column(sa.String(200))\n",
        "  AlbumId = sa.Column(sa.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 = sa.Column(sa.Integer,primary_key=True)\n",
        "  Name = sa.Column(sa.String(120))\n",
        "  albums = orm.relationship(\"Album\", backref=\"artist\")                  # <<<< HERE\n",
        "\n",
        "  def __repr__(self):\n",
        "    return \"Artist(ArtistId='%s', Name='%s')\" % (self.ArtistId, self.Name)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8GjGRCd8szuk"
      },
      "source": [
        "Let's check the new Artist-Album relationship:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "p4G6x4hGs4Kt"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "    print( session.query(Artist).count() )        # total number of artists in the database\n",
        "    print( session.query(Artist).first() )        # first artist\n",
        "    print( session.query(Artist).first().albums ) # albums of the first artist"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rB9VmnrA4VGn"
      },
      "source": [
        "### Adding a new object (related to existing data/objects)"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "9LcydMUd4jZc"
      },
      "source": [
        "The lifetime of the `Session` object defines a transaction.  \n",
        "When changes to table/row objects are made they are not written immediately.  \n",
        "They get written when `commit()` method of the `Session` object is called.  \n",
        "Note, that if multiple session objects exist simultaneously, the data between them are not synchronized.  \n",
        "Consequently, checking newly added (modified) content after `commit()` cannot be done through another session object which was created before `commit()` was called."
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "qPl51kY9-6p4"
      },
      "source": [
        "Let's list the `Album`s of Metallica existing in the database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HMYOAGKItNie"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "  artist = session.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "  print(artist, \"\\n\") \n",
        "  for album in artist.albums: \n",
        "    print( album )"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "Z7GW5nbA_HnF"
      },
      "source": [
        "Here is an example of adding a new `Album`.  \n",
        "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.  \n",
        "The changes will get written to the database the moment of `commit()`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vL07b1K6zQ8Q"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "  artist = session.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "\n",
        "  newAlbum = Album(Title='Hardwired... to Self-Destruct') # new Album object, only in memory\n",
        "  newAlbum.artist = artist                                # building relation to theArtist\n",
        "  print(newAlbum)\n",
        "\n",
        "  session.add(newAlbum)                                   # staging Album to become written to the database\n",
        "  session.commit()                                        # writing to the database\n",
        "  \n",
        "  print(newAlbum)"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's check whether the new album has been added:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V4H7nBhR2mA1"
      },
      "outputs": [],
      "source": [
        "with orm.Session(engine) as session:\n",
        "  artist = session.query(Artist).filter(Artist.Name==\"Metallica\").one()\n",
        "  print(artist, \"\\n\") \n",
        "  for album in artist.albums: \n",
        "    print( album )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Qw2PjfliXr2c"
      },
      "source": [
        "## Multistep exercise"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "-H5XxgZ_4k4w"
      },
      "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 the earlier ones).  \n",
        "Try to implement each step without checking the solution provided below.  \n",
        "Try to type. Do not copy-paste.\n",
        "\n",
        "The solution is suboptimal - the returned objects are not fully independent of the current session and they may provide inconsistent data."
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "0CusUEgnyQRo"
      },
      "source": [
        "1. Define a new class `Chinook`. This class will contain all methods to access the database. The methods will be added gradually.\n",
        "1. In the class `Chinook`: \n",
        "    - Define the class constructor `__init__(self, url)`.\n",
        "    - The `url` argument will provide the location of the database as needed by the `create_engine` function.\n",
        "1. In the class `Chinook`:\n",
        "    - In the constructor create the engine and store it in the attribute `self._engine`. \n",
        "1. Outside the class:\n",
        "    - Create an object instance of the class `Chinook`. \n",
        "    - Use the proper `url` argument (so it uses the `chinook.db` file).\n",
        "    - Store the new object in the variable `db`.\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 value of `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 for the provided `artistId`.\n",
        "    - (Returning the `Artist` object is not the best solution because it would exist outside the session; but it is good enough for now.)\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",
        "    - (Returning the list of `Artist` objects is not the best solution because it would exist outside the session; but it is good enough for now.)\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"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uzmrcpDQYNNe"
      },
      "outputs": [],
      "source": [
        "class Chinook:\n",
        "  def __init__(self, url):\n",
        "    self._engine = sa.create_engine(url=url,echo=False)\n",
        "\n",
        "  def addArtist(self, name):\n",
        "    with orm.Session(self._engine) as session:\n",
        "      a = Artist(Name=name)\n",
        "      session.add(a)\n",
        "      session.commit()\n",
        "      return a.ArtistId # here a number is returned, no Session object needed\n",
        "\n",
        "  def getArtist(self, artistId):\n",
        "    with orm.Session(self._engine) as session:\n",
        "      a = session.query(Artist).where(Artist.ArtistId == artistId).one()\n",
        "    return a # this is not safe; the object exists outside the session object; relations will fail\n",
        "\n",
        "  def allArtists(self):\n",
        "    with orm.Session(self._engine) as session:\n",
        "      artists = session.query(Artist).all()\n",
        "    return artists # this is not safe; the object exists outside the session object; relations will fail\n",
        "\n",
        "db = Chinook(\"sqlite:///chinook.db\") # this should be used inside \"with\" context manager\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": {
    "colab": {
      "authorship_tag": "ABX9TyM+fdQExjqO5Tp7uhYj8eK7",
      "collapsed_sections": [
        "cB1iky0ekfcZ",
        "xwjOj9CrHBn5",
        "DPWm9WYRQ8ek",
        "ko-hTfHq1VSZ",
        "ATsLK53bvsWU",
        "X5OYRmld2lvc",
        "uN_63iRze-Rn",
        "j93H1v9pfFUa",
        "IOC77fGJh1Wv"
      ],
      "include_colab_link": true,
      "name": "orm_practice.ipynb",
      "private_outputs": true,
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}