よく使うのでメモです。
IBM Cloudコンソールから資格情報を作成・参照
で表示できる資格情報の文字列、これはJSON形式なので、pythonでは辞書型として読み込めます。
よってpythonでdb2に接続するコードを書く際、この資格情報からいちいち必要な情報を抜き出してコピペが面倒な場合は、そのままコードにコピーして辞書型として読み込み、そこから抜き出せばよいのです。
当投稿ではこのコードをメモっておきます(簡単ですが)。
1. まずは資格情報をクリップボードにコピー
コピーアイコンから中身を見なくともすぐコピーできます。
Db2 on Cloudと Db2 Warehouse on Cloudではこの資格情報の構成が異なるので、以下2つ分けて説明します。
2. Db2 on Cloudの場合
2-1. 資格情報をペーストして変数に入れます
以下のコードのservice_db2_credencials の下に文字列をペーストします。
#service_db2_credencials =
# <ここにコピペ>
service_db2_credencials = \
例： 実際のデータをxxxでマスキングしてます
#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: ポート番号
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して確認：
print(f"databese: {databese}")
print(f"user: {user}")
print(f"password: {password}")
print(f"host: {host}")
print(f"port: {port}")
出力例: xxxxでマスキングしてます
databese: bludb
user: xxxxxxxx
password: xxxxxxxxxxxxxxxx
host: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxx.databases.appdomain.cloud
port: xxxxx
3. Db2 Warehouse on Cloudの場合
3-1. 資格情報をペーストして変数に入れます
以下のコードのservice_woc_credencials の下に文字列をペーストします。
#service_woc_credencials =
# <ここにコピペ>
service_woc_credencials = \
例： 実際のデータをxxxでマスキングしてます
#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: ポート番号
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して確認：
print(f"databese: {databese}")
print(f"user: {user}")
print(f"password: {password}")
print(f"host: {host}")
print(f"port: {port}")
出力例: xxxxでマスキングしてます
databese: BLUDB
user: xxxxxxxx
password: xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
host: xxxxxxxxxxxx.xxxxxxxx.db2w.cloud.ibm.com
port: xxxxx
4. 確認
上記のCodeでdatabese, user, password, portを取得したら、例えば以下のようにDb2に接続可能です。
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です。
import ibm_db
conn = ibm_db.connect(service_woc_credencials["ssldsn"] , "", "")
以上です。