MS-SQL Cursor 사용하기

SQL 작업을 하다보면 조회된 쿼리 결과에 대해서 행 단위 작업이 필요할때가 있다. 이때, Cursor를 사용하면 효율적으로 처리 할 수 있다.

Cursor Command

  • DECLARE: Cursor에 관련된 선언을 하는 명령
  • OPEN: Cursor가 Query결과의 첫번째 Tuple을 포인트로 하도록 설정하는 명령
  • FETCH: Query 결과의 Tuple들 중 현재의 다음 Tuple로 커서를 이동시키는 명령
  • CLOSE: Query 수행을 모두 마친 후 Cursor를 닫기 위한 명령
  • DEALLOCATE: Close된 Cursor의 자원을 반환하는 명령

Cursor life cycle

Cursor Example

DECLARE @pColum_1 NVARCHAR(100), @pColum_2 INT, @pColum_3 BIT
DEClARE pCursor CURSOR
FOR
   select * from [Target Table]

OPEN pCursor

FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3

WHILE(@@FETCH_STATUS <> -1)
   BEGIN
      update [Target Table] SET Colum = @Colum_1 + 1
      where Colum_2 = @pColum_2 and Colum_3 = @pColum_3

      FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3
   END

CLOSE pCursor
DEALLOCATE pCursor

-유의사항-

Cursor를 사용하게 되면 내부적인 임시테이블을 사용하기 때문에 많이 사용하게 되면 DB성능에 영향을 미치게 된다. 되도록 사용량이 적은 시간때이거나 적은양의 데이터를 처리할 때 사용하는 것이 바람직 하다.

Python connect to MS-SQL with pymssql

Python에서 MS-SQL을 사용하려면 일단, MS-SQL DB를 지원하는 Python 모듈을 설치해야 한다. MS-SQL에 대한 모듈은 크게 pyodbc와 pymssql 두가지가 있는데 여기서는 pymssql을 사용 할 것이다.

(DB에 관련된 모듈은 무수히 많은 것들이 있다. 참고: https://www.lfd.uci.edu/~gohlke/pythonlibs/)

다음과 같이 pymssql을 설치한다.

(설치 방법은 Ubuntu Linux대상 이다. 다른 OS를 사용하는 경우는 여기를 참고하면 된다.)

$ apt-get --assume-yes update
$ apt-get --assume-yes install freetds-dev freetds-bin
$ pip install pymssql

이제 Python에서 설치한 모듈을 가지고 MS-SQL에 접근해 보자.

import json
import pymssql 

#[Tip]json 문자열을 환경변수 파일로 저장하여 사용한다면 서버정보를 노출 하지 않을 수 있다.
json_string = 
'{
    "host":"Server Address",
    "port":1433,
    "user":"User ID@Server Name",
    "password":"P@ssW0rd",
    "database":"Database Name"
}'
json_data = json.load(json_string)

#pymssql 모듈을 이용하여 Connection 생성을 한다.
conn = pymssql.connect(host=json_data['host'], port=json_data['port'], user=json_data['user'], password=json_data['password'], database=json_data['database'])

여기까지 하면 MS-SQL서버에 접근(Connection 완료) 한 것이다.
다음으로 Select query문을 실행하여 데이터를 가져와 보자.

#Connection으로부터 cursor 생성
cursor = conn.cursor() 

#Select Query 실행
cursor.execute('select * from [Target Table Name]') 

#결과 데이터를 데이터프레임에 저장
df = pd.DataFrame(cursor.fetchall())

#실행이 끝나면 항상 연결 객체를 닫아 주어야 한다.
conn.close() 

잘 실행 되었다면 결과를 담은 데이터프레임을 출력 해보자.

#결과 출력
print(df)

실행한 Select Query한 결과를 확인 할 수 있을 것이다.

MS-SQL Table Copy

학습 데이터를 준비 하려다 보면 기존의 데이터를 변형해야 될 때가 있다. 이때 원본 테이블을 가지고 바로 작업을 하게 되면 데이터가 손실 될 수 있다.
백업 데이터가 있다고 하면 그나마 다행이지만 보통 학습에 사용되는 데이터는 양이 많기 때문에 100% 복구하려면 시간이 걸린다.
이런 점들 때문에 될 수 있으면 테이블(혹은 데이터)을 복사해서 사용하면 좋다.

  1. 새 테이블을 생성 하면서 데이터 복사
select * into [New Table Name] from [Source Data]
  1. 테이블 구조만 복사
select * into [New Table Name] from [Source Table Name] where 1=2
  1. 기존 테이블에 데이터만 복사
insert into [Destination Table Name] select * from [Source Table Name]

MS-SQL Select random rows from Table

머신러닝 혹은 딥 러닝 학습을 하기 위해서는 우선 학습할 데이터를 탐색해 보아야 한다. 만약, 많은 양의 Row 데이터를 가지고 있는 SQL Table을 가지고 해야 한다면 일부 표본만 추출해서 테스트를 해보는 것이 필요하다.

이런 경우 테이블에서 랜덤으로 데이터를 추출할 때 다음 Query를 사용하면, 간단하게 일정 비율로 랜덤한 데이터를 추출해 낼 수 있다.

select top 10 percent * from [Table Name] order by newid()