MCP Python SDKとGoogle ADKでMCPサーバ（ODBC経由PostgreSQL接続）を試してみた

## 追記：
- MCP Python SDKがStreamable HTTPに対応したよう

    参考：<https://zenn.dev/5enxia/articles/82179743b62296>

- MCP Python SDKでstreamable-httpオプションを使わない実装例はGoogle ADKのブログがわかりやすい

    参考：<https://cloud.google.com/blog/topics/developers-practitioners/use-google-adk-and-mcp-with-an-external-server?hl=en>

ーー
こんにちは。あかいです。  
この記事は、勉強のためにMCPサーバを試した際の備忘録です。
2025/05/06時点で、Transport方法にStreamable HTTPが追加されていますが、MCP Python SDKで未実装のため、自前実装せずに旧SSEで試しています。
また、MCPサーバのresources、prompts、toolsのうち、toolsのみの検証です（adk webコマンドで起動するGUIからのresources等の参照方法を見つけられなかったため）。
その他の方針は[こちら](#方針)。

<!-- omit in toc -->
## 目次

- [MCPとは](#mcpとは)
  - [概要（MCPプロトコル要求）](#概要mcpプロトコル要求)
  - [実装](#実装)
    - [クライアント実装](#クライアント実装)
    - [サーバ実装](#サーバ実装)
- [お試し](#お試し)
  - [方針](#方針)
  - [構築](#構築)
    - [依存関係](#依存関係)
    - [ディレクトリ構成](#ディレクトリ構成)
    - [構築手順](#構築手順)
    - [起動手順](#起動手順)

## MCPとは

2025-03-26(latest) @2025/05/06

### 概要（MCPプロトコル要求）

参考：[Introducing the Model Context Protocol \ Anthropic](https://www.anthropic.com/news/model-context-protocol)

- LLMにコンテキストを与えるためのオープンプロトコル（≠実装）
- MCPホスト、MCPクライアント、MCPサーバからなる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/325677/1f8511ca-f0f0-4ffd-ae96-30299b5973c9.png)

  - MCP Hosts: Programs like Claude Desktop, IDEs, or AI tools that want to access data through MCP
  - MCP Clients: Protocol clients that maintain 1:1 connections with servers
  - MCP Servers: Lightweight programs that each expose specific capabilities through the standardized Model Context Protocol
  - Local Data Sources: Your computer’s files, databases, and services that MCP servers can securely access
  - Remote Services: External systems available over the internet (e.g., through APIs) that MCP servers can connect to

- ベースとなるプロトコル: JSON-RPC 2.0

    ※ バッチ処理可  
    ※ UTF-8

  - Request(クライアント<->サーバ)

    ```json
    {
        jsonrpc: "2.0";
        id: string | number;
        method: string;
        params?: {
            [key: string]: unknown;
        };
    }
    ```

  - Response(クライアント<->サーバ)

    ```json
    {
        jsonrpc: "2.0";
        id: string | number;
        result?: {
            [key: string]: unknown;
        }
        error?: {
            code: number;
            message: string;
            data?: unknown;
        }
    }
    ```

  - Notification(クライアント->サーバ、サーバ->クライアント)

    ```json
    {
        jsonrpc: "2.0";
        method: string;
        params?: {
            [key: string]: unknown;
        };
    }
    ```

- トランスポート
  参考: <https://zenn.dev/atsukish/articles/e4e1009ab19c3c>
  - 2種の通信方式
    - STDIO
      - MCPクライアントがMCPサーバをサブプロセスとして起動
      - STDIN/STDOUTを使用
      - メッセージは改行区切り
    - Streamable HTTP
      - サーバは1つのHTTPエンドポイントを用意
      - POST、GETを使用
        - POST: JSON-RPCメッセージの送信（クライアント<->サーバ）
        - GET: SSEストリームの開始要求（クライアント->サーバ）
      - レスポンスコード
        - 202: 受付
        - 405: SSEストリーム非対応
        - その他エラーコード: その他エラー時
      - SSE (Server-Sent Events)ストリーム
        - クライアント、サーバ両方からいつでも閉じてよい
        - クライアント:サーバ = 多:多
        - ネットワーク切断後に再開可能
    - (SSE): 
      - 古いプロトコル
      - SSE用エンドポイントと、POST用エンドポイントを設ける

- 認証・認可
  - HTTP利用時: HTTPベースの認証
  - STDIO利用時: 環境からクレデンシャルを取得

- スキーマ

    Typescriptスキーマとして定義される  
    (TypescriptからJSONスキーマが自動生成されるが)

- ライフサイクル
    1. Initialization: Capability negotiation and protocol version agreement
    1. Operation: Normal protocol communication
    1. Shutdown: Graceful termination of the connection

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/325677/ee914cef-140e-4a4d-a3b6-3458e08637c8.png)

- エラーハンドリング
  - Error response example

    ```json
    {
        "jsonrpc": "2.0",
        "id": 1,
        "error": {
            "code": -32602,
            "message": "Unsupported protocol version",
            "data": {
            "supported": ["2024-11-05"],
            "requested": "1.0.0"
            }
        }
    }
    ```

### 実装

#### クライアント実装

| クライアント名        | 実装言語     | 対応接続方式   | ホスト形態           | 備考                                                                |
|-----------------------|--------------|----------------|----------------------|---------------------------------------------------------------------|
| LangChain             | Python       | STDIO, SSE     | ローカル             | langchain-mcp-tools を使用                                          |
| LangChain.js          | TypeScript   | STDIO, SSE     | ローカル             | langchain-mcp-adapters により複数接続可                             |
| Claude Desktop        | Electron     | STDIO          | ローカル             | GUIアプリ、ローカルMCPとの接続設定あり                              |
| Sourcegraph Cody      | Go           | STDIO, SSE     | ローカル             | エディタ統合型                                                      |
| Zed Editor            | Rust         | STDIO          | ローカル             | Rust製エディタ、内部でMCPを使用                                     |
| Cursor                | TypeScript   | STDIO          | ローカル             | VSCode風エディタ、プラグイン経由で接続                              |
| Continue              | TypeScript   | STDIO, SSE     | ローカル             | 複数MCPサーバー対応                                                 |
| LibreChat             | JavaScript   | STDIO, SSE     | ローカル／自己ホスト | MCPツール統合可能。Dockerで自己ホストも簡易                         |
| OpenAI Agents SDK     | Python       | STDIO, SSE     | ローカル             | MCP対応のOpenAIクライアント                                         |
| PydanticAI            | Python       | STDIO, SSE     | ローカル             | Python統合用                                                        |
| Google ADK            | Python       | STDIO, SSE     | ローカル／自己ホスト | `MCPToolset`で接続。Gemma等と組み合わせてエージェント開発可能       |

※ ChatGPT 4oで生成。詳細未確認。  
※ 自己ホストとは、他者にも使える前提でLAN/インターネットで公開可能なものとのこと（ポートを開けるとか、localhost限定しないとか）。

#### サーバ実装

| 名称                  | 言語       | 接続方式       | ホスト形態             | 提供元・備考                                       |
|-----------------------|------------|----------------|------------------------|--------------------------------------------------|
| MCP Python SDK        | Python     | STDIO, SSE     | ローカル／自己ホスト   | Anthropic公式。FastAPI等と組み合わせて使用可能。 |
| MCP TypeScript SDK    | TypeScript | STDIO, SSE     | ローカル／自己ホスト   | Node.js環境向け。                                |
| MCP Java SDK          | Java       | STDIO, SSE     | ローカル／自己ホスト   | Spring Bootなどの環境で利用可能。               |
| MCP Kotlin SDK        | Kotlin     | STDIO, SSE     | ローカル／自己ホスト   | JetBrains協力。クライアント・サーバ両対応。     |
| MCP C# SDK            | C#         | STDIO, SSE     | ローカル／自己ホスト   | Microsoft協力。NuGet経由で提供。                |
| MCP Swift SDK         | Swift      | STDIO, SSE     | ローカル／自己ホスト   | Appleプラットフォーム向け。                     |
| MCP Rust SDK          | Rust       | STDIO, SSE     | ローカル／自己ホスト   | コミュニティ主導。                              |
| EasyMCP               | TypeScript | STDIO          | ローカル／自己ホスト   | Express風の構成でMCPツールを簡易実装可能。     |
| FastAPI-MCP           | Python     | SSE            | ローカル／自己ホスト   | FastAPIを活用しAPI公開型のMCPを実装。           |
| LangServe             | Python     | SSE            | ローカル／自己ホスト   | LangChain製。LangChainツールをMCP化可能。      |
| Azure MCP Server      | 任意       | SSE            | SaaS                   | Microsoft提供。AzureベースのMCP公開構成。       |
| Composio MCP          | 不明       | SSE            | SaaS                   | SaaS型MCP集約サービス。OAuth統合が豊富。        |

※ ChatGPT 4oで生成。詳細未確認。  
※ MCP Python SDKには、FastMCPが取り込まれている。別途、FastMCP2.0も公開されている。

## お試し

### 方針

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/325677/9a7b89a6-3a5b-4bae-8b6c-bd19704a7602.png)

今回は、Ubuntu上に、PostgreSQLのMCPサーバをお試しで構築する。  
MCPサーバ、クライアント間はHTTP接続とする。ただし、MCP Python SDKでのStreamable HTTPの実装が手間なので、 旧SSEで実装する ※。  

※ [実装例](https://github.com/modelcontextprotocol/python-sdk/tree/main/examples/servers/simple-streamablehttp-stateless)が公開されているが、[まだMCP Python SDKでは対応していない](https://github.com/modelcontextprotocol/python-sdk/issues/443)よう。

使用するライブラリは以下の通り。  

- クライアント

    Google Agent Development Kit (ADK)  
    <https://google.github.io/adk-docs/>

- サーバ

    MCP Python SDK  
    <https://github.com/modelcontextprotocol/python-sdk>

- LLM

    LLMは~~OpenRouter~~OpenAIのAPIを使用 ※。  
    ローカルLLM利用だと以下あたり？VRAM8GBじゃ足りないかも。蒸留モデルで量子化されたものが望ましい。llama.cppを想定。

  - Llama-3-ELYZA-JP-8B-q4_k_m.gguf(<https://huggingface.co/elyza/Llama-3-ELYZA-JP-8B-GGUF/tree/main>)

  ※ OpenRouterからだと、以下のADKのバグに引っかかる。  
      <https://github.com/google/adk-python/issues/171>

- PostgreSQL

    汎用性を考慮してODBC経由で接続したいので、Ubuntu上で構築楽そうなPostgreSQLとする。  

### 構築

#### 依存関係

```text
Ubuntu          22.04
Python          3.12.10
  uv            0.7.2
    google-adk  0.4.0
    litellm     1.68.0
    mcp[cli]    1.7.1
    pyodbc      5.2.0
postgresql      14.17-0ubuntu0.22.04.1
odbc-postgresql 1:13.02.0000-2

```

#### ディレクトリ構成

[構築手順](#構築手順)後の状態

```text
mcp_test
├── README.md
├── agent_samples
│   ├── .env
│   └── agent
│       ├── __init__.py
│       └── agent.py
├── server_samples
│   └── server.py
├── pyproject.toml
└── uv.lock
```

#### 構築手順

1. データ準備

    [e-Stat](https://www.e-stat.go.jp/)からデータを取得。  
    今回は、[男女別人口（各年10月1日現在）- 総人口、日本人人口（2000年～2020年）](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&cycle=0&toukei=00200524&tstat=000000090001&tclass1=000000090004&tclass2=000001051180&tclass3val=0&stat_infid=000013168601)を使用。  
    マトリクス表なので、Excelで開いてPowerQueryエディタからアンピボットしておく。

    <details><summary>データ</summary>

    ```csv:population_2000-2020.csv
    ID,prefecture,year,population
    1,Hokkaido,2000,5683
    2,Hokkaido,2001,5680
    3,Hokkaido,2002,5672
    4,Hokkaido,2003,5663
    5,Hokkaido,2004,5650
    6,Hokkaido,2005,5628
    7,Hokkaido,2006,5605
    8,Hokkaido,2007,5579
    9,Hokkaido,2008,5548
    10,Hokkaido,2009,5524
    11,Hokkaido,2010,5506
    12,Hokkaido,2011,5488
    13,Hokkaido,2012,5465
    14,Hokkaido,2013,5438
    15,Hokkaido,2014,5410
    16,Hokkaido,2015,5382
    17,Hokkaido,2016,5355
    18,Hokkaido,2017,5325
    19,Hokkaido,2018,5293
    20,Hokkaido,2019,5259
    21,Hokkaido,2020,5225
    22,Aomori-ken,2000,1476
    23,Aomori-ken,2001,1473
    24,Aomori-ken,2002,1467
    25,Aomori-ken,2003,1459
    26,Aomori-ken,2004,1448
    27,Aomori-ken,2005,1437
    28,Aomori-ken,2006,1424
    29,Aomori-ken,2007,1409
    30,Aomori-ken,2008,1395
    31,Aomori-ken,2009,1383
    32,Aomori-ken,2010,1373
    33,Aomori-ken,2011,1363
    34,Aomori-ken,2012,1350
    35,Aomori-ken,2013,1337
    36,Aomori-ken,2014,1323
    37,Aomori-ken,2015,1308
    38,Aomori-ken,2016,1295
    39,Aomori-ken,2017,1282
    40,Aomori-ken,2018,1268
    41,Aomori-ken,2019,1253
    42,Aomori-ken,2020,1238
    43,Iwate-ken,2000,1416
    44,Iwate-ken,2001,1413
    45,Iwate-ken,2002,1407
    46,Iwate-ken,2003,1401
    47,Iwate-ken,2004,1395
    48,Iwate-ken,2005,1385
    49,Iwate-ken,2006,1375
    50,Iwate-ken,2007,1364
    51,Iwate-ken,2008,1352
    52,Iwate-ken,2009,1340
    53,Iwate-ken,2010,1330
    54,Iwate-ken,2011,1315
    55,Iwate-ken,2012,1306
    56,Iwate-ken,2013,1299
    57,Iwate-ken,2014,1290
    58,Iwate-ken,2015,1280
    59,Iwate-ken,2016,1268
    60,Iwate-ken,2017,1254
    61,Iwate-ken,2018,1240
    62,Iwate-ken,2019,1226
    63,Iwate-ken,2020,1211
    64,Miyagi-ken,2000,2365
    65,Miyagi-ken,2001,2369
    66,Miyagi-ken,2002,2369
    67,Miyagi-ken,2003,2369
    68,Miyagi-ken,2004,2366
    69,Miyagi-ken,2005,2360
    70,Miyagi-ken,2006,2358
    71,Miyagi-ken,2007,2354
    72,Miyagi-ken,2008,2349
    73,Miyagi-ken,2009,2348
    74,Miyagi-ken,2010,2348
    75,Miyagi-ken,2011,2326
    76,Miyagi-ken,2012,2329
    77,Miyagi-ken,2013,2333
    78,Miyagi-ken,2014,2335
    79,Miyagi-ken,2015,2334
    80,Miyagi-ken,2016,2332
    81,Miyagi-ken,2017,2326
    82,Miyagi-ken,2018,2320
    83,Miyagi-ken,2019,2312
    84,Miyagi-ken,2020,2302
    85,Akita-ken,2000,1189
    86,Akita-ken,2001,1183
    87,Akita-ken,2002,1175
    88,Akita-ken,2003,1165
    89,Akita-ken,2004,1156
    90,Akita-ken,2005,1146
    91,Akita-ken,2006,1134
    92,Akita-ken,2007,1121
    93,Akita-ken,2008,1109
    94,Akita-ken,2009,1097
    95,Akita-ken,2010,1086
    96,Akita-ken,2011,1075
    97,Akita-ken,2012,1063
    98,Akita-ken,2013,1050
    99,Akita-ken,2014,1037
    100,Akita-ken,2015,1023
    101,Akita-ken,2016,1011
    102,Akita-ken,2017,999
    103,Akita-ken,2018,985
    104,Akita-ken,2019,972
    105,Akita-ken,2020,960
    106,Yamagata-ken,2000,1244
    107,Yamagata-ken,2001,1241
    108,Yamagata-ken,2002,1236
    109,Yamagata-ken,2003,1230
    110,Yamagata-ken,2004,1224
    111,Yamagata-ken,2005,1216
    112,Yamagata-ken,2006,1207
    113,Yamagata-ken,2007,1198
    114,Yamagata-ken,2008,1188
    115,Yamagata-ken,2009,1178
    116,Yamagata-ken,2010,1169
    117,Yamagata-ken,2011,1162
    118,Yamagata-ken,2012,1153
    119,Yamagata-ken,2013,1144
    120,Yamagata-ken,2014,1134
    121,Yamagata-ken,2015,1124
    122,Yamagata-ken,2016,1114
    123,Yamagata-ken,2017,1103
    124,Yamagata-ken,2018,1092
    125,Yamagata-ken,2019,1080
    126,Yamagata-ken,2020,1068
    127,Fukushima-ken,2000,2127
    128,Fukushima-ken,2001,2124
    129,Fukushima-ken,2002,2118
    130,Fukushima-ken,2003,2110
    131,Fukushima-ken,2004,2102
    132,Fukushima-ken,2005,2091
    133,Fukushima-ken,2006,2080
    134,Fukushima-ken,2007,2067
    135,Fukushima-ken,2008,2054
    136,Fukushima-ken,2009,2041
    137,Fukushima-ken,2010,2029
    138,Fukushima-ken,2011,1988
    139,Fukushima-ken,2012,1957
    140,Fukushima-ken,2013,1940
    141,Fukushima-ken,2014,1927
    142,Fukushima-ken,2015,1914
    143,Fukushima-ken,2016,1903
    144,Fukushima-ken,2017,1886
    145,Fukushima-ken,2018,1869
    146,Fukushima-ken,2019,1852
    147,Fukushima-ken,2020,1833
    148,Ibaraki-ken,2000,2986
    149,Ibaraki-ken,2001,2990
    150,Ibaraki-ken,2002,2987
    151,Ibaraki-ken,2003,2985
    152,Ibaraki-ken,2004,2982
    153,Ibaraki-ken,2005,2975
    154,Ibaraki-ken,2006,2974
    155,Ibaraki-ken,2007,2973
    156,Ibaraki-ken,2008,2971
    157,Ibaraki-ken,2009,2970
    158,Ibaraki-ken,2010,2970
    159,Ibaraki-ken,2011,2960
    160,Ibaraki-ken,2012,2947
    161,Ibaraki-ken,2013,2937
    162,Ibaraki-ken,2014,2927
    163,Ibaraki-ken,2015,2917
    164,Ibaraki-ken,2016,2910
    165,Ibaraki-ken,2017,2902
    166,Ibaraki-ken,2018,2892
    167,Ibaraki-ken,2019,2879
    168,Ibaraki-ken,2020,2867
    169,Tochigi-ken,2000,2005
    170,Tochigi-ken,2001,2011
    171,Tochigi-ken,2002,2012
    172,Tochigi-ken,2003,2014
    173,Tochigi-ken,2004,2016
    174,Tochigi-ken,2005,2017
    175,Tochigi-ken,2006,2016
    176,Tochigi-ken,2007,2016
    177,Tochigi-ken,2008,2015
    178,Tochigi-ken,2009,2011
    179,Tochigi-ken,2010,2008
    180,Tochigi-ken,2011,2000
    181,Tochigi-ken,2012,1992
    182,Tochigi-ken,2013,1986
    183,Tochigi-ken,2014,1980
    184,Tochigi-ken,2015,1974
    185,Tochigi-ken,2016,1969
    186,Tochigi-ken,2017,1962
    187,Tochigi-ken,2018,1953
    188,Tochigi-ken,2019,1943
    189,Tochigi-ken,2020,1933
    190,Gumma-ken,2000,2025
    191,Gumma-ken,2001,2029
    192,Gumma-ken,2002,2030
    193,Gumma-ken,2003,2030
    194,Gumma-ken,2004,2027
    195,Gumma-ken,2005,2024
    196,Gumma-ken,2006,2022
    197,Gumma-ken,2007,2020
    198,Gumma-ken,2008,2017
    199,Gumma-ken,2009,2014
    200,Gumma-ken,2010,2008
    201,Gumma-ken,2011,2001
    202,Gumma-ken,2012,1994
    203,Gumma-ken,2013,1986
    204,Gumma-ken,2014,1979
    205,Gumma-ken,2015,1973
    206,Gumma-ken,2016,1969
    207,Gumma-ken,2017,1963
    208,Gumma-ken,2018,1957
    209,Gumma-ken,2019,1949
    210,Gumma-ken,2020,1939
    211,Saitama-ken,2000,6938
    212,Saitama-ken,2001,6977
    213,Saitama-ken,2002,7000
    214,Saitama-ken,2003,7028
    215,Saitama-ken,2004,7046
    216,Saitama-ken,2005,7054
    217,Saitama-ken,2006,7079
    218,Saitama-ken,2007,7106
    219,Saitama-ken,2008,7136
    220,Saitama-ken,2009,7161
    221,Saitama-ken,2010,7195
    222,Saitama-ken,2011,7209
    223,Saitama-ken,2012,7216
    224,Saitama-ken,2013,7228
    225,Saitama-ken,2014,7247
    226,Saitama-ken,2015,7267
    227,Saitama-ken,2016,7288
    228,Saitama-ken,2017,7307
    229,Saitama-ken,2018,7325
    230,Saitama-ken,2019,7342
    231,Saitama-ken,2020,7345
    232,Chiba-ken,2000,5926
    233,Chiba-ken,2001,5970
    234,Chiba-ken,2002,5998
    235,Chiba-ken,2003,6030
    236,Chiba-ken,2004,6047
    237,Chiba-ken,2005,6056
    238,Chiba-ken,2006,6084
    239,Chiba-ken,2007,6119
    240,Chiba-ken,2008,6153
    241,Chiba-ken,2009,6180
    242,Chiba-ken,2010,6216
    243,Chiba-ken,2011,6217
    244,Chiba-ken,2012,6200
    245,Chiba-ken,2013,6201
    246,Chiba-ken,2014,6209
    247,Chiba-ken,2015,6223
    248,Chiba-ken,2016,6242
    249,Chiba-ken,2017,6258
    250,Chiba-ken,2018,6273
    251,Chiba-ken,2019,6283
    252,Chiba-ken,2020,6284
    253,Tokyo-to,2000,12064
    254,Tokyo-to,2001,12165
    255,Tokyo-to,2002,12271
    256,Tokyo-to,2003,12388
    257,Tokyo-to,2004,12482
    258,Tokyo-to,2005,12577
    259,Tokyo-to,2006,12704
    260,Tokyo-to,2007,12848
    261,Tokyo-to,2008,12973
    262,Tokyo-to,2009,13048
    263,Tokyo-to,2010,13159
    264,Tokyo-to,2011,13198
    265,Tokyo-to,2012,13234
    266,Tokyo-to,2013,13307
    267,Tokyo-to,2014,13399
    268,Tokyo-to,2015,13515
    269,Tokyo-to,2016,13646
    270,Tokyo-to,2017,13768
    271,Tokyo-to,2018,13887
    272,Tokyo-to,2019,14007
    273,Tokyo-to,2020,14048
    274,Kanagawa-ken,2000,8490
    275,Kanagawa-ken,2001,8575
    276,Kanagawa-ken,2002,8636
    277,Kanagawa-ken,2003,8702
    278,Kanagawa-ken,2004,8753
    279,Kanagawa-ken,2005,8792
    280,Kanagawa-ken,2006,8846
    281,Kanagawa-ken,2007,8912
    282,Kanagawa-ken,2008,8965
    283,Kanagawa-ken,2009,9006
    284,Kanagawa-ken,2010,9048
    285,Kanagawa-ken,2011,9060
    286,Kanagawa-ken,2012,9070
    287,Kanagawa-ken,2013,9084
    288,Kanagawa-ken,2014,9103
    289,Kanagawa-ken,2015,9126
    290,Kanagawa-ken,2016,9152
    291,Kanagawa-ken,2017,9173
    292,Kanagawa-ken,2018,9197
    293,Kanagawa-ken,2019,9224
    294,Kanagawa-ken,2020,9237
    295,Niigata-ken,2000,2476
    296,Niigata-ken,2001,2471
    297,Niigata-ken,2002,2462
    298,Niigata-ken,2003,2455
    299,Niigata-ken,2004,2445
    300,Niigata-ken,2005,2431
    301,Niigata-ken,2006,2420
    302,Niigata-ken,2007,2408
    303,Niigata-ken,2008,2396
    304,Niigata-ken,2009,2385
    305,Niigata-ken,2010,2374
    306,Niigata-ken,2011,2364
    307,Niigata-ken,2012,2350
    308,Niigata-ken,2013,2336
    309,Niigata-ken,2014,2320
    310,Niigata-ken,2015,2304
    311,Niigata-ken,2016,2286
    312,Niigata-ken,2017,2267
    313,Niigata-ken,2018,2246
    314,Niigata-ken,2019,2224
    315,Niigata-ken,2020,2201
    316,Toyama-ken,2000,1121
    317,Toyama-ken,2001,1120
    318,Toyama-ken,2002,1118
    319,Toyama-ken,2003,1116
    320,Toyama-ken,2004,1115
    321,Toyama-ken,2005,1112
    322,Toyama-ken,2006,1110
    323,Toyama-ken,2007,1107
    324,Toyama-ken,2008,1103
    325,Toyama-ken,2009,1098
    326,Toyama-ken,2010,1093
    327,Toyama-ken,2011,1088
    328,Toyama-ken,2012,1083
    329,Toyama-ken,2013,1077
    330,Toyama-ken,2014,1072
    331,Toyama-ken,2015,1066
    332,Toyama-ken,2016,1061
    333,Toyama-ken,2017,1056
    334,Toyama-ken,2018,1050
    335,Toyama-ken,2019,1043
    336,Toyama-ken,2020,1035
    337,Ishikawa-ken,2000,1181
    338,Ishikawa-ken,2001,1182
    339,Ishikawa-ken,2002,1180
    340,Ishikawa-ken,2003,1179
    341,Ishikawa-ken,2004,1178
    342,Ishikawa-ken,2005,1174
    343,Ishikawa-ken,2006,1173
    344,Ishikawa-ken,2007,1173
    345,Ishikawa-ken,2008,1172
    346,Ishikawa-ken,2009,1171
    347,Ishikawa-ken,2010,1170
    348,Ishikawa-ken,2011,1167
    349,Ishikawa-ken,2012,1164
    350,Ishikawa-ken,2013,1160
    351,Ishikawa-ken,2014,1157
    352,Ishikawa-ken,2015,1154
    353,Ishikawa-ken,2016,1151
    354,Ishikawa-ken,2017,1148
    355,Ishikawa-ken,2018,1145
    356,Ishikawa-ken,2019,1139
    357,Ishikawa-ken,2020,1133
    358,Fukui-ken,2000,829
    359,Fukui-ken,2001,830
    360,Fukui-ken,2002,828
    361,Fukui-ken,2003,827
    362,Fukui-ken,2004,824
    363,Fukui-ken,2005,822
    364,Fukui-ken,2006,820
    365,Fukui-ken,2007,817
    366,Fukui-ken,2008,814
    367,Fukui-ken,2009,810
    368,Fukui-ken,2010,806
    369,Fukui-ken,2011,803
    370,Fukui-ken,2012,800
    371,Fukui-ken,2013,796
    372,Fukui-ken,2014,791
    373,Fukui-ken,2015,787
    374,Fukui-ken,2016,783
    375,Fukui-ken,2017,780
    376,Fukui-ken,2018,777
    377,Fukui-ken,2019,771
    378,Fukui-ken,2020,767
    379,Yamanashi-ken,2000,888
    380,Yamanashi-ken,2001,890
    381,Yamanashi-ken,2002,889
    382,Yamanashi-ken,2003,888
    383,Yamanashi-ken,2004,886
    384,Yamanashi-ken,2005,885
    385,Yamanashi-ken,2006,880
    386,Yamanashi-ken,2007,877
    387,Yamanashi-ken,2008,871
    388,Yamanashi-ken,2009,867
    389,Yamanashi-ken,2010,863
    390,Yamanashi-ken,2011,857
    391,Yamanashi-ken,2012,852
    392,Yamanashi-ken,2013,847
    393,Yamanashi-ken,2014,841
    394,Yamanashi-ken,2015,835
    395,Yamanashi-ken,2016,831
    396,Yamanashi-ken,2017,826
    397,Yamanashi-ken,2018,821
    398,Yamanashi-ken,2019,815
    399,Yamanashi-ken,2020,810
    400,Nagano-ken,2000,2215
    401,Nagano-ken,2001,2222
    402,Nagano-ken,2002,2215
    403,Nagano-ken,2003,2210
    404,Nagano-ken,2004,2206
    405,Nagano-ken,2005,2196
    406,Nagano-ken,2006,2189
    407,Nagano-ken,2007,2182
    408,Nagano-ken,2008,2173
    409,Nagano-ken,2009,2162
    410,Nagano-ken,2010,2152
    411,Nagano-ken,2011,2142
    412,Nagano-ken,2012,2132
    413,Nagano-ken,2013,2122
    414,Nagano-ken,2014,2110
    415,Nagano-ken,2015,2099
    416,Nagano-ken,2016,2091
    417,Nagano-ken,2017,2082
    418,Nagano-ken,2018,2073
    419,Nagano-ken,2019,2061
    420,Nagano-ken,2020,2048
    421,Gifu-ken,2000,2108
    422,Gifu-ken,2001,2111
    423,Gifu-ken,2002,2110
    424,Gifu-ken,2003,2110
    425,Gifu-ken,2004,2108
    426,Gifu-ken,2005,2107
    427,Gifu-ken,2006,2105
    428,Gifu-ken,2007,2104
    429,Gifu-ken,2008,2100
    430,Gifu-ken,2009,2091
    431,Gifu-ken,2010,2081
    432,Gifu-ken,2011,2071
    433,Gifu-ken,2012,2062
    434,Gifu-ken,2013,2053
    435,Gifu-ken,2014,2043
    436,Gifu-ken,2015,2032
    437,Gifu-ken,2016,2024
    438,Gifu-ken,2017,2012
    439,Gifu-ken,2018,2001
    440,Gifu-ken,2019,1992
    441,Gifu-ken,2020,1979
    442,Shizuoka-ken,2000,3767
    443,Shizuoka-ken,2001,3780
    444,Shizuoka-ken,2002,3784
    445,Shizuoka-ken,2003,3790
    446,Shizuoka-ken,2004,3791
    447,Shizuoka-ken,2005,3792
    448,Shizuoka-ken,2006,3795
    449,Shizuoka-ken,2007,3796
    450,Shizuoka-ken,2008,3793
    451,Shizuoka-ken,2009,3783
    452,Shizuoka-ken,2010,3765
    453,Shizuoka-ken,2011,3752
    454,Shizuoka-ken,2012,3739
    455,Shizuoka-ken,2013,3730
    456,Shizuoka-ken,2014,3715
    457,Shizuoka-ken,2015,3700
    458,Shizuoka-ken,2016,3690
    459,Shizuoka-ken,2017,3681
    460,Shizuoka-ken,2018,3667
    461,Shizuoka-ken,2019,3653
    462,Shizuoka-ken,2020,3633
    463,Aichi-ken,2000,7043
    464,Aichi-ken,2001,7091
    465,Aichi-ken,2002,7131
    466,Aichi-ken,2003,7170
    467,Aichi-ken,2004,7209
    468,Aichi-ken,2005,7255
    469,Aichi-ken,2006,7307
    470,Aichi-ken,2007,7357
    471,Aichi-ken,2008,7399
    472,Aichi-ken,2009,7411
    473,Aichi-ken,2010,7411
    474,Aichi-ken,2011,7418
    475,Aichi-ken,2012,7431
    476,Aichi-ken,2013,7449
    477,Aichi-ken,2014,7464
    478,Aichi-ken,2015,7483
    479,Aichi-ken,2016,7509
    480,Aichi-ken,2017,7528
    481,Aichi-ken,2018,7541
    482,Aichi-ken,2019,7557
    483,Aichi-ken,2020,7542
    484,Mie-ken,2000,1857
    485,Mie-ken,2001,1862
    486,Mie-ken,2002,1863
    487,Mie-ken,2003,1864
    488,Mie-ken,2004,1867
    489,Mie-ken,2005,1867
    490,Mie-ken,2006,1872
    491,Mie-ken,2007,1873
    492,Mie-ken,2008,1871
    493,Mie-ken,2009,1864
    494,Mie-ken,2010,1855
    495,Mie-ken,2011,1847
    496,Mie-ken,2012,1841
    497,Mie-ken,2013,1833
    498,Mie-ken,2014,1826
    499,Mie-ken,2015,1816
    500,Mie-ken,2016,1809
    501,Mie-ken,2017,1801
    502,Mie-ken,2018,1793
    503,Mie-ken,2019,1783
    504,Mie-ken,2020,1770
    505,Shiga-ken,2000,1343
    506,Shiga-ken,2001,1354
    507,Shiga-ken,2002,1361
    508,Shiga-ken,2003,1368
    509,Shiga-ken,2004,1375
    510,Shiga-ken,2005,1380
    511,Shiga-ken,2006,1390
    512,Shiga-ken,2007,1398
    513,Shiga-ken,2008,1405
    514,Shiga-ken,2009,1409
    515,Shiga-ken,2010,1411
    516,Shiga-ken,2011,1413
    517,Shiga-ken,2012,1414
    518,Shiga-ken,2013,1415
    519,Shiga-ken,2014,1414
    520,Shiga-ken,2015,1413
    521,Shiga-ken,2016,1414
    522,Shiga-ken,2017,1414
    523,Shiga-ken,2018,1414
    524,Shiga-ken,2019,1416
    525,Shiga-ken,2020,1414
    526,Kyoto-fu,2000,2644
    527,Kyoto-fu,2001,2649
    528,Kyoto-fu,2002,2648
    529,Kyoto-fu,2003,2650
    530,Kyoto-fu,2004,2650
    531,Kyoto-fu,2005,2648
    532,Kyoto-fu,2006,2646
    533,Kyoto-fu,2007,2643
    534,Kyoto-fu,2008,2640
    535,Kyoto-fu,2009,2637
    536,Kyoto-fu,2010,2636
    537,Kyoto-fu,2011,2633
    538,Kyoto-fu,2012,2628
    539,Kyoto-fu,2013,2622
    540,Kyoto-fu,2014,2616
    541,Kyoto-fu,2015,2610
    542,Kyoto-fu,2016,2608
    543,Kyoto-fu,2017,2604
    544,Kyoto-fu,2018,2598
    545,Kyoto-fu,2019,2592
    546,Kyoto-fu,2020,2578
    547,Osaka-fu,2000,8805
    548,Osaka-fu,2001,8821
    549,Osaka-fu,2002,8821
    550,Osaka-fu,2003,8824
    551,Osaka-fu,2004,8825
    552,Osaka-fu,2005,8817
    553,Osaka-fu,2006,8828
    554,Osaka-fu,2007,8839
    555,Osaka-fu,2008,8847
    556,Osaka-fu,2009,8855
    557,Osaka-fu,2010,8865
    558,Osaka-fu,2011,8863
    559,Osaka-fu,2012,8861
    560,Osaka-fu,2013,8856
    561,Osaka-fu,2014,8845
    562,Osaka-fu,2015,8839
    563,Osaka-fu,2016,8841
    564,Osaka-fu,2017,8841
    565,Osaka-fu,2018,8838
    566,Osaka-fu,2019,8842
    567,Osaka-fu,2020,8838
    568,Hyogo-ken,2000,5551
    569,Hyogo-ken,2001,5572
    570,Hyogo-ken,2002,5580
    571,Hyogo-ken,2003,5589
    572,Hyogo-ken,2004,5592
    573,Hyogo-ken,2005,5591
    574,Hyogo-ken,2006,5592
    575,Hyogo-ken,2007,5593
    576,Hyogo-ken,2008,5592
    577,Hyogo-ken,2009,5591
    578,Hyogo-ken,2010,5588
    579,Hyogo-ken,2011,5584
    580,Hyogo-ken,2012,5575
    581,Hyogo-ken,2013,5565
    582,Hyogo-ken,2014,5550
    583,Hyogo-ken,2015,5535
    584,Hyogo-ken,2016,5526
    585,Hyogo-ken,2017,5515
    586,Hyogo-ken,2018,5501
    587,Hyogo-ken,2019,5488
    588,Hyogo-ken,2020,5465
    589,Nara-ken,2000,1443
    590,Nara-ken,2001,1442
    591,Nara-ken,2002,1437
    592,Nara-ken,2003,1434
    593,Nara-ken,2004,1428
    594,Nara-ken,2005,1421
    595,Nara-ken,2006,1417
    596,Nara-ken,2007,1413
    597,Nara-ken,2008,1407
    598,Nara-ken,2009,1404
    599,Nara-ken,2010,1401
    600,Nara-ken,2011,1395
    601,Nara-ken,2012,1388
    602,Nara-ken,2013,1381
    603,Nara-ken,2014,1373
    604,Nara-ken,2015,1364
    605,Nara-ken,2016,1357
    606,Nara-ken,2017,1349
    607,Nara-ken,2018,1341
    608,Nara-ken,2019,1333
    609,Nara-ken,2020,1324
    610,Wakayama-ken,2000,1070
    611,Wakayama-ken,2001,1065
    612,Wakayama-ken,2002,1059
    613,Wakayama-ken,2003,1052
    614,Wakayama-ken,2004,1045
    615,Wakayama-ken,2005,1036
    616,Wakayama-ken,2006,1029
    617,Wakayama-ken,2007,1021
    618,Wakayama-ken,2008,1014
    619,Wakayama-ken,2009,1008
    620,Wakayama-ken,2010,1002
    621,Wakayama-ken,2011,995
    622,Wakayama-ken,2012,988
    623,Wakayama-ken,2013,980
    624,Wakayama-ken,2014,972
    625,Wakayama-ken,2015,964
    626,Wakayama-ken,2016,956
    627,Wakayama-ken,2017,948
    628,Wakayama-ken,2018,940
    629,Wakayama-ken,2019,931
    630,Wakayama-ken,2020,923
    631,Tottori-ken,2000,613
    632,Tottori-ken,2001,613
    633,Tottori-ken,2002,612
    634,Tottori-ken,2003,611
    635,Tottori-ken,2004,610
    636,Tottori-ken,2005,607
    637,Tottori-ken,2006,604
    638,Tottori-ken,2007,601
    639,Tottori-ken,2008,596
    640,Tottori-ken,2009,592
    641,Tottori-ken,2010,589
    642,Tottori-ken,2011,586
    643,Tottori-ken,2012,583
    644,Tottori-ken,2013,580
    645,Tottori-ken,2014,577
    646,Tottori-ken,2015,573
    647,Tottori-ken,2016,570
    648,Tottori-ken,2017,566
    649,Tottori-ken,2018,562
    650,Tottori-ken,2019,557
    651,Tottori-ken,2020,553
    652,Shimane-ken,2000,762
    653,Shimane-ken,2001,760
    654,Shimane-ken,2002,756
    655,Shimane-ken,2003,752
    656,Shimane-ken,2004,747
    657,Shimane-ken,2005,742
    658,Shimane-ken,2006,737
    659,Shimane-ken,2007,733
    660,Shimane-ken,2008,727
    661,Shimane-ken,2009,721
    662,Shimane-ken,2010,717
    663,Shimane-ken,2011,713
    664,Shimane-ken,2012,708
    665,Shimane-ken,2013,704
    666,Shimane-ken,2014,699
    667,Shimane-ken,2015,694
    668,Shimane-ken,2016,691
    669,Shimane-ken,2017,687
    670,Shimane-ken,2018,682
    671,Shimane-ken,2019,677
    672,Shimane-ken,2020,671
    673,Okayama-ken,2000,1951
    674,Okayama-ken,2001,1954
    675,Okayama-ken,2002,1956
    676,Okayama-ken,2003,1958
    677,Okayama-ken,2004,1959
    678,Okayama-ken,2005,1957
    679,Okayama-ken,2006,1956
    680,Okayama-ken,2007,1956
    681,Okayama-ken,2008,1953
    682,Okayama-ken,2009,1949
    683,Okayama-ken,2010,1945
    684,Okayama-ken,2011,1941
    685,Okayama-ken,2012,1937
    686,Okayama-ken,2013,1932
    687,Okayama-ken,2014,1926
    688,Okayama-ken,2015,1922
    689,Okayama-ken,2016,1917
    690,Okayama-ken,2017,1911
    691,Okayama-ken,2018,1904
    692,Okayama-ken,2019,1897
    693,Okayama-ken,2020,1888
    694,Hiroshima-ken,2000,2879
    695,Hiroshima-ken,2001,2880
    696,Hiroshima-ken,2002,2878
    697,Hiroshima-ken,2003,2879
    698,Hiroshima-ken,2004,2879
    699,Hiroshima-ken,2005,2877
    700,Hiroshima-ken,2006,2875
    701,Hiroshima-ken,2007,2874
    702,Hiroshima-ken,2008,2870
    703,Hiroshima-ken,2009,2864
    704,Hiroshima-ken,2010,2861
    705,Hiroshima-ken,2011,2858
    706,Hiroshima-ken,2012,2855
    707,Hiroshima-ken,2013,2850
    708,Hiroshima-ken,2014,2846
    709,Hiroshima-ken,2015,2844
    710,Hiroshima-ken,2016,2840
    711,Hiroshima-ken,2017,2833
    712,Hiroshima-ken,2018,2824
    713,Hiroshima-ken,2019,2813
    714,Hiroshima-ken,2020,2800
    715,Yamaguchi-ken,2000,1528
    716,Yamaguchi-ken,2001,1523
    717,Yamaguchi-ken,2002,1517
    718,Yamaguchi-ken,2003,1510
    719,Yamaguchi-ken,2004,1502
    720,Yamaguchi-ken,2005,1493
    721,Yamaguchi-ken,2006,1484
    722,Yamaguchi-ken,2007,1476
    723,Yamaguchi-ken,2008,1466
    724,Yamaguchi-ken,2009,1459
    725,Yamaguchi-ken,2010,1451
    726,Yamaguchi-ken,2011,1444
    727,Yamaguchi-ken,2012,1434
    728,Yamaguchi-ken,2013,1425
    729,Yamaguchi-ken,2014,1415
    730,Yamaguchi-ken,2015,1405
    731,Yamaguchi-ken,2016,1394
    732,Yamaguchi-ken,2017,1382
    733,Yamaguchi-ken,2018,1369
    734,Yamaguchi-ken,2019,1357
    735,Yamaguchi-ken,2020,1342
    736,Tokushima-ken,2000,824
    737,Tokushima-ken,2001,822
    738,Tokushima-ken,2002,820
    739,Tokushima-ken,2003,818
    740,Tokushima-ken,2004,814
    741,Tokushima-ken,2005,810
    742,Tokushima-ken,2006,806
    743,Tokushima-ken,2007,800
    744,Tokushima-ken,2008,795
    745,Tokushima-ken,2009,790
    746,Tokushima-ken,2010,785
    747,Tokushima-ken,2011,780
    748,Tokushima-ken,2012,775
    749,Tokushima-ken,2013,769
    750,Tokushima-ken,2014,763
    751,Tokushima-ken,2015,756
    752,Tokushima-ken,2016,750
    753,Tokushima-ken,2017,744
    754,Tokushima-ken,2018,736
    755,Tokushima-ken,2019,728
    756,Tokushima-ken,2020,720
    757,Kagawa-ken,2000,1023
    758,Kagawa-ken,2001,1022
    759,Kagawa-ken,2002,1020
    760,Kagawa-ken,2003,1018
    761,Kagawa-ken,2004,1016
    762,Kagawa-ken,2005,1012
    763,Kagawa-ken,2006,1009
    764,Kagawa-ken,2007,1006
    765,Kagawa-ken,2008,1003
    766,Kagawa-ken,2009,999
    767,Kagawa-ken,2010,996
    768,Kagawa-ken,2011,992
    769,Kagawa-ken,2012,989
    770,Kagawa-ken,2013,985
    771,Kagawa-ken,2014,980
    772,Kagawa-ken,2015,976
    773,Kagawa-ken,2016,973
    774,Kagawa-ken,2017,968
    775,Kagawa-ken,2018,963
    776,Kagawa-ken,2019,958
    777,Kagawa-ken,2020,950
    778,Ehime-ken,2000,1493
    779,Ehime-ken,2001,1490
    780,Ehime-ken,2002,1485
    781,Ehime-ken,2003,1481
    782,Ehime-ken,2004,1475
    783,Ehime-ken,2005,1468
    784,Ehime-ken,2006,1460
    785,Ehime-ken,2007,1453
    786,Ehime-ken,2008,1445
    787,Ehime-ken,2009,1438
    788,Ehime-ken,2010,1431
    789,Ehime-ken,2011,1424
    790,Ehime-ken,2012,1415
    791,Ehime-ken,2013,1406
    792,Ehime-ken,2014,1396
    793,Ehime-ken,2015,1385
    794,Ehime-ken,2016,1377
    795,Ehime-ken,2017,1368
    796,Ehime-ken,2018,1357
    797,Ehime-ken,2019,1346
    798,Ehime-ken,2020,1335
    799,Kochi-ken,2000,814
    800,Kochi-ken,2001,812
    801,Kochi-ken,2002,810
    802,Kochi-ken,2003,806
    803,Kochi-ken,2004,802
    804,Kochi-ken,2005,796
    805,Kochi-ken,2006,790
    806,Kochi-ken,2007,783
    807,Kochi-ken,2008,775
    808,Kochi-ken,2009,769
    809,Kochi-ken,2010,764
    810,Kochi-ken,2011,758
    811,Kochi-ken,2012,751
    812,Kochi-ken,2013,743
    813,Kochi-ken,2014,736
    814,Kochi-ken,2015,728
    815,Kochi-ken,2016,721
    816,Kochi-ken,2017,714
    817,Kochi-ken,2018,707
    818,Kochi-ken,2019,699
    819,Kochi-ken,2020,692
    820,Fukuoka-ken,2000,5016
    821,Fukuoka-ken,2001,5030
    822,Fukuoka-ken,2002,5039
    823,Fukuoka-ken,2003,5045
    824,Fukuoka-ken,2004,5050
    825,Fukuoka-ken,2005,5050
    826,Fukuoka-ken,2006,5057
    827,Fukuoka-ken,2007,5061
    828,Fukuoka-ken,2008,5062
    829,Fukuoka-ken,2009,5064
    830,Fukuoka-ken,2010,5072
    831,Fukuoka-ken,2011,5081
    832,Fukuoka-ken,2012,5089
    833,Fukuoka-ken,2013,5096
    834,Fukuoka-ken,2014,5099
    835,Fukuoka-ken,2015,5102
    836,Fukuoka-ken,2016,5113
    837,Fukuoka-ken,2017,5123
    838,Fukuoka-ken,2018,5131
    839,Fukuoka-ken,2019,5134
    840,Fukuoka-ken,2020,5135
    841,Saga-ken,2000,877
    842,Saga-ken,2001,876
    843,Saga-ken,2002,874
    844,Saga-ken,2003,872
    845,Saga-ken,2004,870
    846,Saga-ken,2005,866
    847,Saga-ken,2006,863
    848,Saga-ken,2007,860
    849,Saga-ken,2008,856
    850,Saga-ken,2009,853
    851,Saga-ken,2010,850
    852,Saga-ken,2011,847
    853,Saga-ken,2012,845
    854,Saga-ken,2013,841
    855,Saga-ken,2014,837
    856,Saga-ken,2015,833
    857,Saga-ken,2016,829
    858,Saga-ken,2017,825
    859,Saga-ken,2018,821
    860,Saga-ken,2019,817
    861,Saga-ken,2020,811
    862,Nagasaki-ken,2000,1517
    863,Nagasaki-ken,2001,1512
    864,Nagasaki-ken,2002,1505
    865,Nagasaki-ken,2003,1498
    866,Nagasaki-ken,2004,1490
    867,Nagasaki-ken,2005,1479
    868,Nagasaki-ken,2006,1467
    869,Nagasaki-ken,2007,1455
    870,Nagasaki-ken,2008,1443
    871,Nagasaki-ken,2009,1434
    872,Nagasaki-ken,2010,1427
    873,Nagasaki-ken,2011,1418
    874,Nagasaki-ken,2012,1408
    875,Nagasaki-ken,2013,1397
    876,Nagasaki-ken,2014,1387
    877,Nagasaki-ken,2015,1377
    878,Nagasaki-ken,2016,1367
    879,Nagasaki-ken,2017,1355
    880,Nagasaki-ken,2018,1341
    881,Nagasaki-ken,2019,1327
    882,Nagasaki-ken,2020,1312
    883,Kumamoto-ken,2000,1859
    884,Kumamoto-ken,2001,1860
    885,Kumamoto-ken,2002,1857
    886,Kumamoto-ken,2003,1852
    887,Kumamoto-ken,2004,1848
    888,Kumamoto-ken,2005,1842
    889,Kumamoto-ken,2006,1838
    890,Kumamoto-ken,2007,1832
    891,Kumamoto-ken,2008,1826
    892,Kumamoto-ken,2009,1821
    893,Kumamoto-ken,2010,1817
    894,Kumamoto-ken,2011,1813
    895,Kumamoto-ken,2012,1807
    896,Kumamoto-ken,2013,1801
    897,Kumamoto-ken,2014,1795
    898,Kumamoto-ken,2015,1786
    899,Kumamoto-ken,2016,1775
    900,Kumamoto-ken,2017,1767
    901,Kumamoto-ken,2018,1759
    902,Kumamoto-ken,2019,1749
    903,Kumamoto-ken,2020,1738
    904,Oita-ken,2000,1221
    905,Oita-ken,2001,1220
    906,Oita-ken,2002,1219
    907,Oita-ken,2003,1217
    908,Oita-ken,2004,1214
    909,Oita-ken,2005,1210
    910,Oita-ken,2006,1207
    911,Oita-ken,2007,1206
    912,Oita-ken,2008,1204
    913,Oita-ken,2009,1200
    914,Oita-ken,2010,1197
    915,Oita-ken,2011,1192
    916,Oita-ken,2012,1186
    917,Oita-ken,2013,1180
    918,Oita-ken,2014,1173
    919,Oita-ken,2015,1166
    920,Oita-ken,2016,1160
    921,Oita-ken,2017,1152
    922,Oita-ken,2018,1143
    923,Oita-ken,2019,1134
    924,Oita-ken,2020,1124
    925,Miyazaki-ken,2000,1170
    926,Miyazaki-ken,2001,1168
    927,Miyazaki-ken,2002,1165
    928,Miyazaki-ken,2003,1162
    929,Miyazaki-ken,2004,1159
    930,Miyazaki-ken,2005,1153
    931,Miyazaki-ken,2006,1150
    932,Miyazaki-ken,2007,1146
    933,Miyazaki-ken,2008,1141
    934,Miyazaki-ken,2009,1138
    935,Miyazaki-ken,2010,1135
    936,Miyazaki-ken,2011,1130
    937,Miyazaki-ken,2012,1125
    938,Miyazaki-ken,2013,1119
    939,Miyazaki-ken,2014,1112
    940,Miyazaki-ken,2015,1104
    941,Miyazaki-ken,2016,1097
    942,Miyazaki-ken,2017,1091
    943,Miyazaki-ken,2018,1084
    944,Miyazaki-ken,2019,1077
    945,Miyazaki-ken,2020,1070
    946,Kagoshima-ken,2000,1786
    947,Kagoshima-ken,2001,1782
    948,Kagoshima-ken,2002,1776
    949,Kagoshima-ken,2003,1770
    950,Kagoshima-ken,2004,1763
    951,Kagoshima-ken,2005,1753
    952,Kagoshima-ken,2006,1744
    953,Kagoshima-ken,2007,1733
    954,Kagoshima-ken,2008,1721
    955,Kagoshima-ken,2009,1712
    956,Kagoshima-ken,2010,1706
    957,Kagoshima-ken,2011,1697
    958,Kagoshima-ken,2012,1687
    959,Kagoshima-ken,2013,1675
    960,Kagoshima-ken,2014,1662
    961,Kagoshima-ken,2015,1648
    962,Kagoshima-ken,2016,1637
    963,Kagoshima-ken,2017,1626
    964,Kagoshima-ken,2018,1614
    965,Kagoshima-ken,2019,1602
    966,Kagoshima-ken,2020,1588
    967,Okinawa-ken,2000,1318
    968,Okinawa-ken,2001,1327
    969,Okinawa-ken,2002,1336
    970,Okinawa-ken,2003,1345
    971,Okinawa-ken,2004,1353
    972,Okinawa-ken,2005,1362
    973,Okinawa-ken,2006,1369
    974,Okinawa-ken,2007,1374
    975,Okinawa-ken,2008,1378
    976,Okinawa-ken,2009,1385
    977,Okinawa-ken,2010,1393
    978,Okinawa-ken,2011,1402
    979,Okinawa-ken,2012,1411
    980,Okinawa-ken,2013,1419
    981,Okinawa-ken,2014,1426
    982,Okinawa-ken,2015,1434
    983,Okinawa-ken,2016,1442
    984,Okinawa-ken,2017,1448
    985,Okinawa-ken,2018,1454
    986,Okinawa-ken,2019,1462
    987,Okinawa-ken,2020,1467
    ```

    </details>

1. PostgreSQL構築

    ```shell
    sudo apt install postgresql postgresql-contrib
    sudo su - postgres
    psql
      postgres=> CREATE ROLE test WITH LOGIN PASSWORD 'test';
      postgres=> GRANT pg_read_server_files TO test;
      postgres=> \du
      postgres=> CREATE DATABASE test WITH OWNER test;
      postgres=> \l
      postgres=> exit;
    exit
    sudo vi /etc/postgresql/14/main/pg_hba.conf
      local host all all scram-sha-256
    sudo vi /etc/postgresql/14/main/postgresql.conf
      search_path = '"$user", $user, public'  # schema names
    sudo systemctl restart postgresql
    psql -d test -U test
      test=> CREATE SCHEMA test;
      test=> \dn
      test=> CREATE TABLE test("ID" int, "prefecture" varchar(32), "year" int, "population" int);
      test=> \COPY test FROM './population_2000-2020.csv' WITH CSV HEADER;
    ```

1. uvインストール

    ```shell
    curl -LsSf https://astral.sh/uv/install.sh | sh
    ```

    ※ uvはPythonの管理ツール(<https://docs.astral.sh/uv/>)

1. 仮想環境作成

    ```shell
    cd <任意のディレクトリ>
    uv init mcp_test --python 3.12
    cd mcp_test
    uv sync
    . .venv/bin/activate
     ```

1. ディレクトリ作成

    ```shell
    cd mcp_test
    mkdir -p agent_sample/agent
    mkdir server_sample
    ```

1. ADKインストール

    ```shell
    cd mcp_test
    uv add "google-adk"
    uv add "litellm"
    ```

    ※ litellmは、google-adkにGoogle外のLLM（OpenAI等）を渡すため  
       <https://docs.litellm.ai/docs/providers/>

1. MCP Python SDKインストール

    ```shell
    cd mcp_test
    uv add "mcp[cli]"
    ```

1. pyodbcインストール

    ```shell
    cd mcp_test
    uv add "pyodbc"
    ```

1. ODBCドライバインストール

    ```shell
    sudo apt install odbc-postgresql
    sudo odbcinst -i -d -f /etc/odbcinst.ini
    odbcinst -q -d
    ```

1. VSCodeの仮想環境認識設定

    mcp_test/.vscode/settings.json

    ```json
    "python.venvFolders": [
      ".venv"
    ]
    ```

1. Agent実装例

    <details><summary>mcp_test/agent_samples/agent.py</summary>

    ```python
    from contextlib import AsyncExitStack
    from dotenv import load_dotenv
    from google.adk.agents import LlmAgent
    from google.adk.models.lite_llm import LiteLlm
    from google.adk.tools.mcp_tool.mcp_toolset import MCPToolset, SseServerParams


    # load API key
    load_dotenv(".env")


    async def create_agent():
        async_exit_stack = AsyncExitStack()
        tools, _ = await MCPToolset.from_server(
            connection_params=SseServerParams(url="http://localhost:8080/sse"),
            async_exit_stack=async_exit_stack,
        )
        agent = LlmAgent(
            # model=LiteLlm(model="openrouter/anthropic/claude-3.7-sonnet:thinking"),
            model=LiteLlm(model="openai/gpt-4o"),
            name="agent",
            instruction="あなたは有能なアシスタントです。日本語で回答します。"
            "適切なデータがないかデータベースを確認します。データベースにはMCPでアクセスします。"
            "最初に、MCPサーバでテーブル一覧を取得して、質問に必要なデータがないか見てみましょう。",
            tools=[
                *tools,
            ],
        )
        return agent, async_exit_stack


        root_agent = create_agent()
    ```

    ※ 最小限の実装。root_agentにLiteLlmを通して~~Openrouter~~OpenAIのAgentを渡す  
    ※ .envに~~OPENROUTER_API_KEY=<あなたのOpenrouterのキー>~~`OPENAI_API_KEY=<あなたのOpenAIのキー>`を記載し、load_dotenvで読み込んでおく  
    ※ `adk web`を実行後、`http://127.0.0.1:8000`を開くとWebUIが出る

    </details>

1. MCP Server実装例  

    <details><summary>mcp_test/server_samples/server.py</summary>

    ```python
    import os
    import io
    import csv
    import click
    from contextlib import asynccontextmanager
    from collections.abc import AsyncIterator
    from dataclasses import dataclass
    from mcp.server.fastmcp import Context, FastMCP
    import pyodbc


    class PostgreDB:
        def __init__(
            self,
            database: str,
            user: str,
            password: str,
            server: str = "127.0.0.1",
            port: str = "5432",
        ):
            self.database = database
            self.user = user
            self.password = password
            self.server = server
            self.port = port

        def __await__(self):
            return self.__async_init().__await__()

        async def __async_init(self):
            self.__con: pyodbc.Connection = await self.__connect()
            self.__schemas: list = await self.__get_schemas()
            self.__tables: list[dict] = await self.__get_tables()
            return self

        async def __connect(self) -> pyodbc.Connection:
            os.environ["PGUSER"] = self.user
            os.environ["PGPASSWORD"] = self.password
            connection_string = f"DRIVER={{PostgreSQL Unicode}}; SERVER={self.server}; PORT={self.port}; DATABASE={self.database};"
            return pyodbc.connect(connection_string)

        async def __get_schemas(self) -> list[dict]:
            con: pyodbc.Connection = self.con
            sql = "SELECT schema_name FROM information_schema.schemata;"
            result = con.execute(sql).fetchall()
            schemas = list(
                set(self.rows2list(result)) - {"pg_catalog", "information_schema"}
            )
            return schemas

        async def __get_table(self, schema: str) -> list[str]:
            con: pyodbc.Connection = self.con
            sql = f"SELECT tablename FROM pg_tables where schemaname = '{schema}';"
            result = con.execute(sql).fetchall()
            tables = self.rows2list(result)
            return tables

        async def __get_tables(self) -> list[dict]:
            schemas: list = self.schemas if self.schemas else self.__get_schemas
            result = {}
            for schema in schemas:
                tables = await self.__get_table(schema)
                result[schema] = tables
            return result

        def rows2dict(self, rows: list[pyodbc.Cursor]) -> list[dict]:
            if len(rows) == 0:
                return []
            keys = [k[0] if len(k) > 0 else k for k in rows[0].cursor_description]
            result = [dict(zip(keys, row)) for row in rows]
            return result

        def rows2list(self, rows: list[pyodbc.Cursor]) -> list[str]:
            if len(rows) == 0:
                return []
            result = [str(row[0]) for row in rows]
            return result

        def rows2csv(self, rows: list[pyodbc.Cursor]) -> str:
            if len(rows) == 0:
                return ""
            d = self.rows2dict(rows)
            buffer = io.StringIO()
            writer = csv.DictWriter(buffer, fieldnames=d[0].keys(), quoting=csv.QUOTE_ALL)
            writer.writeheader()
            writer.writerows(d)
            result = buffer.getvalue()
            return result

        async def select(self, schema: str, table: str, limit: int) -> list[dict]:
            sql = f"SELECT * FROM {schema}.{table}"
            if limit > 0:
                sql += f" limit {limit}"
            sql += ";"
            result = self.con.execute(sql).fetchall()
            return self.rows2csv(result)

        @property
        def con(self):
            return self.__con

        @property
        def schemas(self):
            return self.__schemas

        @property
        def tables(self):
            return self.__tables


    @dataclass
    class AppContext:
        db: PostgreDB


    @asynccontextmanager
    async def app_lifespan(servver: FastMCP) -> AsyncIterator[AppContext]:
        # since this is sample, hard-coded.
        db: PostgreDB = await PostgreDB(
            database="test",
            user="test",
            password="test",
        )
        try:
            yield AppContext(db=db)
        finally:
            db.con.close()


    # Create an MCP server
    mcp = FastMCP("posetgresql_test", lifespan=app_lifespan, host="127.0.0.1", port="8080")


    # Resources
    # Note: ADK Web does not recognize the resources (maybe an oversight), so I use them as a tools.
    #
    # @mcp.resource(uri="schemas://main", mime_type="application/json")
    # async def get_schemas() -> list[str]:
    #    """This returns schemas.
    #
    #    Returns:
    #        list[str]: [schema1, schema2, ...]
    #    """
    #    ctx = mcp.get_context()
    #    db: PostgreDB = ctx.request_context.lifespan_context.db
    #    return db.schemas
    #
    #
    # @mcp.resource(uri="schemas://{schema}/tables", mime_type="application/json")
    # async def get_tables(schema: str) -> list[str]:
    #    """This returns tables in the schema.
    #
    #    Args:
    #        schema (str): Specify the schema.
    #
    #    Returns:
    #        list[str]: [table1, table2, ...]
    #    """
    #    ctx = mcp.get_context()
    #    db: PostgreDB = ctx.request_context.lifespan_context.db
    #    return db.tables[schema]
    #
    #
    # @mcp.resource(uri="tables://main", mime_type="application/json")
    # async def get_all_tables() -> dict[list[str]]:
    #    """This returns all tables in all schemas.
    #
    #    Returns:
    #        dict[list[str]]:  {schema1: [table1, table2, ...], schdma2: [], ...}.
    #    """
    #    ctx = mcp.get_context()
    #    db: PostgreDB = ctx.request_context.lifespan_context.db
    #    return db.tables


    # Tools
    @mcp.tool()
    async def get_all_tables(ctx: Context) -> dict[list[str]]:
        """This returns all tables in all schemas.

        Returns:
            dict[list[str]]:  {schema1: [table1, table2, ...], schdma2: [], ...}.
        """
        db: PostgreDB = ctx.request_context.lifespan_context.db
        return db.tables


    @mcp.tool()
    async def select(ctx: Context, schema: str, table: str, limit: int) -> list[dict]:
        """Return the result executed a SELECT claude.

        Args:
            schema (str): Specify the shema.
            table (str): Specify the table.
            limit (int): Specify The number of limit. 0 means no use of limit.

        Returns:
            list[dict]: [{"col1": val1, "col2": val2, ...}, ...]
        """
        db: PostgreDB = ctx.request_context.lifespan_context.db
        return await db.select(schema, table, limit)


    @click.command()
    @click.option(
        "--transport",
        "-t",
        type=str,
        default="sse",
        help='Specify "sse" or "stdio". Defaults to "sse"',
    )
    def main(transport: str):
        if transport not in ["sse", "stdio"]:
            raise click.BadParameter(f'Transport should be "sse" or "stdio". {transport=}')
        mcp.run(transport=transport)


    if __name__ == "__main__":
        main()
    ```

    ※ ADKだとresourcesが参照されてなさそうだったので（おそらく設定もれだが）、テーブル一覧等もtoolsに実装。  

    </details>

#### 起動手順

1. MCP Server起動
    1. MCP Inspector起動

        ```shell
        npx @modelcontextprotocol/inspector
        ```

        ※ MCP Inspector起動は、mcp cliによる以下コマンドの延長でも実行可能（MCPサーバは、STDIOモードで起動される）

        ```shell
        mcp dev server.py
        ```

    1. サーバ起動
        - ダイレクトに起動

            以下のスクリプトがserver.pyに記載されている必要がある。

            ```python
            if __name__ == "__main__":
                mcp.run(transport="sse")
            ```

        - ポートは8080にする

            MCPクライアントがデフォルトで8000を使うので避ける。

            ```python
            mcp = FastMCP("posetgresql_test", lifespan=app_lifespan, host="127.0.0.1", port="8080")
            ```

        起動コマンドは以下。

        ```shell
        python server.py
        ```

        または

        ```shell
        uv run server.py
        ```

    1. MCP Inspectorから動作確認

        Transport Type: `SSE`  
        URL: `http://localhost:8080/sse`

        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/325677/4f341113-2ba8-497d-bf02-b68c994a2067.png)

1. MCP Client起動
   1. MCP Client起動

      ```shell
      adk web
      ```

      ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/325677/8c5f50e5-8fbf-4767-abed-1db9fa3db89b.png)

    ※ システムプロンプトにMCPサーバを確認するよう入力してある