DA_HW4.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PtKcpEMzoJbe"
      },
      "source": [
        "<div style=\"display:block\" direction=rtl align=right><br><br>\n",
        "    <div  style=\"width:100%;margin:100;display:block\"  display=block align=center>\n",
        "        <img width=130 align=right src=\"https://i.ibb.co/yXKQmtZ/logo1.png\" style=\"margin:0;\" />\n",
        "        <img width=170 align=left  src=\"https://i.ibb.co/wLjqFkw/logo2.png\" style=\"margin:0;\" />\n",
        "        <span><br><font size=5>University of Tehran , school of ECE</font></span>\n",
        "        <span><br><font size=3>Data Analytics Course</font></span>\n",
        "        <span><br><font size=3>Fall 2022</font></span>\n",
        "    </div><br><br><br>\n",
        "    <div style=\"display:block\" align=left display=block> \n",
        "        <font size=3>Homework 4</font><br>\n",
        "        <hr />\n",
        "        <font size=3>TA: <a href=\"mailto:p.zilouchian@ut.ac.ir\">Parham Zilouchian</a><br></font><br>\n",
        "    </div>\n",
        "</div>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "u1tqLWgNoJbi"
      },
      "source": [
        "# **Working with Database Systems**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IZr8AKLdojlO"
      },
      "source": [
        "## **Part1 - EDA and Visualization**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Setup"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            " * Starting PostgreSQL 12 database server\n",
            "   ...done.\n"
          ]
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper\n",
            "sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper\n",
            "sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper\n",
            "sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper\n",
            "sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper\n",
            "\n",
            "[notice] A new release of pip available: 22.2.2 -> 22.3.1\n",
            "[notice] To update, run: pip install --upgrade pip\n"
          ]
        }
      ],
      "source": [
        "%%bash\n",
        "# Install postgresql server\n",
        "sudo apt-get -y -qq update\n",
        "sudo apt-get -y -qq install postgresql\n",
        "sudo service postgresql start\n",
        "# Setup a password `postgres` for username `postgres`\n",
        "# TODO: read from the %env file\n",
        "sudo -u postgres psql -U postgres -c \"ALTER USER postgres PASSWORD postgres;\"\n",
        "sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS database_demo;'\n",
        "sudo -u postgres psql -U postgres -c 'CREATE DATABASE database_demo;'\n",
        "sudo -u postgres psql -U postgres -c \"CREATE USER postgres WITH PASSWORD 'postgres';\"\n",
        "sudo -u postgres psql -U postgres -c \"GRANT ALL PRIVILEGES ON DATABASE database_demo to postgres;\"\n",
        "# install\n",
        "pip install ipython-sql psycopg2 sqlalchemy  --quiet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%%bash\n",
        "mkdir birhts\n",
        "for year in {91..99} 1400\n",
        "do\n",
        "     curl -o birhts/$year.xlsx -s https://github.com/ahimico/da-postgres/blob/main/contents/veladat-$year.xlsx?raw=true\n",
        "done"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "env: DATABASE_NAME=database_demo\n",
            "env: DATABASE_HOST=localhost\n",
            "env: DATABASE_PORT=5432\n",
            "env: DATABASE_USER=postgres\n",
            "env: DATABASE_PASS=postgres\n"
          ]
        }
      ],
      "source": [
        "%env DATABASE_NAME=database_demo\n",
        "%env DATABASE_HOST=localhost\n",
        "%env DATABASE_PORT=5432\n",
        "%env DATABASE_USER=postgres\n",
        "%env DATABASE_PASS=postgres"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "endpoint='postgresql://postgres:postgres@localhost?port=5432&dbname=database_demo'\n",
            "(psycopg2.OperationalError) FATAL:  password authentication failed for user \"postgres\"\n",
            "FATAL:  password authentication failed for user \"postgres\"\n",
            "\n",
            "(Background on this error at: https://sqlalche.me/e/14/e3q8)\n",
            "Connection info needed in SQLAlchemy format, example:\n",
            "               postgresql://username:password@hostname/dbname\n",
            "               or an existing connection: dict_keys([])\n"
          ]
        }
      ],
      "source": [
        "import sqlalchemy \n",
        "import os\n",
        "\n",
        "endpoint=\"postgresql://{}:{}@{}?port={}&dbname={}\".format(\n",
        "    os.environ['DATABASE_USER'],\n",
        "    os.environ['DATABASE_PASS'],\n",
        "    os.environ['DATABASE_HOST'],\n",
        "    os.environ['DATABASE_PORT'],\n",
        "    os.environ['DATABASE_NAME'],\n",
        ")\n",
        "\n",
        "print(f\"{endpoint=}\")\n",
        "sqlalchemy.create_engine(endpoint)\n",
        "# %load_ext sql\n",
        "\n",
        "%sql $endpoint"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Environment variable $DATABASE_URL not set, and no connect string given.\n",
            "Connection info needed in SQLAlchemy format, example:\n",
            "               postgresql://username:password@hostname/dbname\n",
            "               or an existing connection: dict_keys([])\n"
          ]
        }
      ],
      "source": [
        "%%sql\n",
        "CREATE TABLE birthdays (\n",
        "    id SERIAL PRIMARY KEY,\n",
        "    date DATE,\n",
        "    men_population INT,\n",
        "    wemen_population INT,\n",
        "    province VARCHAR(100),\n",
        ");"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Environment variable $DATABASE_URL not set, and no connect string given.\n",
            "Connection info needed in SQLAlchemy format, example:\n",
            "               postgresql://username:password@hostname/dbname\n",
            "               or an existing connection: dict_keys([])\n"
          ]
        }
      ],
      "source": [
        "%%sql \n",
        "SELECT table_name\n",
        "FROM information_schema.tables\n",
        "WHERE table_schema = 'public'\n",
        "ORDER BY table_name;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3LATlXmarB4K"
      },
      "source": [
        "#### **Section 1. Just some points :)**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CvIGYwp6rCC_"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pzt5ux-prN9t"
      },
      "source": [
        "#### **Section 2. Write Query using Psycopg2**\n",
        "\n",
        "```\n",
        "# This is formatted as code\n",
        "```\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ah5yNbuWrrm6"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_P8tauZfrryg"
      },
      "source": [
        "##### **a. Total births**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9VMZkADnr3jO"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jgnz5Urxr335"
      },
      "source": [
        "##### **b. Total births divide by gender**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yqL4Xj06sEcT"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fSIn86XjsEoF"
      },
      "source": [
        "##### **c. Most births in last five years**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3GPqu0lKsnTg"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Vxm-ftnNsneR"
      },
      "source": [
        "##### **d. Least births in last five years**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7Vy4t9nCs6zU"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CRqmmAeHs6_S"
      },
      "source": [
        "##### **e. Most Birth-rate**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KPWNBtmBtTOz"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4ZdBspiPtTal"
      },
      "source": [
        "##### **f. Least Birth-rate**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0f9bNjSNt_El"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1oa5m4K9vkwL"
      },
      "source": [
        "##### **g. Birth number and rate in each season!**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JKgBVBr1v3FI"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "muR6qA9qoq79"
      },
      "source": [
        "## **Part2 - Working with QGIS**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sY79-KuKqjDz"
      },
      "source": [
        "#### **Section A: Load Data**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "x_ioHlx8qqBl"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kyvynTxaqjG-"
      },
      "source": [
        "#### **Section B: Answer Questions**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tsmj9bqZq1xn"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vnvc_1cYq1z1"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "49_YIPgcq129"
      },
      "outputs": [],
      "source": [
        "# PC"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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.8.13"
    },
    "orig_nbformat": 4,
    "vscode": {
      "interpreter": {
        "hash": "9ac03a0a6051494cc606d484d27d20fce22fb7b4d169f583271e11d5ba46a56e"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}