OCIのVMからADWへpythonでアクセスする

#はじめに
OCIのVMにOracle Clientをインストール、cx_Oracleを使ってpythonでADWをつっつく方法を紹介します。

#Oracle Clientのインストール
OCIのVMはubuntu 20.04を使っています。ここにOracle Clientをインストールします。
LinuxのOracle Clientは以下からダウンロードできます。

https://www.oracle.com/jp/database/technologies/instant-client/linux-x86-64-downloads.html

ちなみにmacos版は以下からダウンロード可能です。

https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html

Basicパッケージ（RPM）があれば、cx_Oracleを使ってのアクセスは可能となります。あとは必要に応じてですが、SQL*Plusパッケージ（RPM）、ツール・パッケージ（RPM）も入れておくと便利です。

ダウンロードしたらrpmパッケージをdebパッケージにalienコマンドで変換します。

```
$ sudo apt install alien
$ sudo alien -c ./oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm 
$ sudo alien -c ./oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm 
$ sudo alien -c ./oracle-instantclient19.6-tools-19.6.0.0.0-1.x86_64.rpm 
```
そしてdpkgでインストールします。

```
$ sudo dpkg -i ./oracle-instantclient19.6-basic_19.6.0.0.0-2_amd64.deb 
$ sudo dpkg -i ./oracle-instantclient19.6-sqlplus_19.6.0.0.0-2_amd64.deb 
$ sudo dpkg -i ./oracle-instantclient19.6-tools_19.6.0.0.0-2_amd64.deb 
```
Oracle Clientは以下のパスにインストールされます。
/usr/lib/oracle/19.6/client64

.bashrcにパスを通しておきましょう。

```$HOME/.bashrc
export ORACLE_HOME=/usr/lib/oracle/19.6/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export NLS_LANG=Japanese_Japan.UTF8
export PATH=$ORACLE_HOME/bin:$PATH
```

#SQL*PlusによるADW接続
ADWへの接続はクライアント資格証明(ウォレット)を使います。以下の手順に従いWallet_\<database_name\>.zipをダウンロードします。

https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/connect-download-wallet.html

適当な場所で解凍します。

```
$ mkdir -p $HOME/oracle/lib/network/admin
$ sudo apt install unzip
$ cd $HOME/oracle/lib/network/admin
$ unzip Wallet_<database_name>.zip
```
解凍されたファイルのうち、sqlnet.oraを編集します。

```sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/ubuntu/oracle/lib/network/admin")))
SSL_SERVER_DN_MATCH=yes
```

環境変数を通して準備完了です。

```$HOME/.bashrc
export TNS_ADMIN=/home/ubuntu/oracle/lib/network/admin
```

では、sqlplusを実行してみましょう。sqlplusの引数は、DBユーザ/パスワード＠TNS名となりますが、TNS名はWallet_\<database_name\>.zipをunzipしたファイルにあるtnsnames.oraに記載があります。
\<database_name\>_high, \<database_name\>_low,\<database_name\>_mediumという３つのTNS名が定義されていますが、それぞれの意味は以下に記載があります。同時問い合わせ数とパフォーマンス定義の違いになります。

https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/connect-predefined.html

```
$ . $HOME/.bashrc
$ sqlplus <user_id>/<password>@<database_name>_low

SQL*Plus: Release 19.0.0.0.0 - Production on 日 8月 23 21:11:15 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 日 8月  23 2020 21:10:15 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
に接続されました。
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
	 0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
```

# cx_OracleによるADW接続
次にpython用にOracleが作成しているモジュールcx_Oracleを使ってみます。
ubuntuにpip, cx_Oracleをインストールします。

```
$ sudo apt install puthon3-pip
$ sudo python3 -m pip install cx_Oracle
```

testスクリプトを作って接続確認しましょう。

```test.py
import cx_Oracle

con=cx_Oracle.connect(user='<user_id>',password='<password>',dsn='<database_name>_low')

sql='select * from v$version'
print(sql)
cur = con.cursor()
for row in cur.execute(sql):
    print(row)
```

```
$ python3 test.py
select * from v$version
('Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production', 'Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.5.0.0.0', 'Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production', 0)
```

# Power Rock Today オンエアリスト更新
「[Oracle APEXによるRadio オンエアリストの検索アプリ作成](https://qiita.com/calbeez/items/98cb956c579b22aa80f3)」にある通り、[PRTオンエアリスト ver.2](https://herrkf.com/prtonair/)から、Power Rock Todayというラジオ番組のオンエアリストをスクレイピングし、ADWのテーブルにインポート、検索できるようにしています。

https://cmdfvmibkwubzxh-db202008140156.adb.ap-tokyo-1.oraclecloudapps.com/ords/r/calbeez/power-rock-today-on-air-database/

番組は今も続いており、毎週オンエアリストが追加されるため、この環境を使いpythonスクリプトで自動更新できるようにしました。
前回作成したスクレイピングのスクリプトを使って、更新用のスクリプトへ編集しています。

https://github.com/calbeez/PRT/blob/master/insertPRT.py

ADW上のテーブル（calbeez.onairlist）から最終日付を持ってきて、以降のオンエアリストをスクレイピング、結果をリスト（配列）に突っ込んで、executemany()を使い、まとめてInsertしています。リストを一括処理できて良い感じです。

```
# onairlistへのinsert
if len(rows) != 0:
    try:
        cur.executemany("insert into calbeez.onairlist(maximum, year, month, day, hour, minute, title, artist) values (:1, :2, :3, :4, :5, :6, :7, :8)", rows)
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print(error.message)
    else:
        con.commit()
        print(len(rows), " rows inserted.")
    finally:
        cur.close()
        con.close()
else:
    print("On air list is not found.")
```

呼び出しのshell scriptも用意しました。

https://github.com/calbeez/PRT/blob/master/insertPRT.sh

毎週日曜朝にはオンエアリストが追加されるので、cronで実行します。

```
$ crontab -e
# Add new Power Rock Today Playlist
0 10 * * 0 /home/ubuntu/bin/insertPRT.sh 1>> /home/ubuntu/log/insertPRT.log 2>&1
```

これで自動更新されるようになりました。