[pymssql] 프로시저 호출 테스트

mssql DB에 있는 프로시저를 호출하여 결과값에 따른 처리를 해줘야했다.
하여 기능테스트를 진행하려고 한다.

테이블 생성

간단한 사용자 테이블을 만들 예정이다.

CREATE TABLE CM_USER(
    USER_NO INT IDENTITY(1,1) PRIMARY KEY,
    USER_ID VARCHAR(30) NOT NULL,
    USER_PW VARCHAR(30) NOT NULL,
    USER_NAME VARCHAR(30) NOT NULL,
    DEPT_CD VARCHAR(10) DEFAULT ''
)

데이터를 몇개 넣어보자. 대략 이렇게 넣었다.

USER_NO	USER_ID	USER_PW	USER_NAME	DEPT_CD
1	LEE_LEE	1234	사용자_1	001
2	KIM_KIM	5677	사용자_2	002
3	PARK_PARK	0000	사용자_3	003

프로시저를 생성

mssql의 경우 리턴값이 숫자만 가능하고 output으로 쓸 변수를 넣어야 다른 값으로 받을수 있다 고 한다.(아니면 댓글좀)

숫자로 받을경우 RETURN(1) 이렇게 해주면 된다.

CREATE PROCEDURE USER_SET
    @user_id varchar(30),
    @user_pw varchar(10),
    @result varchar(1) output
AS
BEGIN
    IF EXISTS(
    	SELECT * FROM CM_USER WHERE USER_ID = @user_id AND USER_PW = @user_pw
    )
        BEGIN
            SET @result = 'Y'
        END
    ELSE
    	BEGIN
            SET @result = 'N'
        END
    RETURN;
END

프로시저 호출 테스트

--mssql은 프로시저를 아래와 같이 호출한다.
EXEC [프로시저명] '파라미터1','파라미터2' 
-- 괄호가 없어서 처음엔 굉장히 헷갈린다.

EXEC USER_SET 'LEE_LEE','1234'

이렇게 호출하게 되면 

아래와 같은 창을 마주하게된다. 

0.0 (결과가 없다!!) (dbeaver)

이럴때는 output으로 사용될 변수를 지정해주고 SELECT까지 해줘야한다.

파라미터로 넣어줄때는 output이란 문구는 없어도 될거다.(아니면 삭제예정)

DECLARE @result varchar(1);
EXEC USER_SET 'LEE_LEE', '1234', @result output;
SELECT @result;

이제야 결과를 확인할 수 있다.

flask+sqlalchemy+pymssql 사용

아래와 같이 예제 코드를 작성할 수 있다.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.pool import NullPool

engine = create_engine("mssql+pymssql://{id}:{pw}@{ip}:{port}/{db}", poolclass=NullPool)
Session = scoped_session(sessionmaker(bind=engine))

session = Session()

_sql = """
    DECLARE @result varchar(1);
    EXEC USER_SET :userId, :userPw, @result output;
    SELECT @result;
"""

_param = {
    "userId": "LEE_LEE",
    "userPw": "1234"
}

result = session.execute(_sql, _param).one() # 프로시저일때는 .one()으로 호출하자
#.all()도 가능하지만 결과가 [('','N')] 이렇게 배열에 들어있게된다.

login_result = result['']

print(login_result) # -> 'Y'가 출력된다.

끗!