파이썬에서 DB 프로시저 호출하기 - pyodbc에서 procedure 호출

제가 사용중인 ubuntu에서 설치된 pyodbc는 현재 .callproc 메서드가 존재하지 않습니다. 관련해서 pyodbc에서 procedure를 호출하는 방법을 알아보도록 합니다.

Table Of Contents

• 기본 Procedure 사용방법
• 1. 인자없는 프로시저 호출
• 2. 입력 인자만 있는 프로시저 호출
• 3. 프로시저 배치로 실행하기
• 4. 출력(output) 인자 처리하기

기본 Procedure 사용방법

ODBC는 프로시저 호출과 관련하여 ODBC 드라이버에서 지원해야 하는 {CALL ...}, ODBC CALL escape clause가 존재합니다. 이 내용은 MS SQL Server spec에 존재합니다. 더 자세한 내용은 아래 링크를 확인해 주세요.

ODBC CALL escape clause 보러 가기

pyodbc는 이 ODBC CALL escape clause를 지원합니다.

그러므로, 프로시저는 이 구문을 통해 pyodbc에서 사용할 수 있습니다.

1. 인자없는 프로시저 호출

매개 변수를 받지 않는 "usp_NoParameters"라는 저장 프로시저를 호출하려면 다음을 수행할 수 있습니다.

crsr.execute("{CALL sp_NoParameter}")

 

2. 입력 인자만 있는 프로시저 호출

입력 매개 변수만 받는 저장 프로시저를 호출하려면 다음과 같이 수행할 수 있습니다.

params = (14, "test")
crsr.execute("{CALL sp_UpdateFirstName (?,?)}", params)

 

3. 프로시저 배치로 실행하기

pyodbc는 executemany라는 메서드를 통해서 배치 작업을 지원합니다. 이 때 DBMS에 한번의 네트워크로 전송하는 array인터페이스를 사용하기 위해서는 cursor의 fast_executemany 속성을 True로 설정해 주어야 합니다.

간단한 테스트를 위해 아래와 같이 INTEGER를 입력으로 받아서 출력하는 프로시저를 생성합니다.

CREATE OR REPLACE PROCEDURE PROC_PRINT (P1 IN INTEGER) AS
BEGIN
  PRINTLN('IN PARAMETER ' || P1);
  insert into t1 values( p1 );
END;
/

 

실행결과는 아래와 같습니다.

iSQL> create table t1(i1 integer);
Create success.
iSQL> CREATE OR REPLACE PROCEDURE PROC_PRINT (P1 IN INTEGER) AS
    2 BEGIN
    3   PRINTLN('IN PARAMETER ' || P1);
    4   insert into t1 values( p1 );
    5 END;
    6 /
Create success.

iSQL> EXEC PROC_PRINT(45);
IN PARAMETER 45
Execute success.
iSQL> select * from t1;
I1
--------------
45
1 row selected.

 

이를 배치로 실행하는 것은 아래 코드처럼 작성하면 됩니다.

import pyodbc

# Altibase 연결 문자열을 설정합니다.
conn_str = (
    "DSN=altibase-dsn;"
    "UID=sys;"
    "PWD=manager;"
)

pyodbc.pooling = False
# Altibase에 연결합니다.
conn = pyodbc.connect(conn_str)

# 커서를 생성합니다.
cursor = conn.cursor()

array_data = [(1,), (2,), (3,), (4,), (5,)]

cursor.fast_executemany = True
cursor.executemany('{call PROC_PRINT(?)}', array_data)
conn.commit()

cursor.execute('select * from t1')
rows = cursor.fetchall()
print(f"Result from PROC_PRINT:")

# 결과를 출력합니다.
for row in rows:
    print(row)

# 연결을 닫습니다.
conn.close()

 

위 코드를 실행하면 다음과 같습니다.

% python3 test.py
Result from PROC_PRINT:
(1,)
(2,)
(3,)
(4,)
(5,)

 

4. 출력(output) 인자 처리하기

pyodbc에는 .callproc이 없으므로 출력 매개변수 및 반환 값의 값을 검색하기 위한 해결 방법을 사용해야 합니다. 구체적인 방법은 특정 ODBC 드라이버가 지원하는 기능에 따라 다르지만, SQL Server나 Oracle의 ODBC 드라이버의 경우 "익명 코드 블록"을 사용하여 저장 프로시저를 실행한 다음 출력 매개 변수 및/또는 반환 값을 SELECT할 수 있습니다. 예를 들어 다음과 같이 작성할 수 있습니다.

sql = """\
DECLARE
    v_out INTEGER;
BEGIN
    -- 프로시저 호출
    PROC_PRINT(?, v_out);
    -- 결과 출력
    SELECT v_out as the_output;
END;
"""
cursor.executemany(sql, array_data)
rows = cursor.fetchall()
print(f"Result from PROC_PRINT:")

# 결과를 출력합니다.
for row in rows:
    print(row)

 

하지만, Altibse의 경우에는 익명(anonymous) 프로시저 블록에서 SELECT 구문에 INTO가 없는 경우 에러가 발생하여 처리가 불가능한 것으로 확인되었습니다.