[python] 在 oracle 的 in 中使用長字串搜尋

Visits: 2

在 oracle 的 in 中使用長字串搜尋

本文紀錄如何將字串陣列 (list of strings) 轉為長字串 (long string) 放入 cx_Oracle 的參數中進行查詢。

在 client 搜尋

透過 DBeaver 進行下列查詢

SELECT * FROM (
    SELECT TO_CHAR(ROWNUM) ID
    FROM DUAL
    CONNECT BY ROWNUM <= 10
)
WHERE ID IN (:IDS)

參數 IDS 輸入 '1', '2'
file

查詢結果
file

在 python 中以 cx_Oracle 進行查詢

同樣的查詢透過 cx_Oracle 在 python 中進行查詢,會查出空值。

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    SELECT * FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    )
    WHERE ID IN (:IDS)
"""
params = {
    'IDS': "'1', '2'"
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果為空陣列,代表查不到資料
file

解法

會有這樣的原因是 cx_Oracle 在 parsing parameter 的時候,只能接受單一字串,不能接受字串串列 (list of strings) ,所以要把 lsit of strings 變成 長字串 (long string) 並透過 cx_Oracle 進行解析

解法1: 使用 INSTR > 0 進行查詢

  • 使用 INSTR 進行查詢,用法為 {INSTR} (string , substring [, position [, occurrence]]) ,此函數的目標是取得 substring 在 string 中的位置
  • 若 INSTR > 0 則代表 substr 有在 string 中出現
import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    SELECT * FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    )
    WHERE INSTR(:IDS, ID) > 0
"""
params = {
    'IDS': '1,2'  # 從 list of strings 變成 long string
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果正確
file

解法2: 使用 REGEXP_STR 與 CONNECT BY LEVEL 進行查詢

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    WITH GET_ID AS(
        SELECT 
            REGEXP_SUBSTR(:IDS, '[^,]+', 1, LEVEL) IDS
        FROM DUAL
        CONNECT BY LEVEL <= LENGTH(:IDS) - LENGTH(REPLACE(:IDS, ',', '')) + 1
    )
    SELECT A.* FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    ) A
    INNER JOIN GET_ID B ON A.ID = B.IDS
"""
params = {
    'IDS': '1,2'  # 從 list of strings 變成 long string
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果正確
file

結論

  • INSTR 的方法比較好閱讀,跟透過 client 下的語法比較接近 (SELECT * FROM DUAL WHERE ID IN ('1', '2')),日後維護上也比較方便,但缺點是因為沒使用 index 所以效能比較差。
  • REGEXP_SUBSTRCONNECT BY LEVEL 的寫法因為有使用 index 所以效能比較好,但缺點是指令比較長也比較難維護,而且如果 parameter 有兩個,組成的 temp table 在 join 上會有問題。

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You may also like these