chapters/10.4/zip.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ppNdnF-Xu8qX"
      },
      "source": [
        "[矢吹太朗『Webのしくみ』（サイエンス社, 2020）](https://github.com/taroyabuki/webbook)\n",
        "\n",
        "下のアイコンをクリックすれば，この文書に掲載されているコードを，Google Colab上で実行できます．（Googleのアカウントが必要です．）\n",
        "\n",
        "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/taroyabuki/webbook/blob/master/chapters/10.4/zip.ipynb)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FZZy6VirilZq"
      },
      "source": [
        "# 郵便番号データベースによる体験SQL\n",
        "\n",
        "郵便番号データを使って，SQLでの検索を体験します．\n",
        "\n",
        "まずは準備です．\n",
        "作成済みのデータベースを読み込んで，操作できるようにします．\n",
        "この部分は，理解せずに実行するだけでかまいません．\n",
        "（もちろん，理解しようとしてもかまいません．理解してもかまいません．）\n",
        "\n",
        "コードにカーソルを置いて，左側の▶ボタンをクリックするかShift+Enterで実行してください．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7TxNa1w21LKW"
      },
      "outputs": [],
      "source": [
        "# このセルは準備のためのものなので，理解できなくてかまいません．\n",
        "!wget --quiet https://github.com/taroyabuki/webbook/raw/master/chapters/10.4/zip.sqlite3\n",
        "import pandas as pd\n",
        "import sqlite3\n",
        "con = sqlite3.connect('zip.sqlite3')\n",
        "def 実行(sql): return pd.read_sql_query(sql, con)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oCBO5m4akAkL"
      },
      "source": [
        "## 例1：全データ取得\n",
        "\n",
        "まずは，\n",
        "\n",
        "```\n",
        "zipという表から，\n",
        "すべての列を取り出す．\n",
        "```\n",
        "\n",
        "という操作をしてみます．\n",
        "次のように翻訳します．\n",
        "\n",
        "```\n",
        "zipという表から， → from zip\n",
        "すべての列を取り出す． → select *\n",
        "```\n",
        "\n",
        "並び替えてSQL文とします．\n",
        "\n",
        "```\n",
        "select *\n",
        "from zip\n",
        "```\n",
        "\n",
        "実行します．\n",
        "\n",
        "```\n",
        "実行('''\n",
        "SQL文\n",
        "''')\n",
        "```\n",
        "\n",
        "↑のように書くと，SQL文が実行されるようにしてあります．\n",
        "ですから，ここで作ったSQL文は，次のように実行します．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rczFji-RMpS7"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select *\n",
        "from zip\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s_5j4aJmj6lV"
      },
      "source": [
        "## 例2：件数\n",
        "\n",
        "`select count(*)`として，件数を得ます．補足：例1の結果にも表示されていますが，それはSQLではなくPythonによるものです．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "r5Q7tpKiM3kq"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select count(*)\n",
        "from zip\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cf8DmjPokjx3"
      },
      "source": [
        "## 例3：条件\n",
        "\n",
        "`where 条件`として，条件を設定します．\n",
        "「where」は「場所」というよりは「場合」のことだと考えましょう．"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5LcFzetWmWSM"
      },
      "source": [
        "### 例3.1：完全一致\n",
        "\n",
        "等号は`=`，等号否定は`!=`や`<>`です．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kNooqDRkM_PK"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select *\n",
        "from zip\n",
        "where code = '2758588'\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oPicOt2BmcNg"
      },
      "source": [
        "### 例3.2 部分一致\n",
        "\n",
        "任意の文字列を「`%`」で表し，「`%`」を含む文字列との比較を`like`や`not like`で行います．\n",
        "\n",
        "27から始まる郵便番号を検索します．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GPBEBMrvNMU7"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select *\n",
        "from zip\n",
        "where code like '27%'\n",
        "''')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "q9AX0DN2qfgT"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select count(*)\n",
        "from zip\n",
        "where code like '27%'\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h0eQlPYImv2L"
      },
      "source": [
        "### 例3.3 条件の組合せ\n",
        "\n",
        "条件を組み合わせるときは，`and`や`or`を使います．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qC484HL7NdNS"
      },
      "outputs": [],
      "source": [
        "実行('''\n",
        "select *\n",
        "from zip\n",
        "where address1 = '北海道' and address2 like '%中央区%'\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "USBpBduvnurB"
      },
      "source": [
        "### 練習\n",
        "\n",
        "officeに「千葉工」を含むデータの件数を求めてみましょう．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nFo8eRFzR_Ez"
      },
      "outputs": [],
      "source": [
        "# ここにコードを書いて，実行して試してみてください．（いろいろ試したい場合は，左上の「＋ コード」をクリックして，セルを追加するといいでしょう．）\n",
        "実行('''\n",
        "\n",
        "''')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_xZ6wwXRxIgN"
      },
      "source": [
        "# 補足：Pythonだけで完結させる（やらなくていいです）\n",
        "\n",
        "Pythonは基本的には手続き型のプログラミング言語ですが，表形式のデータを宣言的に扱うしくみが用意されています．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tEf7OHu54UmR"
      },
      "outputs": [],
      "source": [
        "# 準備\n",
        "import pandas as pd\n",
        "data = pd.read_csv('https://github.com/taroyabuki/webbook/raw/master/chapters/10.4/zip.csv',\n",
        "                   converters={0:str},    # 0列目は文字列\n",
        "                   keep_default_na=False) # 空白をNaNにしない"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AisqXGizocSs"
      },
      "outputs": [],
      "source": [
        "# 全データ\n",
        "data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4q3cSdspceA0"
      },
      "outputs": [],
      "source": [
        "# 完全一致\n",
        "data.query(\"code == '2758588'\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6zmQr5tIzNu8"
      },
      "outputs": [],
      "source": [
        "# 部分一致（27で始まる郵便番号）\n",
        "# startswithをcontainsにすると「・・・を含む」になる．\n",
        "# startswithをendswithにすると「・・・で終わる」になる．\n",
        "data.query(\"code.str.startswith('27')\", engine='python')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Zn5NsaP4LWbN"
      },
      "outputs": [],
      "source": [
        "# 条件の組合せ\n",
        "data.query(\"address1 == '北海道' and address2.str.contains('中央区')\", engine='python')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eKx6SsmEpnYN"
      },
      "source": [
        "### 練習\n",
        "\n",
        "officeに「千葉工」を含むデータの件数を求めてみましょう．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6bUVDOWoSSo0"
      },
      "outputs": [],
      "source": [
        "# ここにコードを書いて，実行して試してみてください．（いろいろ試したい場合は，左上の「＋ コード」をクリックして，セルを追加するといいでしょう．）"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XGPgtb8dPrmB"
      },
      "source": [
        "# 補足：シェルだけで完結させる（やらなくていいです）\n",
        "\n",
        "ここでやっている程度の話は，Pythonのような汎用プログラミング言語を持ち出すまでもなく，シェルスクリプトという簡易的なプログラムでも解決できます．\n",
        "\n",
        "参考書\n",
        "\n",
        "- [カーニハン，パイク『UNIXプログラミング環境』](https://calil.jp/search?q=UNIX%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E7%92%B0%E5%A2%83)（1985年版と2017年版のどちらを参照してもかまいません．）\n",
        "- [エイホほか『プログラミング言語AWK』](https://calil.jp/search?q=%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E8%A8%80%E8%AA%9EAWK)（第1版と第2版のどちらを参照してもかまいません．）\n",
        "\n",
        "以下，「`-F,`」は「コンマで分割する」という意味です．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "99KvQN7qscjX"
      },
      "outputs": [],
      "source": [
        "# ツールのインストール（管理者権限が必要）\n",
        "!apt-get install gawk | tail -n 1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jaunZim0P1NN"
      },
      "outputs": [],
      "source": [
        "# 準備\n",
        "!wget --quiet https://github.com/taroyabuki/webbook/raw/master/chapters/10.4/zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "K8mABv21pw8A"
      },
      "outputs": [],
      "source": [
        "# データの形式\n",
        "!head zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o4w34WouP5IQ"
      },
      "outputs": [],
      "source": [
        "# 件数（この結果から見出しの分つまり1を引く）\n",
        "!wc -l zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "K4PUSYWLePma"
      },
      "outputs": [],
      "source": [
        "# 完全一致\n",
        "!awk -F, '$1==\"\\\"2758588\\\"\"' zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# 部分一致（27で始まる郵便番号．headを付けて最初の方を表示する．）\n",
        "!awk -F, '$1~/\"27/' zip.csv | head"
      ],
      "metadata": {
        "id": "2hQ_RGNLtTh4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HAweDM06c1qk"
      },
      "outputs": [],
      "source": [
        "# 部分一致（「 | wc -l」を付けて件数を数える．）\n",
        "!awk -F, '$1~/\"27/' zip.csv | wc -l"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CfrzW-MMdJZ3"
      },
      "outputs": [],
      "source": [
        "# 条件の組合せ\n",
        "!awk -F, '$2==\"\\\"北海道\\\"\" && $3~/中央区/' zip.csv | head"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wV6bXq6Kreaq"
      },
      "outputs": [],
      "source": [
        "!awk -F, '$2==\"\\\"北海道\\\"\" && $3~/中央区/' zip.csv | wc -l"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1185FTn7rj3J"
      },
      "source": [
        "### 練習\n",
        "\n",
        "officeに「千葉工」を含むデータの件数を求めてみましょう．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "m8MqtdR9SU9q"
      },
      "outputs": [],
      "source": [
        "# ここにコードを書いて，実行して試してみてください．（いろいろ試したい場合は，左上の「＋ コード」をクリックして，セルを追加するといいでしょう．）\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zJjltowexneN"
      },
      "source": [
        "# （気が強い人のための）補足：データベースの作り方\n",
        "\n",
        "**最新版のデータを使うため，上に掲載したのとは，違う結果を得る可能性があります．**\n",
        "\n",
        "日本の郵便番号のデータは，https://www.post.japanpost.jp/zipcode/download.html で，CSV形式で公開されています．\n",
        "このデータをダウンロードして，データベースを作ります．\n",
        "\n",
        "SQLite3というデータベース管理システムを使います．\n",
        "SQLite3の操作はPythonで行います．"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iWU5VpCFscjZ"
      },
      "outputs": [],
      "source": [
        "# ツールのインストール（管理者権限が必要）\n",
        "!apt-get install nkf gawk -y | tail -n 1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lItTaYnW3cyA"
      },
      "outputs": [],
      "source": [
        "# 準備\n",
        "!rm -f *.zip *.csv *.CSV # 作業ファイルの削除\n",
        "\n",
        "# ダウンロード\n",
        "!wget --quiet https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip\n",
        "!wget --quiet https://www.post.japanpost.jp/zipcode/dl/jigyosyo/zip/jigyosyo.zip\n",
        "\n",
        "# 展開\n",
        "!unzip -q ken_all.zip\n",
        "!unzip -q jigyosyo.zip\n",
        "\n",
        "# zip.csvの作成\n",
        "!echo 'code,address1,address2,address3,address4,office' > zip.csv\n",
        "\n",
        "# 文字コードの変換と必要なデータの抽出\n",
        "!nkf -Lu -w KEN_ALL.CSV | gawk -F, -v OFS=\",\" '{print $3,$7,$8,$9,\"\\\"\\\"\",\"\\\"\\\"\"}'  >> zip.csv\n",
        "!nkf -Lu -w JIGYOSYO.CSV | gawk -F, -v OFS=\",\" '{print $8,$4,$5,$6,$7,$3}' >> zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zQ0NeSAR81cN"
      },
      "outputs": [],
      "source": [
        "# 元のデータはこんな感じ（nkf -wでUTF-8に変換してから，headで先頭部分を表示）\n",
        "!nkf -w KEN_ALL.CSV | head"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YL5osQxDzTe9"
      },
      "outputs": [],
      "source": [
        "# 抽出後のデータはこんな感じ\n",
        "!head zip.csv"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# データの件数\n",
        "!wc -l zip.csv"
      ],
      "metadata": {
        "id": "DHwQik9YdgKA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_h7IXXJp2TeE"
      },
      "source": [
        "一度Pythonで読み込んで，SQLiteのデータベースにデータを挿入する．（zip.sqlite3というファイルができる．これが，作りたかったものである．）"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "goFpFxeI3lK8"
      },
      "outputs": [],
      "source": [
        "# Pythonで読み込む\n",
        "import pandas as pd\n",
        "data = pd.read_csv('zip.csv', converters={0:str}, keep_default_na=False)\n",
        "\n",
        "import sqlite3\n",
        "!rm -f zip.sqlite3                   # 既存のデータベースの削除\n",
        "con = sqlite3.connect('zip.sqlite3') # データベースの作成\n",
        "data.to_sql('zip', con, index=False) # データの挿入\n",
        "cur = con.cursor()                   # インデックスの作成（番号での検索を高速化するため）\n",
        "cur.execute('create index code_idx on zip (code)')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "H87iduIY44kQ"
      },
      "outputs": [],
      "source": [
        "# 動作確認2\n",
        "pd.read_sql_query('select * from zip limit 10', con)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8a5hDFjq463Y"
      },
      "outputs": [],
      "source": [
        "# データベースを閉じて終了\n",
        "con.close()"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "zip のコピー",
      "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.8"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}