SQL | Stored Procedure 및 Python 연결

2023-02-19 Comments

SQL에서 사용되는 스토어드 프로시져에 대해 정리하는 글입니다. 추가로 파이썬도 사용해보아요.

데이터베이스와 SQL
SQL 미리 맛보기
SQL 기본 문법
SQL 고급 문법
테이블과 뷰
인덱스
스토어드 프로시져 ⬅
SQL과 파이썬 연결 ⬅

스토어드 프로시저^{Stored procedure}

스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해 사용할 수 있습니다.

기본 구조는 다음과 같습니다.

DELIMITER $$
CREATE PROCEDURE Stored_Procedure_Name( IN 혹은 OUT 파라미터)
BEGIN
    -- SQL 프로그래밍 기입
END $$ 
DELIMITER ;

CALL Stored_Procedure_Name(); -- 호출

스토어드 프로시저 삭제 DROP PROCEDURE Stored_Procedure_Name;
Input 파라미터 지정 형식: IN 입력_매개변수_이름 데이터_형식
파라미터 입력: CALL 프로시저이름(전달_값);
Output 파라미터 지정 형식: OUT 출력_매개변수_이름 데이터_형식
변수 지정: CALL 프로시저이름(@변수_명);
변수 출력: SELECT @변수_명;
날짜와 관련된 함수
- YEAR, MONTH, DAY는 날짜 데이터 타입에서 연, 월, 일을 구해줍니다.
- CURDATE() 는 현재 날짜를 알려줍니다. YEAR, MONTH, DAY를 CURDATE와 같이 사용하면 현재 연, 월, 일을 얻을 수 있습니다.
동적 SQL은 SQL을 생성하고 PREPARE과 EXECUTE로 사용합니다

스토어드 함수^{Stored function}

내장 함수 외에 직접 함수를 만들 수 있습니다.
스토어드 프로시저와 비슷하지만, 용도가 다르고 RETURNS 예약어로 값을 반환합니다

기본 형식은 다음과 같습니다

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(파라미터)
    RETURNS 반환형식
BEGIN
    -- SQL 프로그래밍
    RETURN 반환값;
END $$
DELIMITER ;

SELECT 스토어드_함수_이름();

CREATE FUNCTION 하위의 RETURNS에서 반환 형식을 지정합니다.
파라미터는 모두 인풋입니다. IN을 따로 붙이지 않습니다.
SELECT로 호출합니다.
함수 내에서 SELECT를 사용할 수 없습니다.

예시

데뷔 연도를 입력하면 활동기간이 얼마나 되었는지 출력해주는 함수입니다.

DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    -- SQL 프로그래밍
    DECLARE runYear INT;
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

사용 예시

그대로 출력할 수도 있고, SELECT ~ INTO 를 이용해 변수에 저장해 사용할 수도 있습니다.

SELECT calcYearFunc(2010) AS '활동 햇수';

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이';

삭제할 때에는 DROP FUNCTION 함수이름;을 사용합니다.

커서^cursor

커서는 테이블에서 한 row씩 처리하기 위한 방식입니다.
첫 번째 행부터 마지막 행까지 하나씩 접근해서 값을 처리합니다.

실습

회원의 평균 인원수를 구하는 스토어드 프로시저를 만들어 실습해보겠습니다.
실습을 위해 커서로 한 row씩 접근해서 인원수를 누적시켜봅니다.

1. 사용할 변수 준비하기

DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

endOfRow는 row의 끝을 확인하기 위한 변수입니다.

2. 커서 선언하기

1 2	DECLARE memberCursor CURSOR FOR SELECT mem_number FROM member;

커서라는 것은 결국 SELECT문입니다.

3. 반복 조건 선언하기

1 2	DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;

row가 끝까지 가면 반복을 종료할 수 있도록 조건을 성정합니다.

4. 커서 열기

1	OPEN memberCursor;

5. 행 반복하기

cursor_loop: LOOP
    FETCH memberCursor INTO memNumber;

    IF endOfRow THEN
        LEAVE cursor_loop;
    END IF;

    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

FETCH는 한 row씩 읽습니다.
LEAVE는 반복을 빠져나갑니다.

데이터 확인하기

1	SELECT (totNumner/cnt) AS '회원의 평균 인원 수';

6. 커서 닫기

1	CLOSE memberCursor;

