【Python3】Oracle Databaseへの接続とSQL実行【cx_Oracle】

はじめに

Python3でのcx_Oracleパッケージの使い方をまとめました。
これさえあれば、ある程度の開発は行えると思います。

大事なことなので初めに言っておきますが、公式ドキュメントを見るのが一番正確ですよ。
cx_Oracle公式ドキュメント

システム構成

• クライアントPC
  • Windows10 Pro 64bit
  • Windows PowerShell
  • Python 3.8.0
  • pip 19.2.3
  • cx_Oracle 7.2.3
• データベース
  • Oracle Database 12c Release 1

パッケージインストール

PowerShell

pip install cx_Oracle

エラー発生時
Windowsには標準でCコンパイラが付属していないため、pipでのインストール時に下記メッセージを含むエラーが発生する場合がある。

error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": https://visualstudio.microsoft.com/downloads/

その場合はVisual Studioのダウンロードページにある「Build Tools for Visual Studio 2019」をインストールする必要がある。(2019/11/23現在)

[最低限必要な構成]
ワークロード
・ C++ Build Tools
オプション
・ MSVC v142 - VS 2019 C++ x64/x86 ビルド ツール
・ Windows 10 SDK

データベースへの接続

connect.py

# パッケージのインポート
import cx_Oracle

HOST = "localhost"
PORT = 1521
SVC_NM = "hoge.example.com"

# 接続記述子の生成
dsn = cx_Oracle.makedsn(HOST, PORT, service_name = SVC_NM)

# コネクションの確立
connection = cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8")

# SQL発行などの処理...

# コネクションの解放
connection.close()

パッケージのインポート

import cx_Oracle

接続記述子の生成

HOST = "localhost"
PORT = 1521

# SID(インスタンス識別子)を使用する場合
SID = "hoge"
dsn = cx_Oracle.makedsn(HOST, PORT, sid = SID)

# SERVICE_NAME(サービス名)を使用する場合
SVC_NM = "hoge.example.com"
dsn = cx_Oracle.makedsn(HOST, PORT, service_name = SVC_NM)

# NET_SERVICE_NAME(ネットサービス名、接続識別子)を使用する場合
NET_SN = "hogenet"
dsn = cx_Oracle.makedsn(HOST, PORT, NET_SN)

# 生成された接続記述子(例: SERVICE_NAME)
print(dsn)
# >> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hoge.example.com)))

cx_Oracle.makedsn()
　引数で渡したパラメータを基に、接続識別子を返します。

※ネットサービス名(接続識別子)を使用する場合
予め、クライアント側でtnsnames.oraの構成が必要です。

コネクションの確立と解放

# コネクションの確立
connection = cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8")

# SQL発行などの処理...

# コネクションの解放
connection.close()

with構文を使用して、withブロックを抜けたら自動的にコネクションを解放することが可能です。
特別な理由が無ければ、個人的には↓こちらの方が良いと思います。

with cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8") as connection:
    # SQL発行などの処理...
    # connection.close()の記述は不要

SQL実行

query.py

# カーソル生成
cursor = connection.cursor()

# バインド変数
sql = "select * from countries where country_id = :id"
bind_data = { id: 1 }

# SQL発行
cursor.execute(sql, bind_data)

# データ取得
rows = cursor.fetchall()

# バインドする値の変更
bind_data["id"] = 2

# SQL発行(ソフトパース)
cursor.execute(sql, bind_data)

# カーソル解放
cursor.close()

カーソルの生成と解放

# カーソル生成
cursor = connection.cursor()

# カーソル解放
cursor.close()

コネクションと同様にwith構文で自動的に解放することが可能です。
特に理由が無ければ、同じく↓こちらをお勧めします。

with connection.cursor() as cursor:
    # SQL発行など...
    # cursor.close()の記述は不要

バインド変数

# バインド変数(プレースホルダ)
sql = "select country_id, country_name from countries where country_id = :id"

# バインドする値の定義
# 辞書型の場合
bind_data = { id: 1 }

# リスト型の場合
bind_data = [1]

辞書型	リスト型
バインド変数とkeyが一致した値が割り当てられる	SQLの先頭からのバインド変数の配置順に値が割り当てられる

SQL発行

cursor.execute(sql, bind_data)

# キーワード引数も可
cursor.execute(sql, id = 1)

# バインド変数の値を変えてSQL再発行
bind_data["id"] = 2
cursor.execute(sql, bind_data)

• SQLの文末にセミコロン(;)は不要
• SELECT文だけでなく、INSERT文やUPDATE文なども同様に発行可能
• バインド変数の値を変えて、SQLを再発行することが可能

SOFT PARSE (ソフトパース)
発行されたSQLはパーサによって解析され、共有プールにキャッシュされる(HARD PARSE(ハードパース))。
ソフトパースでは、共有プールにキャッシュされた解析結果を再使用することにより、パフォーマンスの向上が期待できる。

データ取得

# カーソルから一レコードずつ取得
row = cursor.fetchone()
print(row)
# >> (1, Japan)

# カーソルから任意のレコード数を取得(例: 10レコード)
numRows = 10
rows = cursor.fetchmany(numRows)
print(rows)
# >> [(1, Japan)]

# カーソルから全てのレコードを取得
rows = cursor.fetchall()
print(rows)
# >> [(1, Japan)]

• データはカーソルからタプルとして取得される
• 複数レコードを取得した場合は、タプルのリストとして取得される

トランザクション

transaction.py

# 明示的なトランザクションの開始
connection.begin()

# SQL発行
cursor.execute(
    "insert into countries (country_id, country_name) values (:id, :name))",
    id = 2, name = "United States"
)

# コミット
connection.commit()

# ロールバック
connection.rollback()

明示的なトランザクションの開始

# 省略可能
connection.begin()

Oracle Databaseでは最初のSQLが実行された時点で自動的にトランザクションが開始されるため、特別な理由が無ければ明示的にトランザクションを開始する必要はありません。

コミットとロールバック

# コミット
connection.commit()

# ロールバック
connection.rollback()

コミットせずにデータベース接続が閉じられた場合、トランザクションはロールバックされます。
※connection.autocommitを有効にした場合を除く

---

Python3の開発環境構築についてはコチラ！
【Python3】開発環境構築《Windows編》

Python3のチートシートもあります！
【Python3】他言語エンジニアのためのPythonチートシート《基礎編》
【Python3】他言語エンジニアのためのPythonチートシート《オブジェクト指向編》