[PYTHON] pymssql 라이브러리

- 이 글은 제가 공부를 하며, 이해한 것을 바탕으로 작성하는 글입니다.

- 그렇기에 틀리거나 잘못된 부분이 있을 수 있습니다.

- 글의 오류를 발견하시면, 댓글로 말씀해 주시면 정말 감사하겠습니다.

 

---

python을 활용하여, MS-SQL DB를 다루는 방법을 공유하고자 합니다. DB에 접속하여, SELECT 하는 구문을 사용해 봅시다.

 

[1] 사용하는 라이브러리

사용하는 라이브러리는 PYMSSQL으로, 매우 간단한 코드로 접근이 가능합니다.

이 라이브러리에 대한 설명은 아래 첨부된, 링크를 확인하시면 되겠습니다.

 

PYMSSQL Docs 바로가기

 

설치는 pip로 라이브러리만 설치하면 됩니다.

pip install pymssql

 

[2] 전체 코드

아래의 코드는 MS-SQL DB에 접근하여 쿼리를 입력하여 실행시키고, 그 결과를 Pandas 라이브러리를 이용하여 데이터 프레임으로 변환하는 코드이다. 코드마다 주석을 달아 놓았고, 자세한 설명은 [3] 번 항목을 참조하면 된다.

 

import pymssql
import pandas as pd
import datetime as dt

#아래는 DB 접속 정보이다.
host = 'qa-sql-prdb.test.com'
id ='test_id'
pw = '12345'
db_name = 'test_tbl'

#connect 메소드를 활용하여, DB에 접속한다.
conn = pymssql.connect(host=host, user = id, password = pw, database = db_name, charset = 'utf8')

#어제, 오늘 날짜
today_date = dt.datetime.today().strftime("%Y-%m-%d")
yesterday_date = (dt.datetime.today() - dt.timedelta(days=1)).strftime("%Y-%m-%d")

# 접속한 DB에 켜서를 만든다.
cursor = conn.cursor()

# 내가 원하는 SQL문을 작성한다.
# WHERE절의 %s로 변수를 넣어준다. 
sql ="""
    SELECT
    T1.UNO,
    T1.name,
    T1.gender,
    T1.birth,
    T1.email,
    T2.RegDate
    FROM customer_history T1
    LEFT OUTER JOIN customer_info T2 ON T1.UNO = T2.UNO
    WHERE T1.RegDate > %s AND T1.RegDate > %s  T1.CI IS NOT NULL                   
    ORDER BY T2.RegDate"""
    
#위에서 만든 변수들을 튜플에 순서대로 넣어준다. 
date_lst = (yesterday_date, today_date)

#SQL을 실행한다.
#parameter로 sql, 변수값 순으로 넣어준다.
curs.execute(sql, date_lst)

#쿼리 결과 행을 모두 가지고 온다.
rows = curs.fetchall()

#쿼리 결과를 df로 변환한다.
col=['uno','name','sex','birth','E-mail','regdate']
df =pd.DataFrame(rows, columns=col)

#접속을 종료한다. 
conn.close()

 

[3] 설명

위의 코드를 나누어 설명하도록 하겠습니다.

 

1. MS-SQL DB에 접근하는 방법

2. 쿼리를 삽입하여 동작시키는 방법

3. 쿼리로 불러온 결과를 Pandas 라이브러리를 이용하여 데이터 프레임으로 변환

 

 

1. MS-SQL DB에 접근하는 방법

host = 'qa-sql-prdb.test.com'
id ='test_id'
pw = '12345'
db_name = 'test_tbl'
conn = pymssql.connect(host=host, user=id, password=pw, database=db_name, charset='utf8')

 

connection 메서드는 아래와 같은 Parameter를 가지고 있습니다. 필요한 부분에 맞게 사용하시면 되겠습니다.

Parameter	내용	type
server	database host	String
user	database에 접속하는 user id	String
password	비밀번호	String
timeout	쿼리 실행 후, 결과 timeout 시간으로, 디폴트 값은 0초(무제한 대기)입니다.	Int
login_timeout	DB 접근, 로그인 timeout 시간으로 디폴트 값은 60초입니다.	Int
charset	인코딩	String
as_dict	디폴트로 쿼리의 결과는 행이 tuple로 반환되는데, 이를 dictionary로 변경할 것인지에 대한 파라미터 입니다.	boolean
appname	DB연결에 사용되는 프로그램이름을 말합니다.	String
port	서버에 접속할 때 사용하는 포트번호	String

 

-TIP(ReadOnly 옵션 없음)

이 pymssql 라이브러리는 Readonly 옵션이 없습니다. 그렇기에 이 옵션을 사용하려면, odbc를 이용하는 pyodbc 라이브러리를 사용하셔야 합니다. 