트리거^Trigger

DML^{Data manipulation language}문 (INSERT, UPDATE, DELETE)이 발생하면 자동으로 실행되는 프로그래밍 기능입니다.
테이블에 미리 부착 (attach) 해놓으면 작동하는 코드입니다.
BEFORE 트리거와 AFTER 트리거로 나뉩니다. 보통은 AFTER를 사용하므로, 여기서는 AFTER에 대해서만 설명합니다.

그러면 실습을 해보겠습니다.

테이블 만들기

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

CREATE TABLE backup_singer
	(	mem_id		CHAR(8) NOT NULL,
		mem_name	VARCHAR(10) NOT NULL,
		mem_number	INT NOT NULL,
        addr		CHAR(2) NOT NULL,
        modType		CHAR(2),
        modDate		DATE,
        modUser		VARCHAR(30)
	);

CREATE TABLE backup_singer
	(	mem_id		CHAR(8) NOT NULL,
		mem_name	VARCHAR(10) NOT NULL,
		mem_number	INT NOT NULL,
        addr		CHAR(2) NOT NULL,
        modType		CHAR(2),
        modDate		DATE,
        modUser		VARCHAR(30)
	);

backup_singer 테이블은 처음에는 row가 존재하지 않습니다.

트리거 작성하기

singer 테이블에 Update와 Delete가 발생하면 backup_singer 테이블에 수정 혹은 삭제로 로그를 남기게 됩니다.

-- UPDATE 트리거
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg
	AFTER UPDATE
    ON singer
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name,
		OLD.mem_number, OLD.addr, "수정", CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;

-- DELETE 트리거
DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg
	AFTER DELETE
    ON singer
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name,
		OLD.mem_number, OLD.addr, "삭제", CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;

테이블 데이터 변경하기

그룹이 BLK 멤버의 주소지를 영국으로 바꾸었습니다.
멤버가 7 이상인 그룹을 삭제하였습니다.

1 2	UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK'; DELETE FROM singer WHERE mem_number >= 7;

⭐️참고 - Safe mode 해제하기.
여러 행을 바꿀 때, safe mode가 활성화되어 있다면 아래의 문구가 뜹니다.
1
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
이를 해제하려면 다음의 Query를 실행해주면 됩니다.
1
set SQL_SAFE_UPDATES = 0;

두 테이블의 결과를 확인해봅시다.

1 2	SELECT * FROM singer; SELECT * FROM backup_singer;

singer 테이블

backup_singer 테이블

singer 테이블의 데이터를 성공적으로 변경하고 삭제하였습니다. 트리거에 지정한대로 backup_singer 테이블에도 기록이 남는 것을 확인하였습니다

SQL과 Python 연결하기

Python은 유명한 프로그래밍 언어이고 과학, 분석, 개발 등에서 사용됩니다. (정보는 너무나 많으므로 언어에 대한 별도의 설명은 하지 않겠습니다)
python의 pymysql 라이브러리를 이용해봅니다.

DB 연결하기

import pymysql
conn = pymysql.connect(host = "127.0.0.1",
                       user = "root",
                       password = password,
                       db = "soloDB",
                       charset = "utf8")

커서 생성하기

연결된 데이터베이스에 SQL문을 실행하고 결과를 받으려면 커서가 필요합니다. 커서는 다음처럼 정의가능합니다.

1	cur = conn.cursor()

데이터 조회하기

cur.execute("SELECT * FROM soloDB.userTable") # 성공하면 1을 Return합니다
result = cur.fetchall() # execute의 결과를 반환합니다.
print(result)
# (('내 ID', '내 이름', '이메일@naver.com', 1991),)

이런식으로, cur.execute를 이용하면 SQL문을 전송하면서 지금까지 배워왔던 SQL 명령이 모두 가능합니다.

SQL과 GUI 연동하기

pymysql과 tkinter를 이용해 GUI프로그램을 만들고 mysql과 연동하여 입력,조회를 해보았습니다.
코드는 길어질 수 있으므로, 다음의 강의를 참고해주세요.

실습 결과

오늘은 스토어드 프로시저와 Python과 연결에 대해 알아보았습니다.

읽어주셔서 감사합니다 👋

SQL | Stored Procedure 및 Python 연결

스토어드 프로시저Stored procedure

스토어드 함수Stored function

커서cursor

트리거Trigger