Db2 on Cloud & Db2 Warehouse on Cloudの資格情報をpythonの変数に入れて必要な接続情報を取得

よく使うのでメモです。

[IBM Cloudコンソールから資格情報を作成・参照](https://qiita.com/nishikyon/items/69fee851849e7ef7e163#ibm-cloud%E3%82%B3%E3%83%B3%E3%82%BD%E3%83%BC%E3%83%AB%E3%81%8B%E3%82%89%E8%B3%87%E6%A0%BC%E6%83%85%E5%A0%B1%E3%82%92%E4%BD%9C%E6%88%90%E5%8F%82%E7%85%A7lite%E3%83%97%E3%83%A9%E3%83%B3%E3%81%AE%E5%A0%B4%E5%90%88%E3%81%AF%E3%81%93%E3%82%8C%E3%81%AE%E3%81%BF)
で表示できる資格情報の文字列、これはJSON形式なので、pythonでは辞書型として読み込めます。

よってpythonでdb2に接続するコードを書く際、この資格情報からいちいち必要な情報を抜き出してコピペが面倒な場合は、そのままコードにコピーして辞書型として読み込み、そこから抜き出せばよいのです。

当投稿ではこのコードをメモっておきます(簡単ですが)。

# 1. まずは資格情報をクリップボードにコピー
コピーアイコンから中身を見なくともすぐコピーできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/be2cddd2-8e9b-878a-222a-1b65c6085af0.png)

Db2 on Cloudと Db2 Warehouse on Cloudではこの資格情報の構成が異なるので、以下2つ分けて説明します。

# 2. Db2 on Cloudの場合
## 2-1. 資格情報をペーストして変数に入れます
以下のコードのservice_db2_credencials の**下に**文字列をペーストします。
```python
#service_db2_credencials = 
# <ここにコピペ>
service_db2_credencials = \

```
例：　実際のデータをxxxでマスキングしてます
```python
#service_db2_credencials = 
# <ここにコピペ>
service_db2_credencials = \
{
  "apikey": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "connection": {
    "cli": {
      "arguments": [
        [
          "-u",
          "xxxxxxxx",
          "-p",
          "xxxxxxxxxxxxxxxx",
          "--ssl",
          "--sslCAFile",
          "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
          "--authenticationDatabase",
          "admin",
          "--host",
          "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud:xxxxx"
        ]
      ],
      "bin": "db2",
      "certificate": {
        "certificate_base64": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        "name": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
      },
      "composed": [
        "db2 -u xxxxxxxx -p xxxxxxxxxxxxxxxx --ssl --sslCAFile xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx --authenticationDatabase admin --host xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud:xxxxx"
      ],
      "environment": {},
      "type": "cli"
    },
    "db2": {
      "authentication": {
        "method": "direct",
        "password": "xxxxxxxxxxxxxxxx",
        "username": "xxxxxxxx"
      },
      "certificate": {
        "certificate_base64": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        "name": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
      },
      "composed": [
        "db2://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud:xxxxx/bludb?authSource=admin&replicaSet=replset"
      ],
      "database": "bludb",
      "host_ros": [
        "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud:xxxxx"
      ],
      "hosts": [
        {
          "hostname": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud",
          "port": xxxxx
        }
      ],
      "jdbc_url": [
        "jdbc:db2://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud:xxxxx/bludb:user=<userid>;password=<your_password>;sslConnection=true;"
      ],
      "path": "/bludb",
      "query_options": {
        "authSource": "admin",
        "replicaSet": "replset"
      },
      "replica_set": "replset",
      "scheme": "db2",
      "type": "uri"
    }
  },
  "iam_apikey_description": "Auto-generated for key crn:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:resource-key:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "iam_apikey_name": "xxxx",
  "iam_role_crn": "crn:v1:bluemix:public:iam::::serviceRole:Manager",
  "iam_serviceid_crn": "crn:v1:bluemix:public:iam-identity::x/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "instance_administration_api": {
    "deployment_id": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "instance_id": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "root": "https://xxxxxxxxxxxxxxxxxxxxxxxxxx/v5/ibm"
  }
}
```

## 2-2. 接続情報を変数から取り出す

Db接続に必要な以下の情報を取得します
- databese: データベース名
- user： ユーザーid
- password: パスワード
- host: ホスト名
- port: ポート番号