기본적인 사용방법은 거의 동일하나, 라이브러리 설치 시 애로 사항이 많습니다. 저도 이 라이브러리를 윈도와 우분투에 설치하느라 많이 애를 먹었었는데요. 차후 정리하여 업로드하도록 해 보겠습니다.

 

2. 쿼리를 삽입하여 동작시키는 방법

cursor = conn.cursor()

여기서 가장 중요한 것은 바로 cursor라는 것입니다.

원문의 설명을 보도록 하겠습니다. 

 

This class represents a Cursor (in terms of Python DB-API specs) that is used to make queries against the database and obtaining results. You create instances by calling cursor() method on an open Connection connection object.

 

이것은 쿼리를 만들고 데이터베이스에서 결과를 가져오는 데 사용되는 놈이라고 보시면 됩니다. DB를 연결 후, 반드시 이후의 작업을 위해 필요한 것으로 보면 될 것 같습니다. 

 

아래는 자주 사용하는 함수입니다. 

 

- execute

실행시킬 쿼리를 입력하고 실행하는 함수입니다. 가장 간단한 형태는 다음과 같습니다.

cursor.execute( SELECT * FROM tbl1 WHERE event_date = '2022-01-11' AND get _date = '2022-01-10';)

 

- TIP1(쿼리를 변수에 넣자)

좀 더 복잡하고 긴 쿼리를 작성할 때에는 아래와 같이 변수에 쿼리를 문자열로 할당하여, 처리하는 게 좋습니다. 

sql='''
SELECT * FROM tbl1 WHERE event_date = '2022-01-11' AND get_date='2022-01-10';)
'''

cursor.execute(sql)

 

- TIP2(쿼리내부에 변수를 넣는 방법)

쿼리문 내에 변수를 넣어야하는 경우가 존재합니다. 이럴 때에는 어떻게 해야 할까요? 쿼리문은 현재 문자열로 되어있는데, 이 내부에 어떻게 변수를 삽입할까요?

 

아래의 예시를 보도록 하겠습니다.

매일 쿼리를 하루에 한 번씩, event_date와 get_date를 각각 오늘 날짜와 어제 날짜를 넣고 쿼리를 돌려야 한다고 생각해 봅시다. 그렇다면, 위에 있는 쿼리에 있는 2022-01-11, 2022-01-10 이라는 이 두 날짜를 매일 수정해 주어야 할 것입니다. 이를 아래와 같이 처리하도록 합니다. 

 

우선 쿼리에서 이 변수들이 들어갈 항목에 %s을 넣습니다. 그런 다음 datetime library를 이용하여, 어제와 오늘 날짜를 가지고 오는 변수를 만듭니다. (today_date, yesterday_date) 그리고, 이것들을 튜플로 묶어 주고, 해당 튜플 변수를 execute parameter로 넣어줍니다. 

 

# 오늘, 어제 날짜를 가지고 온다.
today_date = dt.datetime.today().strftime("%Y-%m-%d")
yesterday_date = (dt.datetime.today() - dt.timedelta(days=1)).strftime("%Y-%m-%d")


sql='''
SELECT * FROM tbl1 WHERE event_date = %s AND get_date = %s;)
'''

date_lst = (yesterday_date, today_date)

curs.execute(sql, date_lst)

 

문자열이 아닌, 정수, 실수 등을 변수로 쿼리를 넣을 때에는 %s가 아닌 아래의 표를 참조하시면 되겠습니다.

종류	데이터 타입	내용
%s	문자열	String
%c	문자	Character
%d	정수	Demical
%f	실수	Float
%g	정수, 실수	정수, 실수 자동 표현(소수점의 여부로)

 

 

- fetchone

실행시킨 쿼리의 한 row(행)를 불러오는 함수입니다. 아래는 fetchone을 이용한 모든 행을 불러오는 코드입니다. 

cursor.execute('SELECT UNO, name, age FROM tbl1;')  

#한 행만 불러오기
row = cursor.fetchone() 

# 행이 끝날 때 까지, print 
while row:  
    print("UNO 컬럼의 값 = "str(row[0]) + "name 컬럼의 값 = " + str(row[1]) + "age 컬럼의 값 = " + str(row[2])     
    row = cursor.fetchone()

 

- fetchall

쿼리의 결과를 모두 불러올 때에는 fetchone보다 이 함수를 사용하는 것이 훨씬 깔끔합니다. 

curs.execute(sql, date_lst)

#쿼리 결과 행을 모두 가지고 온다.
rows = curs.fetchall()

 

아래는 실행시킨 쿼리를 모두 불러와 pandas 라이브러리의 DataFrame으로 넘기는 코드입니다.

curs.execute(sql, date_lst)

#쿼리 결과 행을 모두 가지고 온다.
rows = curs.fetchall()

# DataFrame의 컬럼 명을 지정 해 준다.
col=['uno','name','sex','birth','E-mail','regdate']
#쿼리 결과를 df로 변환한다.
df =pd.DataFrame(rows, columns=col)

 

감사합니다.