sql_ex_postgres_in_colab.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "sql-ex-postgres-in-colab.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyNgA9pqKU36xbTMWZfsR8sT",
      "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/glebmikha/sql-course/blob/main/sql_ex_postgres_in_colab.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RRTVmrgsjTAS"
      },
      "source": [
        "# SQL-EX with Postgres"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Br9nBpDcdvUP"
      },
      "source": [
        "https://t.me/mikhaylovgleb"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-YmnxtXYJHyn",
        "outputId": "7712ff0f-9bcf-4ffa-8b95-9189449b08d1"
      },
      "source": [
        "# Install postgresql server\n",
        "!sudo apt-get -y -qq update\n",
        "!sudo apt-get -y -qq install postgresql\n",
        "!sudo service postgresql start\n",
        "\n",
        "# Setup a password `postgres` for username `postgres`\n",
        "!sudo -u postgres psql -U postgres -c \"ALTER USER postgres PASSWORD 'postgres';\""
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76, <> line 10.)\n",
            "debconf: falling back to frontend: Readline\n",
            "debconf: unable to initialize frontend: Readline\n",
            "debconf: (This frontend requires a controlling tty.)\n",
            "debconf: falling back to frontend: Teletype\n",
            "dpkg-preconfigure: unable to re-open stdin: \n",
            "Selecting previously unselected package cron.\n",
            "(Reading database ... 160690 files and directories currently installed.)\n",
            "Preparing to unpack .../0-cron_3.0pl1-128.1ubuntu1_amd64.deb ...\n",
            "Unpacking cron (3.0pl1-128.1ubuntu1) ...\n",
            "Selecting previously unselected package logrotate.\n",
            "Preparing to unpack .../1-logrotate_3.11.0-0.1ubuntu1_amd64.deb ...\n",
            "Unpacking logrotate (3.11.0-0.1ubuntu1) ...\n",
            "Selecting previously unselected package netbase.\n",
            "Preparing to unpack .../2-netbase_5.4_all.deb ...\n",
            "Unpacking netbase (5.4) ...\n",
            "Selecting previously unselected package postgresql-client-common.\n",
            "Preparing to unpack .../3-postgresql-client-common_190ubuntu0.1_all.deb ...\n",
            "Unpacking postgresql-client-common (190ubuntu0.1) ...\n",
            "Selecting previously unselected package postgresql-client-10.\n",
            "Preparing to unpack .../4-postgresql-client-10_10.16-0ubuntu0.18.04.1_amd64.deb ...\n",
            "Unpacking postgresql-client-10 (10.16-0ubuntu0.18.04.1) ...\n",
            "Selecting previously unselected package ssl-cert.\n",
            "Preparing to unpack .../5-ssl-cert_1.0.39_all.deb ...\n",
            "Unpacking ssl-cert (1.0.39) ...\n",
            "Selecting previously unselected package postgresql-common.\n",
            "Preparing to unpack .../6-postgresql-common_190ubuntu0.1_all.deb ...\n",
            "Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'\n",
            "Unpacking postgresql-common (190ubuntu0.1) ...\n",
            "Selecting previously unselected package postgresql-10.\n",
            "Preparing to unpack .../7-postgresql-10_10.16-0ubuntu0.18.04.1_amd64.deb ...\n",
            "Unpacking postgresql-10 (10.16-0ubuntu0.18.04.1) ...\n",
            "Selecting previously unselected package postgresql.\n",
            "Preparing to unpack .../8-postgresql_10+190ubuntu0.1_all.deb ...\n",
            "Unpacking postgresql (10+190ubuntu0.1) ...\n",
            "Selecting previously unselected package sysstat.\n",
            "Preparing to unpack .../9-sysstat_11.6.1-1ubuntu0.1_amd64.deb ...\n",
            "Unpacking sysstat (11.6.1-1ubuntu0.1) ...\n",
            "Setting up sysstat (11.6.1-1ubuntu0.1) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "\n",
            "Creating config file /etc/default/sysstat with new version\n",
            "update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode\n",
            "Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.\n",
            "Setting up ssl-cert (1.0.39) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "Setting up cron (3.0pl1-128.1ubuntu1) ...\n",
            "Adding group `crontab' (GID 110) ...\n",
            "Done.\n",
            "Created symlink /etc/systemd/system/multi-user.target.wants/cron.service → /lib/systemd/system/cron.service.\n",
            "update-rc.d: warning: start and stop actions are no longer supported; falling back to defaults\n",
            "invoke-rc.d: could not determine current runlevel\n",
            "invoke-rc.d: policy-rc.d denied execution of start.\n",
            "Setting up logrotate (3.11.0-0.1ubuntu1) ...\n",
            "Setting up netbase (5.4) ...\n",
            "Setting up postgresql-client-common (190ubuntu0.1) ...\n",
            "Setting up postgresql-common (190ubuntu0.1) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "Adding user postgres to group ssl-cert\n",
            "\n",
            "Creating config file /etc/postgresql-common/createcluster.conf with new version\n",
            "Building PostgreSQL dictionaries from installed myspell/hunspell packages...\n",
            "Removing obsolete dictionary files:\n",
            "Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.\n",
            "invoke-rc.d: could not determine current runlevel\n",
            "invoke-rc.d: policy-rc.d denied execution of start.\n",
            "Setting up postgresql-client-10 (10.16-0ubuntu0.18.04.1) ...\n",
            "update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode\n",
            "Setting up postgresql-10 (10.16-0ubuntu0.18.04.1) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "Creating new PostgreSQL cluster 10/main ...\n",
            "/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5\n",
            "The files belonging to this database system will be owned by user \"postgres\".\n",
            "This user must also own the server process.\n",
            "\n",
            "The database cluster will be initialized with locale \"en_US.UTF-8\".\n",
            "The default database encoding has accordingly been set to \"UTF8\".\n",
            "The default text search configuration will be set to \"english\".\n",
            "\n",
            "Data page checksums are disabled.\n",
            "\n",
            "fixing permissions on existing directory /var/lib/postgresql/10/main ... ok\n",
            "creating subdirectories ... ok\n",
            "selecting default max_connections ... 100\n",
            "selecting default shared_buffers ... 128MB\n",
            "selecting default timezone ... Etc/UTC\n",
            "selecting dynamic shared memory implementation ... posix\n",
            "creating configuration files ... ok\n",
            "running bootstrap script ... ok\n",
            "performing post-bootstrap initialization ... ok\n",
            "syncing data to disk ... ok\n",
            "\n",
            "Success. You can now start the database server using:\n",
            "\n",
            "    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start\n",
            "\n",
            "Ver Cluster Port Status Owner    Data directory              Log file\n",
            "\u001b[31m10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log\u001b[0m\n",
            "update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode\n",
            "invoke-rc.d: could not determine current runlevel\n",
            "invoke-rc.d: policy-rc.d denied execution of start.\n",
            "Setting up postgresql (10+190ubuntu0.1) ...\n",
            "Processing triggers for man-db (2.8.3-2ubuntu0.1) ...\n",
            "Processing triggers for systemd (237-3ubuntu10.46) ...\n",
            " * Starting PostgreSQL 10 database server\n",
            "   ...done.\n",
            "ALTER ROLE\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uefZ6RdnJN3t",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6de42ada-26d7-4923-bf14-0092a50822f6"
      },
      "source": [
        "from sqlalchemy import create_engine\n",
        "con = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres')"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
            "  \"\"\")\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DIP_iS7Eg2DY"
      },
      "source": [
        "Скачиваем данные с sql-ex"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4uFTDcBeg6NI",
        "outputId": "2baa60ea-47cc-4e13-fb7b-e6da015aa63b"
      },
      "source": [
        "!wget https://sql-ex.ru/download/sql-ex-pg.sql"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-05-06 16:35:14--  https://sql-ex.ru/download/sql-ex-pg.sql\n",
            "Resolving sql-ex.ru (sql-ex.ru)... 136.243.105.254, 2a01:4f8:171:ed3:ce0:a1ff:fee0:bb42\n",
            "Connecting to sql-ex.ru (sql-ex.ru)|136.243.105.254|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 41053 (40K) [application/x-sql]\n",
            "Saving to: ‘sql-ex-pg.sql’\n",
            "\n",
            "sql-ex-pg.sql       100%[===================>]  40.09K  --.-KB/s    in 0s      \n",
            "\n",
            "2021-05-06 16:35:15 (302 MB/s) - ‘sql-ex-pg.sql’ saved [41053/41053]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E4aZBrOXiJXw"
      },
      "source": [
        "with open('/content/sql-ex-pg.sql', 'r') as file:\n",
        "    sql = file.read()"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hcBzIzmrjfMx"
      },
      "source": [
        "Запускаем скрипт"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RlV-FuCxiby3",
        "outputId": "29e4a24e-c9b0-4ca6-ab14-e83f1286bd68"
      },
      "source": [
        "con.execute(sql[1:])"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f468657c550>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R5kFp7-mYcm_"
      },
      "source": [
        "Сделаю еще свою знаменитую функцию select, которая заворачивает пандосовкую функцию. Нужно для того, чтобы каждый раз не писать всю эту авадакедавру, а писать красивенький селект"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pwHWvKDVY43E"
      },
      "source": [
        "import pandas as pd"
      ],
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QcDB9yxOYtEU"
      },
      "source": [
        "def select(sql):\n",
        "  return pd.read_sql(sql,con)"
      ],
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NnajuWiuX1aV"
      },
      "source": [
        "# Пример решения задачи"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "npomPLFNYE0W"
      },
      "source": [
        "Возьмем например это задание https://sql-ex.ru/learn_exercises.php?LN=3"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eoo6FtWCYDqv"
      },
      "source": [
        "Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zHLbU36xaJ5r"
      },
      "source": [
        "Сначала глянем на таблу с ПК-блокнотами, ну т.е. ноутбуками"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t1MPjEubaISI"
      },
      "source": [
        "sql = '''select * from laptop t'''"
      ],
      "execution_count": 21,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J4AB2phgaUST",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 225
        },
        "outputId": "c105ecf0-5eba-4103-f92a-029340e385e6"
      },
      "source": [
        "select(sql)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>code</th>\n",
              "      <th>model</th>\n",
              "      <th>speed</th>\n",
              "      <th>ram</th>\n",
              "      <th>hd</th>\n",
              "      <th>price</th>\n",
              "      <th>screen</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>1298</td>\n",
              "      <td>350</td>\n",
              "      <td>32</td>\n",
              "      <td>4.0</td>\n",
              "      <td>700.0</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>1321</td>\n",
              "      <td>500</td>\n",
              "      <td>64</td>\n",
              "      <td>8.0</td>\n",
              "      <td>970.0</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>1750</td>\n",
              "      <td>750</td>\n",
              "      <td>128</td>\n",
              "      <td>12.0</td>\n",
              "      <td>1200.0</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>1298</td>\n",
              "      <td>600</td>\n",
              "      <td>64</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1050.0</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>1752</td>\n",
              "      <td>750</td>\n",
              "      <td>128</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1150.0</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6</td>\n",
              "      <td>1298</td>\n",
              "      <td>450</td>\n",
              "      <td>64</td>\n",
              "      <td>10.0</td>\n",
              "      <td>950.0</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   code model  speed  ram    hd   price  screen\n",
              "0     1  1298    350   32   4.0   700.0      11\n",
              "1     2  1321    500   64   8.0   970.0      12\n",
              "2     3  1750    750  128  12.0  1200.0      14\n",
              "3     4  1298    600   64  10.0  1050.0      15\n",
              "4     5  1752    750  128  10.0  1150.0      14\n",
              "5     6  1298    450   64  10.0   950.0      12"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S_ikWZLCaTq1"
      },
      "source": [
        "Теперь напишем сам запрос"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rpIxFIYtYV3i"
      },
      "source": [
        "sql = '''\n",
        "select t.model, t.ram, t.screen from laptop t\n",
        "where t.price > 1000\n",
        "'''"
      ],
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tXg9cBN7Ya44",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 136
        },
        "outputId": "2f678385-dea0-4c74-82f6-d757ca7e0a41"
      },
      "source": [
        "select(sql)"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>model</th>\n",
              "      <th>ram</th>\n",
              "      <th>screen</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1750</td>\n",
              "      <td>128</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1298</td>\n",
              "      <td>64</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1752</td>\n",
              "      <td>128</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  model  ram  screen\n",
              "0  1750  128      14\n",
              "1  1298   64      15\n",
              "2  1752  128      14"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YSiSRXlzan1S"
      },
      "source": [
        "Вроде ок. Теперь выбираем на sql-ex  упражнения с выбором базы данных https://sql-ex.ru/exercises/index.php?act=learn, выбираем postgres, и копируем туда sql код (только то, что между тройных кавычек).\n",
        "\n",
        "На sql-ex еще есть проверочные базы, которых у нас нет. И решение может не приниматься, потому что не проходит проверку на проверочной базе. Здесь, либо подумай, как переписать запрос более умно, либо если совсем застрял -- иди к следующему упражнению, а к этому вернешься чуть позже, когда нарастут sql-мышцы).\n",
        "\n",
        "Я понмю, что иногда если добавишь в начало запроса distinct, то проверочная база может принять. Лайфхак такой. Немного дистинкта никогда не повредит). Но лучше потести это дистинк и в колабе, чтобы понимать что он не нарушает логики твоего запроса."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5JNU1nx1L9Uq"
      },
      "source": [
        "https://www.glebmikhaylov.com/"
      ]
    }
  ]
}