BWH_Almost_East_Coast.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "### Installing and importing libraries"
      ],
      "metadata": {
        "id": "VFP8DPqTZdSs"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "R4stOmLVVumF"
      },
      "outputs": [],
      "source": [
        "!pip install -q mysql-connector-python"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import mysql.connector\n",
        "import sqlite3"
      ],
      "metadata": {
        "id": "0g_VwHfKWHdo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Creating database and a cursor to establish connection"
      ],
      "metadata": {
        "id": "ebsQDkByZmf-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection = sqlite3.connect('bwh_almost_east_coast.db')"
      ],
      "metadata": {
        "id": "3vaO7MFbWRG0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cursor = db_connection.cursor()"
      ],
      "metadata": {
        "id": "CCeouS8DWf6B"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Creating the Occupations table"
      ],
      "metadata": {
        "id": "EkLdgnMca8of"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "create_table_query1 = '''\n",
        "CREATE TABLE IF NOT EXISTS occupations (\n",
        "    o_id INTEGER PRIMARY KEY,\n",
        "    occupation TEXT\n",
        ")\n",
        "'''\n",
        "\n",
        "cursor.execute(create_table_query1)"
      ],
      "metadata": {
        "id": "K8IdiZPxXOcs",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "76b50bfc-3df9-4176-f28f-05ba5cacc9da"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "x9gIlcHrd8ng"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "## cursor.execute(\"DELETE FROM occupations WHERE o_id = ?\", (1,))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "66So44Gle4Qg",
        "outputId": "5cc2208f-0f83-4f16-9e39-91031e157862"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "## cursor.execute(\"SELECT * FROM occupations WHERE o_id = ?\", (1,))\n",
        "## print(cursor.fetchall())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0LHb1PIAfGte",
        "outputId": "3f72ffd3-bac7-413a-c40d-922b2fe0c524"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "occupation_data_query1 = '''\n",
        "INSERT INTO occupations (o_id, occupation) VALUES (?,?)\n",
        "'''\n",
        "data1 = (1, 'Firefighter')\n",
        "cursor.execute(occupation_data_query1, data1)"
      ],
      "metadata": {
        "id": "9l6Zvt1ObE8l",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cc717a5b-52f2-4638-a3da-d338896fa0d5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "DDvw2NzbeDKb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "occupation_data_query2 = '''\n",
        "INSERT INTO occupations (o_id, occupation) VALUES (?,?)\n",
        "'''\n",
        "data2 = (2, 'Painter')\n",
        "cursor.execute(occupation_data_query2, data2)"
      ],
      "metadata": {
        "id": "wC286A5Tdz8b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "249913fb-c484-4401-e80f-1ceea4d3a942"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "6s8MKvvceDmZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "occupation_data_query3 = '''\n",
        "INSERT INTO occupations (o_id, occupation) VALUES (?,?)\n",
        "'''\n",
        "data3 = (3, 'Carpenter')\n",
        "cursor.execute(occupation_data_query3, data3)"
      ],
      "metadata": {
        "id": "NMEQHQ82d1EB",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e9dc883c-b20a-4107-f631-14e680fd274c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "SM1wAf1yeEC-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "occupation_data_query4 = '''\n",
        "INSERT INTO occupations (o_id, occupation) VALUES (?,?)\n",
        "'''\n",
        "data4 = (4, 'Software Engineer')\n",
        "cursor.execute(occupation_data_query4, data4)"
      ],
      "metadata": {
        "id": "EUIwFO-Xd2Js",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "1231ba69-2adc-4cd4-faea-a4fd64f36cc0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "mkVqtDr7bHmh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Creating the Retirement Home table"
      ],
      "metadata": {
        "id": "Sck75TMqbIHw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "\n",
        "create_table_query2 = '''\n",
        "CREATE TABLE IF NOT EXISTS retirement_home (\n",
        "    rh_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    name TEXT,\n",
        "    location TEXT,\n",
        "    threshold_distance INTEGER\n",
        "    username text,\n",
        ")\n",
        "'''\n",
        "\n",
        "## resources TEXT REFERENCES occupation(occupation) -- keeping it here to use it later\n",
        "cursor.execute(create_table_query2)"
      ],
      "metadata": {
        "id": "CnKE6ft0WmXc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d875545a-af2f-4883-a760-4f47da41c241"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "retirement_home_data_query1 = '''\n",
        "INSERT INTO retirement_home (rh_id, name, location, threshold_distance) VALUES (?,?,?,?)\n",
        "'''\n",
        "data = (1, 'Intergenerational Retirement Home', 'New York City', 50)\n",
        "cursor.execute(retirement_home_data_query1, data)"
      ],
      "metadata": {
        "id": "h7SqmzpkbpPl",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "05e66a50-932a-47b2-a494-4ec634e3b4a3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "QV9yeYTdb9LD"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Creating the Community Organization table"
      ],
      "metadata": {
        "id": "j2sf3tRzb-Da"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "create_table_query3 = '''\n",
        "CREATE TABLE IF NOT EXISTS community_organization (\n",
        "    co_id INTEGER PRIMARY KEY,\n",
        "    name TEXT,\n",
        "    location TEXT,\n",
        "    threshold_distance INTEGER\n",
        ")\n",
        "'''\n",
        "\n",
        "cursor.execute(create_table_query3)"
      ],
      "metadata": {
        "id": "3kzw8R76Y_JH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "10fd3f3c-ce92-4d96-9ec0-6db3228294fb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "community_organization_data_query1 = '''\n",
        "INSERT INTO community_organization (co_id, name, location, threshold_distance) VALUES (?,?,?,?)\n",
        "'''\n",
        "data = (1, 'Icahn Charter 4 School', 'New York City', 50)\n",
        "cursor.execute(community_organization_data_query1, data)"
      ],
      "metadata": {
        "id": "hqAjMcRpcd4o",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "29b62368-7cd0-46ee-91d8-3ae061fac7dc"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "K51HB2ebdeC4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Creating the Occupations in Retirement Homes table"
      ],
      "metadata": {
        "id": "_kTMO0KTc_It"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the occupations_in_retirement_homes table if it does not exist\n",
        "create_table_query3 = '''\n",
        "CREATE TABLE IF NOT EXISTS occupations_in_retirement_homes (\n",
        "    oirh_id INTEGER PRIMARY KEY,\n",
        "    name TEXT,\n",
        "    count INTEGER\n",
        ")\n",
        "'''\n",
        "cursor.execute(create_table_query3)\n",
        "\n",
        "# Insert data into the occupations_in_retirement_homes table\n",
        "occupations_in_retirement_homes_data_query1 = '''\n",
        "INSERT INTO occupations_in_retirement_homes (oirh_id, name, count) VALUES (?,?,?)\n",
        "'''\n",
        "data = (1, 'Firefighter', 5)\n",
        "cursor.execute(occupations_in_retirement_homes_data_query1, data)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "r3E7UhbsgGkF",
        "outputId": "cb5e616d-e2af-4966-8496-881c12a03e71"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db_connection.commit()"
      ],
      "metadata": {
        "id": "65pp8_D8devC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Displaying Occupations Table"
      ],
      "metadata": {
        "id": "OV_4J5mkZ-xp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "select_query_occupations = '''\n",
        "SELECT * FROM occupations\n",
        "'''\n",
        "cursor.execute(select_query_occupations)"
      ],
      "metadata": {
        "id": "GNd5_QUNXv1T",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2fc68777-840b-46fa-8ade-991084bb8064"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7df931fbd340>"
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "result = cursor.fetchall()"
      ],
      "metadata": {
        "id": "X46Yu9DWXzvI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for row in result:\n",
        "    print(row)"
      ],
      "metadata": {
        "id": "-jXgfr46X1fj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e595b175-2657-47f1-c545-5a76d3f57908"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Firefighter')\n",
            "(2, 'Painter')\n",
            "(3, 'Carpenter')\n",
            "(4, 'Software Engineer')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Displaying Retirement Home Table"
      ],
      "metadata": {
        "id": "GmSMiuETglyp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "select_query_retirement_home = '''\n",
        "SELECT * FROM retirement_home\n",
        "'''\n",
        "cursor.execute(select_query_retirement_home)\n",
        "\n",
        "result = cursor.fetchall()\n",
        "\n",
        "for row in result:\n",
        "    print(row)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R9KwwPmFgIwt",
        "outputId": "78616447-e0e8-43e3-cf54-ec52251d64d4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Intergenerational Retirement Home', 'New York City', 50)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Displaying Community Organizations Table"
      ],
      "metadata": {
        "id": "-AyM-xU5gx-n"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "select_query_community_organization = '''\n",
        "SELECT * FROM community_organization\n",
        "'''\n",
        "cursor.execute(select_query_community_organization)\n",
        "\n",
        "result = cursor.fetchall()\n",
        "\n",
        "for row in result:\n",
        "    print(row)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B9voaiFigvZC",
        "outputId": "b2397892-288d-400f-e5b1-e0ce6f813c17"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Icahn Charter 4 School', 'New York City', 50)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Displaying Occupations in retirement homes Table"
      ],
      "metadata": {
        "id": "piZ7RepnhYwg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "select_query_occupations_in_retirement_homes = '''\n",
        "SELECT * FROM occupations_in_retirement_homes\n",
        "'''\n",
        "cursor.execute(select_query_occupations_in_retirement_homes)\n",
        "\n",
        "result = cursor.fetchall()\n",
        "\n",
        "for row in result:\n",
        "    print(row)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ATpG9zmQhW7N",
        "outputId": "a09a4327-c40c-45c7-cefb-877e10025b07"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Firefighter', 5)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Login Table"
      ],
      "metadata": {
        "id": "pzxQ5IRf9wXL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Notes:\n",
        "\n",
        "For sign up:\n",
        "Take username and password. Check if username exists in the DB\n",
        "  a. If it does, return error saying username already exists\n",
        "  b. If it doesn't, encrypt the password (use SHA256, or SHA512) to hash the password and store in the table.\n",
        "\n",
        "For sign in:\n",
        "Take username and password. Check if username exists in DB.\n",
        "  a. If it does, get the password of the user from the DB, decrypt it and then match the password with the string given by the user. If it works, let the user see the next page. If it doesn't, give error\n",
        "  b. If it doesn't, tell user to sign up first before logging in.\n"
      ],
      "metadata": {
        "id": "vataQnjyVG_M"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "##Login Table\n",
        "\n",
        "create_table_query3 = '''\n",
        "CREATE TABLE IF NOT EXISTS login_info (\n",
        "    login_id INTEGER PRIMARY KEY,\n",
        "    username TEXT,\n",
        "    password TEXT\n",
        "\n",
        ")\n",
        "'''\n",
        "cursor.execute(create_table_query3)"
      ],
      "metadata": {
        "id": "BNaQRorNhjzl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import hashlib\n",
        "def hash_password(password):\n",
        "    hashed_password = hashlib.sha256(password.encode('utf-8')).hexdigest()\n",
        "    return hashed_password"
      ],
      "metadata": {
        "id": "uW2d5uZVPE1F"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def create_table():\n",
        "\n",
        "    cursor.execute('''\n",
        "        CREATE TABLE IF NOT EXISTS users (\n",
        "            id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            username TEXT,\n",
        "            password TEXT\n",
        "        )\n",
        "    ''')\n",
        "\n",
        "def check_existing_username(username):\n",
        "\n",
        "    cursor.execute('SELECT * FROM users WHERE username=?', (username,))\n",
        "    user = cursor.fetchone()\n",
        "\n",
        "    return user is not None\n",
        "\n",
        "def register_user(username, password):\n",
        "\n",
        "\n",
        "\n",
        "    if check_existing_username(username):\n",
        "        print(\"Username already exists. Please choose a different username.\")\n",
        "\n",
        "        return False\n",
        "\n",
        "\n",
        "    hashed_password = hash_password(password)\n",
        "\n",
        "\n",
        "    cursor.execute('INSERT INTO users (username, password) VALUES (?, ?)', (username, hashed_password))\n",
        "\n",
        "    print(\"User registered successfully!\")\n",
        "\n",
        "\n",
        "    return True\n",
        "\n",
        "def authenticate_user(username, password):\n",
        "\n",
        "\n",
        "    cursor.execute('SELECT * FROM users WHERE username=?', (username,))\n",
        "    user = cursor.fetchone()\n",
        "\n",
        "    if user:\n",
        "        # Get the hashed password from the database\n",
        "        stored_password = user[2]\n",
        "\n",
        "        while True:\n",
        "            # Hash the provided password for comparison\n",
        "            input_password = input(\"Enter your password: \")\n",
        "\n",
        "            if input_password.lower() == 'exit':\n",
        "                print(\"Authentication aborted.\")\n",
        "                break\n",
        "\n",
        "            hashed_password = hash_password(input_password)\n",
        "\n",
        "            # Compare the hashed passwords\n",
        "            if hashed_password == stored_password:\n",
        "                print(\"Login successful!\")\n",
        "                break\n",
        "            else:\n",
        "                print(\"Invalid password. Please try again or type 'exit' to quit.\")\n",
        "    else:\n",
        "        print(\"Username not found. Please sign up.\")\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "create_table()\n"
      ],
      "metadata": {
        "id": "CRcBs8heXYEk"
      },
      "execution_count": 39,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Sign Up\n",
        "new_username = input(\"Enter a username: \")\n",
        "new_password = input(\"Enter a password: \")\n",
        "\n",
        "if register_user(new_username, new_password):\n",
        "    print(\"Registration successful!\")\n",
        "else:\n",
        "    print(\"Registration failed.\")\n"
      ],
      "metadata": {
        "id": "eYJa1etyj2YT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "# Sign In\n",
        "login_username = input(\"Enter your username: \")\n",
        "authenticate_user(login_username, \"\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Bkt2tNhahLbY",
        "outputId": "0d2919bf-1b2d-4d48-93c4-c53480106bb4"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Enter your username: berde\n",
            "Enter your password: berde12345\n",
            "Invalid password. Please try again or type 'exit' to quit.\n",
            "Enter your password: berde123\n",
            "Login successful!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Sending emails"
      ],
      "metadata": {
        "id": "WbpD8ZpMU-W4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import smtplib\n",
        "from email.mime.text import MIMEText\n",
        "from email.mime.multipart import MIMEMultipart"
      ],
      "metadata": {
        "id": "DSFGmhGFVEmx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "### we should create an email for the platform\n",
        "\n",
        "sender_email = \"priyankabose22812@gmail.com\"\n",
        "sender_password = \"gdnk qppb wcax qdbn\""
      ],
      "metadata": {
        "id": "5VaH3D6IWF75"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "recipient_email = \"priyanka.bose@nyu.edu\""
      ],
      "metadata": {
        "id": "EymrVRThWMYv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "subject = \"Subject of the email\"\n",
        "body = \"Body of the email\"\n",
        "message = MIMEMultipart()\n",
        "message['From'] = sender_email\n",
        "message['To'] = recipient_email\n",
        "message['Subject'] = subject\n",
        "message.attach(MIMEText(body, 'plain'))"
      ],
      "metadata": {
        "id": "0M9EkmJTWOl-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "smtp_server = \"smtp.gmail.com\"\n",
        "smtp_port = 587\n",
        "with smtplib.SMTP(smtp_server, smtp_port) as server:\n",
        "    # Start the TLS connection (for security)\n",
        "    server.starttls()\n",
        "\n",
        "    # Log in to the email account\n",
        "    server.login(sender_email, sender_password)\n",
        "\n",
        "    # Send the email\n",
        "    server.sendmail(sender_email, recipient_email, message.as_string())\n",
        "\n",
        "print(\"Email sent successfully!\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GkEPRKitWS6m",
        "outputId": "bdd2ecc5-f605-4169-f94a-19c2e0ac72f8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Email sent successfully!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "create_table_query4 = '''CREATE TABLE IF NOT EXISTS events (\n",
        "    event_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    event_name TEXT NOT NULL,\n",
        "    entity_id INTEGER,\n",
        "    poc_email TEXT NOT NULL,\n",
        "    creation_date DATE NOT NULL,\n",
        "    event_date DATE NOT NULL,\n",
        "\n",
        "\n",
        "    FOREIGN KEY (entity_id) REFERENCES retirement_home(rh_id)\n",
        "\n",
        ")\n",
        "'''\n",
        "\n",
        "cursor.execute(create_table_query4)"
      ],
      "metadata": {
        "id": "fEpM8LCjWmzX",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "61184641-856e-4559-bb61-d7c98353c8c1"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7efb6b1263c0>"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "-HEjkALJtpDf"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}