class/homework_parent_child_explained.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyN6fva6tlSdHJAQ//3DkgQ9",
      "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/werowe/HypatiaAcademy/blob/master/class/homework_parent_child_explained.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EJPsBhP3pFAi"
      },
      "outputs": [],
      "source": [
        "%load_ext sql"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%sql sqlite:///random.db"
      ],
      "metadata": {
        "id": "pZz4KKCypdtk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "DROP TABLE IF EXISTS parent;\n",
        "DROP TABLE IF EXISTS child;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NKOYNe6oRLT0",
        "outputId": "a925defa-3955-45f6-a73d-f175fec75495"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "(sqlite3.OperationalError) no such table: parent\n",
            "[SQL: drop table parent;]\n",
            "(Background on this error at: https://sqlalche.me/e/20/e3q8)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import random\n",
        "\n",
        "# we want to make random data\n",
        "\n",
        "def getName():\n",
        "\n",
        "    fnames = [\"Marya\", \"Diana\", \"Oleskii\", \"Yulia\", \"Illias\", \"Vlad\", \"Egor\"]\n",
        "\n",
        "    lnames = [\"Zelensky\", \"Kovalenko\", \"Kravchenko\", \"Hrytsenko\", \"Melnyk\"]\n",
        "\n",
        "    return fnames[random.randint(0, len(fnames)-1)] +  \" \" + lnames[random.randint(0, len(lnames)-1)]\n",
        "\n"
      ],
      "metadata": {
        "id": "1Az-miiyyZtk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# you need some common element to link two tables\n",
        "\n",
        "%%sql\n",
        "\n",
        "CREATE TABLE parent\n",
        "  (\n",
        "    parent_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    parent_name text,\n",
        "    date_created text\n",
        "  );\n",
        "\n",
        "CREATE TABLE child\n",
        "  (\n",
        "    child_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    parent_id INTEGER,\n",
        "    child_name text,\n",
        "    date_created text\n",
        "  );\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R26ruIyWptix",
        "outputId": "b20a2dbe-203d-432f-bd4a-7dd351728c3a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "Done.\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "insert into parent(parent_name, date_created) values(\"fred\", \"2024-10-08\");\n",
        "insert into parent(parent_name, date_created) values(\"jane\", \"2024-10-08\");"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Vtl10VZKqAsN",
        "outputId": "2c9dce58-2509-41a5-dc5a-14f22570dfc2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "1 rows affected.\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select * from parent"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 118
        },
        "id": "0R8Dou2TqstY",
        "outputId": "34792277-d958-4db1-dfb9-fdfb379997df"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'fred', '2024-10-08'), (2, 'jane', '2024-10-08')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>parent_id</th>\n",
              "            <th>parent_name</th>\n",
              "            <th>date_created</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>fred</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>jane</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "insert into child(parent_id, child_name, date_created) values(1, \"oleskii\", \"2024-10-08\");\n",
        "insert into child(parent_id, child_name, date_created) values(1, \"Egor\", \"2024-10-08\");"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DCC6WAVDqyZ_",
        "outputId": "9ed23ee7-d9cf-4453-d187-6cad7bdb3366"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "1 rows affected.\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select * from child"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 118
        },
        "id": "wmWsLr5IrUBB",
        "outputId": "c9c4a471-669c-48d3-d664-7bbdf92f03a3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 1, 'oleskii', '2024-10-08'), (2, 1, 'Egor', '2024-10-08')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>child_id</th>\n",
              "            <th>parent_id</th>\n",
              "            <th>child_name</th>\n",
              "            <th>date_created</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>1</td>\n",
              "            <td>oleskii</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>1</td>\n",
              "            <td>Egor</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# use the join operation to get data from both tables, joining on the common column parent_id\n",
        "\n",
        "\n",
        "%%sql\n",
        "\n",
        "SELECT parent.parent_name, child.child_name\n",
        "FROM parent, child\n",
        "WHERE parent.parent_id = child.parent_id\n"
      ],
      "metadata": {
        "id": "WxwnCJ_kAC1l",
        "outputId": "a830eb62-5640-4299-9f4b-7f8ae76f60ff",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 118
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('fred', 'oleskii'), ('fred', 'Egor')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>parent_name</th>\n",
              "            <th>child_name</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>fred</td>\n",
              "            <td>oleskii</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>fred</td>\n",
              "            <td>Egor</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# hard-coded usually means BAD\n",
        "# generate lots of data.  so we will use python to generate data\n",
        "\n",
        "#insert into child(parent_id, child_name, date_created) values(1, \"Egor\", \"2024-10-08\");\n",
        "\n",
        "\n",
        "# Homework:  fix this to work with your data\n",
        "\n",
        "\n",
        "def createChild():\n",
        "\n",
        "  sql=\"insert into child(parent_id, child_name, date_created) values({}, '{}', '{}')\".format(1, getName(), \"2024-10-08\")\n",
        "\n",
        "  return sql\n"
      ],
      "metadata": {
        "id": "5n7mI1jBwYwh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# boilerplate code, means just a shell, it means the code that the will be the same every time\n",
        "\n",
        "\n",
        "import sqlite3\n",
        "\n",
        "db_path = 'random.db'\n",
        "\n",
        "# connect to it\n",
        "conn = sqlite3.connect(db_path)\n",
        "\n",
        "# a cursor lets you work with data\n",
        "cursor = conn.cursor()\n",
        "\n",
        "# this means run SQL\n",
        "cursor.execute(createChild())\n",
        "\n",
        "# commit means apply the changes\n",
        "conn.commit()\n",
        "\n",
        "\n",
        "# don't close until we have added lots of data\n",
        "conn.close()\n"
      ],
      "metadata": {
        "id": "3AWQn3GYxIsL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "select * from child"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 139
        },
        "id": "tVv5LtnlyK6C",
        "outputId": "ec5dfb55-29ab-48ea-9b68-5e992e1b56aa"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///random.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 1, 'oleskii', '2024-10-08'),\n",
              " (2, 1, 'Egor', '2024-10-08'),\n",
              " (3, 1, 'Vlad Zelensky', '2024-10-08')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>child_id</th>\n",
              "            <th>parent_id</th>\n",
              "            <th>child_name</th>\n",
              "            <th>date_created</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>1</td>\n",
              "            <td>oleskii</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>1</td>\n",
              "            <td>Egor</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>1</td>\n",
              "            <td>Vlad Zelensky</td>\n",
              "            <td>2024-10-08</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "getName()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "auncvMe7yj-D",
        "outputId": "6b9bd389-4eee-4f46-ab13-662a1dcafd18"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Oleskii Kovalenko'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# scope.\n",
        "\n",
        "fnames = [\"Marya\", \"Diana\", \"Oleskii\", \"Yulia\", \"Illias\", \"Vlad\", \"Egor\"]\n",
        "\n",
        "fnames[random.randint(0,len(fnames) - 1 )]\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "goqRfUpvytVF",
        "outputId": "2068eca0-f0ec-446b-b65d-ab9dda250ad5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Diana'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "#Homework\n",
        "\n",
        "Goals are:\n",
        "\n",
        "1.  learn how to make random data and put in parent table\n",
        "\n",
        "2.  (for next time) learn how to write to child table\n",
        "\n",
        "Study the code above and modify it to work with your data.  So only worry about the parent table.\n",
        "\n",
        "\n",
        "1.  The code above shows how to connect to a database and add one record. And the SQL statement is created by a function.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "Don't hard-code it.  Do something like this:\n",
        "\n",
        "```\n",
        "def createChild():\n",
        "\n",
        "  sql=\"insert into child(parent_id, child_name, date_created) values({}, '{}', '{}')\".format(1, getName(), \"2024-10-08\")\n",
        "\n",
        "  return sql\n",
        "```  \n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "LyUvO4mWzhYb"
      }
    }
  ]
}