Python pymssql

[Python/MSSQL] pymssql로 DB insert, 프로시저 호출 방법

by

안녕하세요. 오늘은 파이썬으로 데이터 분석을 한 후 MSSQL에 정의되어 있는 프로시저를 호출하거나 직접 DB insert를 실행하는 방법에 대해 소개하겠습니다.

DB 연결하기

DB를 읽어오거나 프로시저를 호출하기 위해서는 타겟 DB에 먼저 연결을 해주어야 해요. pandas와 pymssql를 import해줍니다.
server와 database 이름, username 및 password를 입력한 후 .connect()에 변수로 입력하여 연결해줍니다. 한글이 포함된 DB를 읽어오실 때, charset = 'CP949' 변수를 추가해주세요.

import pandas as pd
import pymssql

server = '#TYPE_YOUR_SERVER'
database = '#TYPE_YOUR_DATABASE'
username = '#TYPE_YOUR_USERNAME' 
password = '#TYPE_YOUR_PASSWORD'

# DB 연결
cnxn = pymssql.connect(server, username, password, database, charset = 'CP949')

그럼 이제 query 작성을 통해 DB를 읽어올 수 있습니다. 저는 test 테이블의 100개 row를 읽어올게요.

# query 작성
query = """select top 100 * from [dbo].[test]"""" #test 대신 읽어오고자 하는 테이블명 입력

# 테이블 읽어서 DataFrame으로 변환
data = pd.read_sql(query, cnxn)

# DataFrame 출력
data.head()

아래와 같이 테이블이 잘 읽어와졌어요.

테이블 조회 결과
테이블 조회 결과
테이블 조회 결과
테이블 조회 결과
테이블 조회 결과
테이블 조회 결과

DB Insert 하기

직접 DB에 insert query를 실행하는 방법은 아래와 같아요. cursor 객체를 선언한 후에 query를 작성해주시면 됩니다. 그 후에 꼭 cnxn.commit()을 실행해 주셔야 DB에 반영이돼요. cnxn.commit()이 없으면 DB 락다운이 걸릴 수 있으니 꼭 주의해주세요.

#DB insert
cur = cnxn.cursor()

# 쿼리 작성
query = "insert into [dbo].[test] (name, company, nutrient_content, daily_amount) values (%s, %s, %s, %s)"
cur.execute(query, ("마노피아D", "코스맥스바이오(주)", "비타민K | 5 | mg", "2정"))

cnxn.commit()

프로시저 호출하기

미리 정의된 프로시저를 활용하는 방법을 알아보겠습니다. 저는 MY_PROCEDURE라는 프로시저를 미리 MSSQL에 정의해두었어요. 해당 프로시저를 호출하면 test테이블에 row가 insert되는 프로시저 입니다. 마찬가지로 cursor 객체를 만들어 준 후에 query를 작성해주시면 됩니다. cnxn.commit() 잊지 마세요!

cur = cnxn.cursor()

params = ("마노피아D", "코스맥스바이오(주)", "비타민K | 5 | mg", "2정")
cur.callproc('MY_PROCEDURE', params)

cnxn.commit()

DB 연결 해제하기

작업이 모두 끝났다면 DB와의 연결을 해제해주세요.

# DB 연결 해제
cursor.close()
cnxn.close()

pymssql 패키지를 통해 파이썬으로 쉽게 DB에 접근하거나 조작하는 방법을 알아보았습니다. MSSQL에서 테이블을 조회한 후 따로 엑셀로 추출하거나 할 필요 없이 간편하게 파이썬으로 분석할 수 있어 저는 아주 자주 활용하는 코드에요. 감사합니다! ❤️

Leave a Comment