[Py2.7] pymssql 패키지 설치 및 SQL Server 데이터 가져오기

/*******************************************************************************************************
-- Title : [Py2.7] pymssql 패키지 설치 및 SQL Server 데이터 가져오기
-- Reference : 웹검색
-- Key word : pymssql package 패키지 설치 install pip pysql
*********************************************************************************************************/

■ Python을 사용하여 SQL 데이터베이스에 연결
   ㅇ"https://azure.microsoft.com/ko-kr/documentation/articles/sql-database-develop-python-simple/"

■ pymssql 드라이버 다운로드
   ㅇpymssql-2.1.3-cp27-cp27m-win_amd64.whl <or> pymssql‑2.1.3‑cp34‑cp34m‑win_amd64.whl

   ㅇ"http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql"

■ pymssql 드라이버 실행
   ㅇpython.exe가 있는 폴더에 드라이버 파일 이동
   ㅇ"C:\Anaconda2> pip install pymssql-2.1.3-cp27-cp27m-win_amd64.whl" 실행
   ㅇSuccessfullu installed pymssql-2.1.3 메시지 떠야 함

■ pip 업그레이드
   ㅇYou are using pip version 8.1.1, however version 8.1.2 is available.
     메시지가 뜨면 pip 업그레이드 해야 함
   ㅇ"C:\Anaconda2> python -m pip install --upgrade pip" 실행
   ㅇ필요시 pymssql 삭제 : "pip uninstall pymssql-2.1.3-cp27-cp27m-win_amd64.whl" 실행

■ Python 3.4

# -*- coding: utf-8 -*-
 
import os, sys, string
import pandas as pd
import pymssql
 
# ------------------------------
# -- Connection 설정
# ------------------------------
conn = pymssql.connect('localhost', 'usr_python', 'vkdlTjs', 'mopdb', as_dict=True) # as_dict : fetch['컬럼명']로 사용
#conn = pymssql.connect('localhost', 'usr_python', 'vkdlTjs', 'mopdb')              # as_dict 없을 땐 : fetch[0]로 사용
cur = conn.cursor()
 
 
# ------------------------------
# -- 하나씩 fetch
# ------------------------------
print ("[fetch one]", "-" * 100)
 
cur.execute("select skey, inv_ti from dbo.inv_ti with (nolock) where skey in (1503201000441, 1503201000645) and lang_cd = 'us';")
fetch = cur.fetchone()
 
while fetch:
    print ("ID=%d, TI=%s" % (fetch['skey'], fetch['inv_ti']))
    fetch = cur.fetchone()
 
 
# ------------------------------
# -- 한꺼번에 fetch
# ------------------------------
cur.execute("select skey, inv_ti from dbo.inv_ti with (nolock) where skey in (1503201000441, 1503201000645) and lang_cd = 'us';")
fetch2 = cur.fetchall()
 
print ("[fetch all]", "-" * 100)
 
for i in fetch2:
    print (i)
 
 
# ------------------------------
# -- 프로시저 호출
# ------------------------------
print ("[procedure call]", "-" * 100)
 
#cur = conn.cursor(as_dict=True)
cur.execute('up_ttt @skey=%d', 1503201000441)
 
fetch = cur.fetchall()
 
for i in fetch:
    print (str(i["skey"]) + "    " + i["inv_ti"])
 
 
# ------------------------------
# -- Close connection
# ------------------------------
conn.close()

 

■ Python 2.7

# -*- coding: utf-8 -*- import os, sys, string import pandas as pd import pymssql # -- Ad-Hoc 쿼리 conn = pymssql.connect ('servername', 'username', 'password', 'dbname') # fetch[0]처럼 인덱스 사용 # conn = pymssql.connect ('servername', 'username', 'password', 'dbname', as_dict=True) # fetch['skey']처럼 컬럼명 사용. cur = conn.cursor() cur.execute("/*ttt*/select skey, inv_ti from ttt with (nolock) where skey in (4500001000004, 4500001000002) and lang_cd = 'kr';") fetch = cur.fetchone() # -- 하나씩 fetch while fetch: print "ID=%d, NAME=%s" % (fetch[0], fetch[1]) fetch = cur.fetchone() # -- 한꺼번에 fetch cur.execute("/*ttt*/select skey, inv_ti from ttt with (nolock) where skey in (4500001000004, 4500001000002) and lang_cd = 'kr';") fetch2 = cur.fetchall() for i in fetch2: print i # -- 프로시저 호출 cur = conn.cursor(as_dict=True) cur.execute('up_ttt @skey=%d',1503201000001) fetch = cur.fetchall() for i in fetch: print str(i["skey"]) + " " + i["inv_ti"] # -- Close connection conn.close()