{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "17023bc4-02bf-43a9-a16e-2114960bf2d2",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Object-Relational Mapping (ORM):<br>The `school` Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16c8272d-487a-4c6b-9058-1598dffc5686",
   "metadata": {},
   "source": [
    "## Imports from `sqlalchemy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d568604-f68e-4af0-95ad-1b97b7ea627a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07b406a0-dc67-4368-9a32-93eae9697e60",
   "metadata": {},
   "source": [
    "## Create a session to interact with the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64bdbcd4-93d5-4c6e-8eeb-603a47ab1d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "str = 'mysql+mysqlconnector://root:seekrit@localhost/school'\n",
    "engine = create_engine(str)\n",
    "session = Session(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c7bfcd6-a7b3-460d-82fb-e05b08bf56cc",
   "metadata": {},
   "source": [
    "## Prepare to map Python objects to database tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2b76cf1-ca6d-4c5c-b1b8-94279501420f",
   "metadata": {},
   "outputs": [],
   "source": [
    "base = automap_base()\n",
    "base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81478b5d-b596-40bc-9a5a-77dac9786e51",
   "metadata": {},
   "source": [
    "## Map the database tables to Python classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8606ef5f-dd45-455e-83a5-b7713c302165",
   "metadata": {},
   "outputs": [],
   "source": [
    "Student = base.classes.student\n",
    "Contact = base.classes.contact"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce9ad640-400a-4e7b-8632-3a4f5836daf2",
   "metadata": {},
   "source": [
    "## Query the `student` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b170b5e-eda4-4acc-9452-8a6d519186fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Student)\n",
    "\n",
    "for s in result:\n",
    "    print(s.id, s.first, s.last)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0710c4c-fe44-47c7-bdf2-d70869f4b673",
   "metadata": {},
   "source": [
    "#### What did SQLAlchemy send to the database server?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "840c3289-2d14-486b-bb8e-374c72a37a37",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(session.query(Student))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4ba9594-2e48-4242-bcd5-a9f9e2a1cafc",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Student).first();\n",
    "\n",
    "print(result.first, result.last)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43d879ec-4580-4a55-9f40-5e76710f61fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Student).get('S1005')\n",
    "\n",
    "print(result.first, result.last)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a381d9e9-8dae-4fb2-9799-ca51b9e2b32a",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Student).filter(Student.id > 'S1009').all()\n",
    "\n",
    "for s in result:\n",
    "    print(s.id, s.first, s.last)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329e617a-a10d-4601-b476-d6aa64f09823",
   "metadata": {},
   "source": [
    "## Query the contact table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3046e42d-a39f-433e-86bf-3e81059d492c",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Contact)\n",
    "\n",
    "for a in result:\n",
    "    print(a.id, a.email)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c33f40f7-d38d-4f33-afd7-f5afbc17a0d8",
   "metadata": {},
   "source": [
    "## Join the student and contact tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94d6af13-1f7f-4cb8-9b69-4ee644dffa30",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = session.query(Student.id, Student.first, Student.last, Contact.email) \\\n",
    "                .join(Contact).all()\n",
    "\n",
    "for r in result:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b26c9b35-4e56-4580-995c-083fd9a6ba96",
   "metadata": {},
   "source": [
    "#### What did SQLAlchemy send to the database server?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b830e75b-4c08-465f-9e98-687d89f07362",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(session.query(Student).join(Contact))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59e4292b-d94d-4265-83ee-a706e4f3d72b",
   "metadata": {},
   "source": [
    "## Close."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21742259-b65b-4980-91c3-73857fc4b7ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()\n",
    "engine.connect().close()\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43bb100b-d79c-4e95-b638-438b389394e3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}