CP4DのWatson StudioのJupyterからSQLAlchemyでDb2を操作する

CP4DのWatson StudioのJupyterからDb2を操作します。
CP4Dの接続オブジェクトからDb2への接続情報を取得して、SQLAlchemyアダプターのibm_db_saで接続します。

- テスト環境
    - CP4D 4.0.5
    - db2warehouse on cloud
    - python　3.8.11
    - pandas 1.2.4
    - ibm-watson-studio-lib 3.0.6
    - SQLAlchemy 1.4.22
    - ibm_db_sa 0.3.7
   
#1 接続オブジェクトのリストを取得
Watson StudioのプロジェクトにDb2への接続オブジェクト（例：DB2WHOC_HDM）を登録しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/dd6c5a61-3367-7e4b-7179-d45428e2db49.png)


接続オブジェクトにはDb2に接続するための情報が記録されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/4046f3ad-65a1-db01-f591-d69493311019.png)


ibm_watson_studio_libでPythonコードから接続オブジェクトにアクセスが可能です。

https://www.ibm.com/docs/ja/cloud-paks/cp-data/4.0?topic=lib-watson-studio-python#get-conn-info

list_connectionsで接続オブジェクトのリストを得ることができます。

```python:接続オブジェクトのリスト
import ibm_watson_studio_lib
wslib = ibm_watson_studio_lib.access_project_or_space()
#接続オブジェクトのリスト
assets=wslib.list_connections()
wslib.show(assets)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/44adda6a-932f-ed07-40da-9ea058361432.png)


get_connectionで接続オブジェクトの設定を取得できます。database, hostname, username, passwordなどの情報が取れます。

```python:接続オブジェクトの情報を取得
connection_config=wslib.get_connection('DB2WHOC_HDM')
connection_config
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/a9b67ba6-6c13-7707-ee64-89494a7cb434.png)


#2 Db2への接続
Db2を扱うドライバーやアダプターはいろいろありますが、ここではpandasへの読み書きが容易なSQL Alchemyのアダプターであるibm_db_saをつかってDb2へ接続します。

* 参考:Python ドライバー、SQLAlchemy アダプター、Django アダプターのインストールの検証 - IBM Documentation 

https://www.ibm.com/docs/ja/db2/11.5?topic=psdf-verifying-python-driver-sqlalchemy-adapter-django-adapter-installation


```python:SQLAlchemyで接続
import sqlalchemy
from sqlalchemy import *
import ibm_db_sa
con_params = 'ibm_db_sa://%s:%s@%s:%s/%s' % (
    connection_config['username'], connection_config['password'], connection_config['host'], connection_config['port'], connection_config['database'])
if connection_config['ssl']=='true' :
    con_params = con_params+ ";Security=SSL"
con_sa = sqlalchemy.create_engine(con_params)
```



#3 Db2からSELECT

SELECT結果をpd.read_sqlでpandasのDataframeに読み込んでみます。

```python:データフレームに検索結果の読み込み
import pandas as pd
# SELECT文の実行
sql = "select 2 as ID, 'suzuki' as NAME, 0.1 as RATE, SYSDATE as TDATE from SYSIBM.SYSDUMMY1" \
   " union all select 1 as ID, 'sato' as NAME, 2.5 as RATE, SYSDATE as TDATE from SYSIBM.SYSDUMMY1"
df = pd.read_sql(sql, con_sa)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/95150cef-af74-5894-aac8-e360a7f766ea.png)

データタイプを確認しておきます。文字列型はobject型になっています。

```python:データタイプの確認
df.dtypes
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/4807e5b7-c574-cc03-9da1-0f0ad7d1d6db.png)


#4 Db2のテーブルへの書き込み
DataFrameをテーブルとして書き込みます。何度も実行することを想定して、すでに表がある場合はDROPしてCREATEしなおす、洗い替えをするコードを書きました。

```python:if_exists='replace'が動作しないので削除をしておく
sql = text('DROP TABLE IF EXISTS hkwddummy;')
result = con_sa.execute(sql)
```

to_sqlは、DataFrameのデータ型に従って、CREATE TABLEとINSERTを自動で行ってくれます。ただ、object型のままだとDb2上でCLOBのデータ型になってしまったので、String(255) にマッピングしなおしました。
最適なサイズにするためには、きっちりとした型指定を行ってください。

```python:dfのテーブルへの書き込み
＃object型をCLOB型にしないためにVARCHAR(255)で指定。
cols = df.dtypes[df.dtypes=='object'].index
type_mapping = {col : String(255) for col in cols }
df.to_sql(name='hkwddummy', con=con_sa, schema='hkwd', dtype=type_mapping)
```

* 参考：sqlalchemy/pandas - sqlalchemy読み込み用のコラムをCLOBとして使用する - jpndev

https://jpndev.com/post/431010

テーブルのスキーマ情報は以下で確認ができます。

```python:作成されたテーブルのスキーマ情報の確認
inspector = sqlalchemy.inspect(con_sa)
inspector.get_columns("hkwddummy")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/e2873cf8-6fec-b570-8080-99d7de33dba0.png)


* 参考：SQLAlchemyを利用して既存テーブルのメタデータを取得する - Qiita

https://qiita.com/kurukuruz/items/4d3569edf40582259d1b

#5 接続破棄
```python:破棄
con_sa.dispose()
```

# サンプルコード

https://github.com/hkwd/220201watsonstudio/blob/master/220201db2test/db2whtest2.ipynb
# 参考
Cloud Pak for DataのNotebookでDb2のデータを読み書きする 

https://qiita.com/ttsuzuku/items/fa5453b42f064cef69ac

watson studioのJupyter notebookからdb2へアクセス 

https://qiita.com/makaishi2/items/791fefa540c332a090f0