sqlite3_template.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyMj6XYgu9mILVWlzpScOx2X",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "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/sqlite3_template.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X0YsN4D7PGeW"
      },
      "source": [
        "Это апгрейд sqlite3 чтобы были оконные функции."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5JTquU-QKWl6"
      },
      "source": [
        "https://stackoverflow.com/a/59429952/4527289"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B0gkjBet4ueK"
      },
      "source": [
        "В следующей ячейке в конце стоит команда os.kill -- она убьет текущий рантайм. Это нужно для того, чтобы заработала обновленная версия sqlite3. Когда ты запустишь эту ячейку colab выдаст сообщение об ошибке -- так и должно быть. Просто запускай следующую ячейку."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MXSlDiZBhzlG",
        "outputId": "dfcc3ecb-ecd8-4027-c0d6-61b5bb9e3b9d"
      },
      "source": [
        "!gdown 1CDqqyKV9BvrvnuR1l8HVz0aYuM4yOTUr\n",
        "!mv _sqlite3.cpython-38-x86_64-linux-gnu.so /usr/lib/python3.8/lib-dynload/\n",
        "import os\n",
        "os.kill(os.getpid(), 9)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Downloading...\n",
            "From: https://drive.google.com/uc?id=1CDqqyKV9BvrvnuR1l8HVz0aYuM4yOTUr\n",
            "To: /content/_sqlite3.cpython-38-x86_64-linux-gnu.so\n",
            "\r  0% 0.00/6.59M [00:00<?, ?B/s]\r 72% 4.72M/6.59M [00:00<00:00, 38.6MB/s]\r100% 6.59M/6.59M [00:00<00:00, 50.5MB/s]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nUs0xd3Mhbdo"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kBuTgNeohy-d"
      },
      "source": [
        "import sqlite3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "t2TA-Ew2iR_6",
        "outputId": "f7be4e0d-2798-4d52-b862-c4747c372abf"
      },
      "source": [
        "sqlite3.sqlite_version"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'3.40.0'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aG69YxJYhrox"
      },
      "source": [
        "con = sqlite3.connect('db')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jxf4HAmTjDfb"
      },
      "source": [
        "def select(sql):\n",
        "  return pd.read_sql(sql,con)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BaV8Kwaxavrx"
      },
      "source": [
        "sql = '''Create table If Not Exists Employee (Id int, Salary int)\n",
        "insert into Employee (Id, Salary) values ('1', '100')\n",
        "insert into Employee (Id, Salary) values ('2', '200')\n",
        "insert into Employee (Id, Salary) values ('3', '300')'''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aYGGNKpWP8DB"
      },
      "source": [
        "sql = ';\\n'.join(sql.split('\\n'))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4ZNXu-VaQDvB",
        "outputId": "5dbf7414-55e3-42b8-e4a1-eee1eeba61ff"
      },
      "source": [
        "print(sql)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Create table If Not Exists Employee (Id int, Salary int);\n",
            "insert into Employee (Id, Salary) values ('1', '100');\n",
            "insert into Employee (Id, Salary) values ('2', '200');\n",
            "insert into Employee (Id, Salary) values ('3', '300')\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wptUxLd-QH6D",
        "outputId": "82c14c74-13d4-4ab0-c834-b514e277669d"
      },
      "source": [
        "con.executescript(sql)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<pysqlite3.dbapi2.Cursor at 0x7f0693b08f10>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k3jQqjH8QKGp"
      },
      "source": [
        "sql = '''select * from Employee t'''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "unxCo8kUQP4u",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 136
        },
        "outputId": "bcfe7ab6-240f-471f-ea88-adf941657f22"
      },
      "source": [
        "select(sql)"
      ],
      "execution_count": null,
      "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>Id</th>\n",
              "      <th>Salary</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>300</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Id  Salary\n",
              "0   1     100\n",
              "1   2     200\n",
              "2   3     300"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    }
  ]
}