[Python] MSSQL 연동 (Feat. pymssql)

http://www.pymssql.org/en/stable/pymssql_examples.html

pymssql examples — pymssql 2.1.4 documentation

위의 공식사이트에서 기본적인 사용예제 및 추가적인 부분 확인하시면 됩니다!!

 

pymssql 설치

 - cmd 창이나, 아나콘다 powershell 프롬프트에서 아래의 명령어를 입력하여 pymssql 설치
   ※ cmd에서 사용할려면, pip설치하는 부분을 확인하여 설치 후, 사용가능합니다..

pip install pymssql

 

pymssql 사용

pymssql 연결

import pymssql

server = '192.168.0.2'
database = 'DZ'
username = 'sa'
password = 'pwd'

#############################################################################
# MSSQL 접속
conn = pymssql.connect(server, username, password, database) 
# auto commit 을 사용할 경우 : conn.autocommit(True)
cursor = conn.cursor()

- MSSQL 정보

 

SELECT 문

#############################################################################
# SELECT
cursor.execute('SELECT * FROM tblTest1;')

row = cursor.fetchone()
while row:
    #print(row[0], row[1].encode('ISO-8859-1').decode('euc-kr'))
    print(row[0], row[1], row[2], row[3], row[4])
    row = cursor.fetchone()

 - 결과 값 

#############################################################################
# SELECT <결과 값 리스트로 반환>
cursor.execute('SELECT * FROM tblTest1;')

list = cursor.fetchall()
print(list)

 - 결과 값

 

INSERT 문

#############################################################################
# INSERT
query = "INSERT INTO tblTest1 (col1, col2, col3, col4, col5) VALUES ('6', '66', '666', '6666', '66666')"
cursor.execute(query)
conn.commit()

#############################################################################
# INSERT <여러개 한번에 입력>
query = "INSERT INTO tblTest1 (col1, col2, col3, col4, col5) VALUES ('6', '66', '666', '6666', '66666')"
cursor.executemany(
    "INSERT INTO tblTest1 values (%s, %s, %s, %s, %s)",
    [('11', 't11', 't111', '', ''),
    ('22', 't22', 't222', '', ''),
    ('33', 't33', 't333', '', '')]
)
conn.commit()

 

UPDATE 문

#############################################################################
# UPDATE
data = '헬로우 월드 !!'
query = "UPDATE tblTest1 set col5 = '" + str(data) + "'  where col1 = '6'"
cursor.execute(query)
conn.commit()

 

DELETE 문

#############################################################################
# DELETE
data = '헬로우 월드 !!'
query = "DELETE FROM tblTest1 WHERE col5 = '" + str(data) + "'" 
cursor.execute(query)
conn.commit()

 

PROCEDURE 호출

#############################################################################
# PROCEDURE CALL
cursor.callproc('PRC_TEST1', ('9', '99'))
conn.commit()

 - "PRC_TEST1" 프로시저

USE [DZ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PRC_TEST1] 
	@P_COL1 AS NVARCHAR(10)
	, @P_COL2 AS NVARCHAR(10)
AS
BEGIN
	SET NOCOUNT ON;

    INSERT INTO tblTest1 (col1, col2, col3, col4, col5) VALUES (@P_COL1, @P_COL2, '', '', '')
END

 

연결 끊기

conn.close()