Lesson_2c_ORM_Relationship.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f5c2c297-0237-4d1d-bff9-4ff27f176224",
   "metadata": {},
   "source": [
    "# ORM and RELATIONSHIPS (Done right)\n",
    "*Author: Marco Prenassi*   \n",
    "*License: Creative Commons Attribution 4.0 International*    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d7b30ec1-4486-46d6-9fda-d979cdd1a9b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in /opt/conda/lib/python3.11/site-packages (2.0.22)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in /opt/conda/lib/python3.11/site-packages (from sqlalchemy) (4.8.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /opt/conda/lib/python3.11/site-packages (from sqlalchemy) (3.0.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "8f862deb-f7b4-40a2-982d-9397e09cfb3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Just to be sure, let's delete the database and..\n",
    "!rm ./databases/orm_database.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "28a82b55-a5a7-452c-8e1c-f037b1d068ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new sqlite database\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine(\"sqlite:///databases/orm_database.db\", echo=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cc191775-7b56-4897-b91c-2fbff46a57e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.exc import IntegrityError # Just to catch every double insertion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "26bcdd78-6cca-4a15-a176-867e01293278",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's import some modules\n",
    "from sqlalchemy import String\n",
    "from sqlalchemy.orm import DeclarativeBase\n",
    "from sqlalchemy.orm import Mapped\n",
    "from sqlalchemy.orm import mapped_column\n",
    "# These are new modules to manage relationships\n",
    "from sqlalchemy.orm import relationship\n",
    "from typing import List\n",
    "from typing import Optional\n",
    "from sqlalchemy import ForeignKey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c05c4b80-dd78-48cf-bbab-83d2001fc07b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's create our engine\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine(\"sqlite:///databases/orm_database.db\", echo=True)\n",
    "# Create all the table, using the definition in Base (that's why we used it)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "81abb1d9-4166-4fc6-9038-21de07fd7023",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-14 06:19:39,446 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:39,501 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"association_table\")\n",
      "2024-10-14 06:19:39,505 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,514 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"association_table\")\n",
      "2024-10-14 06:19:39,514 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,515 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"left_table\")\n",
      "2024-10-14 06:19:39,515 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,516 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"left_table\")\n",
      "2024-10-14 06:19:39,517 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,517 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"right_table\")\n",
      "2024-10-14 06:19:39,517 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,518 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"right_table\")\n",
      "2024-10-14 06:19:39,518 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:39,519 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# let's drop all to be sure\n",
    "try:\n",
    "    Base.metadata.drop_all(engine)\n",
    "except:\n",
    "    pass # LIKE ALWAYS, NEVER DO THIS. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4a3d466-e375-4620-9751-19cbf9fd0181",
   "metadata": {},
   "source": [
    "#### ONE TO ONE Relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "f5683324-952f-494f-adc4-9e9e03c9fe8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-14 06:19:40,625 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:40,626 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"parent_table\")\n",
      "2024-10-14 06:19:40,627 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:40,627 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"parent_table\")\n",
      "2024-10-14 06:19:40,628 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:40,628 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"child_table\")\n",
      "2024-10-14 06:19:40,629 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:40,629 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"child_table\")\n",
      "2024-10-14 06:19:40,630 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:40,632 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE parent_table (\n",
      "\tname VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (name)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-14 06:19:40,633 INFO sqlalchemy.engine.Engine [no key 0.00032s] ()\n",
      "2024-10-14 06:19:40,636 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE child_table (\n",
      "\tname VARCHAR NOT NULL, \n",
      "\tparent_id VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (name), \n",
      "\tFOREIGN KEY(parent_id) REFERENCES parent_table (name)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-14 06:19:40,636 INFO sqlalchemy.engine.Engine [no key 0.00020s] ()\n",
      "2024-10-14 06:19:40,638 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "class Parent(Base):\n",
    "    __tablename__ = \"parent_table\"\n",
    "    name: Mapped[str] = mapped_column(String[50],primary_key=True)\n",
    "    child: Mapped[\"Child\"] = relationship(back_populates=\"parent\")\n",
    "\n",
    "class Child(Base):\n",
    "    __tablename__ = \"child_table\"\n",
    "\n",
    "    name: Mapped[str] = mapped_column(String[50],primary_key=True)\n",
    "    parent_id: Mapped[str] = mapped_column(ForeignKey(\"parent_table.name\"))\n",
    "    parent: Mapped[\"Parent\"] = relationship(back_populates=\"child\")\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8efe5630-882f-4329-acf1-31378d6595ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapper[Child(child_table)]\n",
      "Mapper[Parent(parent_table)]\n",
      "2024-10-14 06:19:41,130 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:41,134 INFO sqlalchemy.engine.Engine INSERT INTO parent_table (name) VALUES (?)\n",
      "2024-10-14 06:19:41,135 INFO sqlalchemy.engine.Engine [generated in 0.00210s] ('Carl',)\n",
      "2024-10-14 06:19:41,138 INFO sqlalchemy.engine.Engine INSERT INTO child_table (name, parent_id) VALUES (?, ?)\n",
      "2024-10-14 06:19:41,138 INFO sqlalchemy.engine.Engine [generated in 0.00037s] ('John', 'Carl')\n",
      "2024-10-14 06:19:41,139 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "for row in Base.registry.mappers:\n",
    "    print(row)\n",
    "\n",
    "from sqlalchemy.orm import Session\n",
    "with Session(engine) as session:\n",
    "    try:\n",
    "        u = Child(name = 'John')\n",
    "        p = Parent(name = 'Carl')\n",
    "        p.child = u\n",
    "        session.add(u)\n",
    "        session.commit()\n",
    "    except IntegrityError as e:\n",
    "        print(e)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "0c621148-ec29-4cff-812a-564ebf838592",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-14 06:19:41,909 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:41,924 INFO sqlalchemy.engine.Engine SELECT parent_table.name \n",
      "FROM parent_table\n",
      "2024-10-14 06:19:41,928 INFO sqlalchemy.engine.Engine [generated in 0.00481s] ()\n",
      "2024-10-14 06:19:41,937 INFO sqlalchemy.engine.Engine SELECT child_table.name AS child_table_name, child_table.parent_id AS child_table_parent_id \n",
      "FROM child_table \n",
      "WHERE ? = child_table.parent_id\n",
      "2024-10-14 06:19:41,938 INFO sqlalchemy.engine.Engine [generated in 0.00120s] ('Carl',)\n",
      "child: John\n",
      "2024-10-14 06:19:41,940 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "with Session(engine) as session:\n",
    "    stmt = select(Parent)\n",
    "    for parent in session.scalars(stmt):\n",
    "        print(f\"child: {parent.child.name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c16863f-04af-4090-bf36-e1dad3578d1e",
   "metadata": {},
   "source": [
    "#### One To Many\n",
    "*A one to many relationship places a foreign key on the child table referencing the parent. relationship() is then specified on the parent, as referencing a collection of items represented by the child:*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "bc4697f7-e966-4ae3-93be-2bff0dcc2413",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-14 06:19:45,451 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:45,454 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"parent_table\")\n",
      "2024-10-14 06:19:45,454 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:45,459 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"child_table\")\n",
      "2024-10-14 06:19:45,460 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:45,462 INFO sqlalchemy.engine.Engine \n",
      "DROP TABLE child_table\n",
      "2024-10-14 06:19:45,463 INFO sqlalchemy.engine.Engine [no key 0.00048s] ()\n",
      "2024-10-14 06:19:45,468 INFO sqlalchemy.engine.Engine \n",
      "DROP TABLE parent_table\n",
      "2024-10-14 06:19:45,468 INFO sqlalchemy.engine.Engine [no key 0.00045s] ()\n",
      "2024-10-14 06:19:45,470 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# let's drop all to be sure\n",
    "try:\n",
    "    Base.metadata.drop_all(engine)\n",
    "except:\n",
    "    pass # LIKE ALWAYS, NEVER DO THIS. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "84788cec-9074-4aff-819d-25ff54b70cb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-14 06:19:46,272 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:46,273 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"parent_table\")\n",
      "2024-10-14 06:19:46,273 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:46,274 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"parent_table\")\n",
      "2024-10-14 06:19:46,274 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:46,275 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"child_table\")\n",
      "2024-10-14 06:19:46,275 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:46,275 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"child_table\")\n",
      "2024-10-14 06:19:46,276 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-14 06:19:46,277 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE parent_table (\n",
      "\tname VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (name)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-14 06:19:46,278 INFO sqlalchemy.engine.Engine [no key 0.00047s] ()\n",
      "2024-10-14 06:19:46,281 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE child_table (\n",
      "\tname VARCHAR NOT NULL, \n",
      "\tparent_id VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (name), \n",
      "\tFOREIGN KEY(parent_id) REFERENCES parent_table (name)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-14 06:19:46,281 INFO sqlalchemy.engine.Engine [no key 0.00044s] ()\n",
      "2024-10-14 06:19:46,285 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "class Parent(Base):\n",
    "    __tablename__ = \"parent_table\"\n",
    "\n",
    "    name: Mapped[str] = mapped_column(String[50], primary_key=True)\n",
    "    children: Mapped[List[\"Child\"]] = relationship(back_populates=\"parent\")\n",
    "    __table_args__ = {'extend_existing' : True}\n",
    "\n",
    "class Child(Base):\n",
    "    __tablename__ = \"child_table\"\n",
    "\n",
    "    name: Mapped[str] = mapped_column(String[50], primary_key=True)\n",
    "    parent_id: Mapped[int] = mapped_column(ForeignKey(\"parent_table.name\"))\n",
    "    parent: Mapped[\"Parent\"] = relationship(back_populates=\"children\")\n",
    "    __table_args__ = {'extend_existing' : True}\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "ba0be44c-c434-499c-906c-90bae0894061",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapper[Child(child_table)]\n",
      "Mapper[Parent(parent_table)]\n",
      "2024-10-14 06:19:46,991 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-14 06:19:46,994 INFO sqlalchemy.engine.Engine INSERT INTO parent_table (name) VALUES (?)\n",
      "2024-10-14 06:19:46,994 INFO sqlalchemy.engine.Engine [generated in 0.00077s] ('Mister',)\n",
      "2024-10-14 06:19:46,997 INFO sqlalchemy.engine.Engine INSERT INTO child_table (name, parent_id) VALUES (?, ?)\n",
      "2024-10-14 06:19:46,999 INFO sqlalchemy.engine.Engine [generated in 0.00171s] [('Jacob', 'Mister'), ('Igor', 'Mister'), ('Veronica', 'Mister')]\n",
      "2024-10-14 06:19:47,000 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# Let's create all the data:\n",
    "for row in Base.registry.mappers:\n",
    "    print(row)\n",
    "\n",
    "from sqlalchemy.orm import Session\n",
    "with Session(engine) as session:\n",
    "    try:\n",
    "        u = [Child(name = 'Jacob'), Child(name = 'Igor'), Child(name = 'Veronica')]\n",
    "        p = Parent(name = 'Mister')\n",
    "        p.children.extend(u)\n",
    "        session.add_all(u)\n",
    "        session.commit()\n",
    "    except IntegrityError as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "ef5351cc-8ad1-435a-b4c8-f11c890e823b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "parent: Mister\n",
      "  child: Jacob\n",
      "  child: Igor\n",
      "  child: Veronica\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "engine.echo = False\n",
    "with Session(engine) as session:\n",
    "    stmt = select(Parent)\n",
    "    for parent in session.scalars(stmt):\n",
    "        print(f\"parent: {parent.name}\")\n",
    "        for child in parent.children:\n",
    "            print(f\"  child: {child.name}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "100b897e-f416-4503-8e28-545dab80bd18",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    Base.metadata.drop_all(engine)\n",
    "except:\n",
    "    pass # LIKE ALWAYS, NEVER DO THIS. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e742a60-eadd-4c15-b2b0-a577a8f863fc",
   "metadata": {},
   "source": [
    "#### Many To Many\n",
    "*This is a really useful relationship, but the complexity grows, we need to extend our database*    \n",
    "*creating a new cross table*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "a8192707-0859-477f-9102-1ee4469dc86e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import annotations\n",
    "\n",
    "from sqlalchemy import Column\n",
    "from sqlalchemy import Table\n",
    "from sqlalchemy import ForeignKey\n",
    "from sqlalchemy import Integer\n",
    "from sqlalchemy.orm import Mapped\n",
    "from sqlalchemy.orm import mapped_column\n",
    "from sqlalchemy.orm import DeclarativeBase\n",
    "from sqlalchemy.orm import relationship\n",
    "\n",
    "\n",
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "\n",
    "association_table = Table(\n",
    "    \"association_table\",\n",
    "    Base.metadata,\n",
    "    Column(\"left_id\", ForeignKey(\"left_table.id\"), primary_key=True),\n",
    "    Column(\"right_id\", ForeignKey(\"right_table.id\"), primary_key=True),\n",
    ")\n",
    "\n",
    "\n",
    "class Parent(Base):\n",
    "    __tablename__ = \"left_table\"\n",
    "\n",
    "    id: Mapped[int] = mapped_column(primary_key=True)\n",
    "    children: Mapped[List[Child]] = relationship(\n",
    "        secondary=association_table, back_populates=\"parents\"\n",
    "    )\n",
    "\n",
    "\n",
    "class Child(Base):\n",
    "    __tablename__ = \"right_table\"\n",
    "\n",
    "    id: Mapped[int] = mapped_column(primary_key=True)\n",
    "    parents: Mapped[List[Parent]] = relationship(\n",
    "        secondary=association_table, back_populates=\"children\"\n",
    "    )\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "7c0e6d98-921c-4e92-8481-8743756893d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import insert\n",
    "\n",
    "\n",
    "with Session(engine) as session:\n",
    "    try:\n",
    "        p1 = Parent(id = 2)\n",
    "        u1 = Child(id = 2)\n",
    "        p1.children.append(u1)\n",
    "        session.add(p1)\n",
    "        session.add(u1)\n",
    "        session.commit()\n",
    "    except IntegrityError as e:\n",
    "        print(e)\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}