oracle dbは利用実績が少ない為、メモ。
Step1 「Instant Client for Linux x86-64」
https://www.oracle.com/jp/database/technologies/instant-client/downloads.html
にある Instant Client for Linux x86-64 (zip)をdownload し、/etc/ld.so.conf へ登録する程度です。
# mkdir -p /opt/oracle # cd /opt/oracle # wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-basic-linux.x64-19.8.0.0.0dbru.zip # unzip instantclient-basic-linux.x64-19.8.0.0.0dbru.zip # ls -l instantclient_19_8 total 233556 -rwxr-xr-x 1 root root 41696 Jul 3 13:44 adrci -r-xr-xr-x 1 root root 5780 Jul 3 13:44 BASIC_LICENSE -rw-r--r-- 1 root root 1632 Jul 3 13:44 BASIC_README -rwxr-xr-x 1 root root 59272 Jul 3 13:44 genezi -rwxr-xr-x 1 root root 8019360 Jul 3 13:44 libclntshcore.so.19.1 lrwxrwxrwx 1 root root 17 Dec 26 06:10 libclntsh.so -> libclntsh.so.19.1 lrwxrwxrwx 1 root root 17 Dec 26 06:10 libclntsh.so.10.1 -> libclntsh.so.19.1 lrwxrwxrwx 1 root root 17 Dec 26 06:10 libclntsh.so.11.1 -> libclntsh.so.19.1 lrwxrwxrwx 1 root root 17 Dec 26 06:10 libclntsh.so.12.1 -> libclntsh.so.19.1 lrwxrwxrwx 1 root root 17 Dec 26 06:10 libclntsh.so.18.1 -> libclntsh.so.19.1 -rwxr-xr-x 1 root root 80804848 Jul 3 13:44 libclntsh.so.19.1 -r-xr-xr-x 1 root root 3608240 Jul 3 13:44 libipc1.so -r-xr-xr-x 1 root root 478096 Jul 3 13:44 libmql1.so -rwxr-xr-x 1 root root 6586360 Jul 3 13:44 libnnz19.so lrwxrwxrwx 1 root root 15 Dec 26 06:10 libocci.so -> libocci.so.19.1 lrwxrwxrwx 1 root root 15 Dec 26 06:10 libocci.so.10.1 -> libocci.so.19.1 lrwxrwxrwx 1 root root 15 Dec 26 06:10 libocci.so.11.1 -> libocci.so.19.1 lrwxrwxrwx 1 root root 15 Dec 26 06:10 libocci.so.12.1 -> libocci.so.19.1 lrwxrwxrwx 1 root root 15 Dec 26 06:10 libocci.so.18.1 -> libocci.so.19.1 -rwxr-xr-x 1 root root 2338456 Jul 3 13:44 libocci.so.19.1 -rwxr-xr-x 1 root root 130527608 Jul 3 13:44 libociei.so -r-xr-xr-x 1 root root 153600 Jul 3 13:44 libocijdbc19.so -rwxr-xr-x 1 root root 115760 Jul 3 13:44 liboramysql19.so drwxr-xr-x 3 root root 19 Jul 3 13:44 network -rw-r--r-- 1 root root 4396157 Jul 3 13:44 ojdbc8.jar -rw-r--r-- 1 root root 1681792 Jul 3 13:44 ucp.jar -rwxr-xr-x 1 root root 236256 Jul 3 13:44 uidrvci -rw-r--r-- 1 root root 74263 Jul 3 13:44 xstreams.jar # vi /etc/ld.so.conf /opt/oracle/instantclient_19_8 # <-- ADD # ldconfig
Step2 pip3 install cx_Oracle
# pip3 install cx_Oracle
Step3 sample script
#!/usr/bin/python3 # -*- coding: utf-8 -*- import cx_Oracle import random import string # 接続情報(ID/PW, HOST, SERVICE NAME)は、以下の tnsnames.ora の通りです。 # /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora db_user_pw_host_port_service = "system/PWはないしょ@localhost:1521/orcl.a5.jp" def main(): db_conn, db_cursor = connect_db() insert_to_tbl(db_cursor,"TEST_TBL_1",10000) insert_to_tbl(db_cursor,"TEST_TBL_2",1000000) db_conn.commit() db_cursor.close() db_conn.close() def insert_to_tbl(db_cursor, tbl_name, row_size): sql_tmpl =''' insert into %s (num_1, num_2, str_1, str_2) values(:1, :2, :3, :4) ''' sql = sql_tmpl % tbl_name db_cursor.prepare(sql) id = 0 sql_vals_rows = [] while id < row_size: id += 1 sql_vals_rows.append([id, random.randint(0,1000), rand_str(4), rand_str(4) ]) if len(sql_vals_rows) > 50: print(tbl_name,":", id,"/",row_size) # executemany()は、bulk insertのような気がしていますが # 実際の処理速度は計測していません # db_cursor.executemany(sql, sql_vals_rows) db_cursor.executemany(None, sql_vals_rows) sql_vals_rows = [] if len(sql_vals_rows) > 0: db_cursor.executemany(None, sql_vals_rows) # ランダムな文字列を生成します def rand_str(str_len): dat = "ABCD" #dat = string.ascii_letters+string.digits #dat = string.digits + string.ascii_lowercase + string.ascii_uppercase return ''.join(random.choices(dat, k=str_len)) def connect_db(): # print(cx_Oracle.clientversion()) connect = cx_Oracle.connect(db_user_pw_host_port_service) cursor = connect.cursor() # カーソルを取得 return connect,cursor if __name__ == '__main__': main()