```python
databese = service_db2_credencials["connection"]["db2"]["database"]
user = service_db2_credencials["connection"]["db2"]["authentication"]["username"]
password = service_db2_credencials["connection"]["db2"]["authentication"]["password"]
host =  service_db2_credencials["connection"]["db2"]["hosts"][0]["hostname"]
port =  service_db2_credencials["connection"]["db2"]["hosts"][0]["port"]
```
printして確認：
```python
print(f"databese: {databese}")
print(f"user: {user}")
print(f"password: {password}")
print(f"host: {host}")
print(f"port: {port}")
```

出力例: xxxxでマスキングしてます
```terminal
databese: bludb
user: xxxxxxxx
password: xxxxxxxxxxxxxxxx
host: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud
port: xxxxx
```

# 3. Db2 Warehouse on Cloudの場合
## 3-1. 資格情報をペーストして変数に入れます
以下のコードのservice_woc_credencials の下に文字列をペーストします。
```python
#service_woc_credencials = 
# <ここにコピペ>
service_woc_credencials = \

```
例：　実際のデータをxxxでマスキングしてます
```python
#service_woc_credencials = 
# <ここにコピペ>
service_woc_credencials = \
{
  "apikey": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "db": "BLUDB",
  "host": "xxxxxxxxxxxxxxxxxxxxx.db2w.cloud.ibm.com",
  "hostname": "xxxxxxxxxxxxxxxxxxxxx.db2w.cloud.ibm.com",
  "https_url": "https://xxxxxxxxxxxxxxxxxxxxx.db2w.cloud.ibm.com",
  "iam_apikey_description": "Auto-generated for key xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "iam_apikey_name": "xxxxxxxxxxxxxxxxxxxx",
  "iam_role_crn": "crn:v1:bluemix:public:iam::::serviceRole:Manager",
  "iam_serviceid_crn": "crn:v1:bluemix:public:iam-identity::xxxxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "parameters": {
    "role_crn": "crn:v1:bluemix:public:iam::::serviceRole:Manager",
    "serviceid_crn": "crn:v1:bluemix:public:iam-identity::x/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
  },
  "password": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "port": xxxxx,
  "ssldsn": "DATABASE=BLUDB;HOSTNAME=xxxxxxxxxxxxxxxxxxxxxxxxxx.cloud.ibm.com;PORT=xxxxx;PROTOCOL=TCPIP;UID=xxxxxxxx;PWD=xxxxxxxxxxxxxxxxxxxxxxxxxxxxx;Security=SSL;",
  "ssljdbcurl": "jdbc:db2://xxxxxxxxxxxxxxxxxxxxx.db2w.cloud.ibm.com:xxxxx/BLUDB:sslConnection=true;",
  "uri": "db2://xxxxxxxx:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx@xxxxxxxxxxxxxxxxxxxxx.db2w.cloud.ibm.com:xxxxx/BLUDB?ssl=true;",
  "username": "xxxxxxxx"
}
```

## 3-2. 接続情報を変数から取り出す

Db接続に必要な以下の情報を取得します
- databese: データベース名
- user： ユーザーid
- password: パスワード
- host: ホスト名
- port: ポート番号

```python
databese = service_woc_credencials["db"]
user = service_woc_credencials["username"]
password = service_woc_credencials["password"]
host =  service_woc_credencials["host"]
port =  service_woc_credencials["port"] 
```


printして確認：
```python
print(f"databese: {databese}")
print(f"user: {user}")
print(f"password: {password}")
print(f"host: {host}")
print(f"port: {port}")
```

出力例: xxxxでマスキングしてます
```terminal
databese: BLUDB
user: xxxxxxxx
password: xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
host: xxxxxxxxxxxx.xxxxxxxx.db2w.cloud.ibm.com
port: xxxxx
```

# 4. 確認
上記のCodeで`databese`, `user`, `password`, `port`を取得したら、例えば以下のようにDb2に接続可能です。
```py
import ibm_db

conn = ibm_db.connect(f"DATABASE={databese};HOSTNAME={host};PORT={port};PROTOCOL=TCPIP;UID={user};\
                PWD={password};Security=SSL;", "", "")
```

またDb2 Warehouse　on Cloudであれば接続変数がそのまま書かれているので、そのまま以下でもOKです。
```py
import ibm_db

conn = ibm_db.connect(service_woc_credencials["ssldsn"] , "", "")  
```

以上です。