[Python] DB Connection - MSSQL

1. MSSQL Connection

참조 : hermeslog.tistory.com/501?category=306365

[Python] pymssql 설치 시 오류 - Microsoft Visual C++ 14.0 or greater is required.

(venv) C:\JetBrains\pythonProject\venv\Scripts>pip install pymssql-2.1.5-cp39-cp39-win_amd64.whl Processing c:\jetbrains\pythonproject\venv\scripts\pymssql-2.1.5-cp39-cp39-win_amd64.whl Installing collected packages: pymssql Successfully installed pymssql-2.1.5

2. Sample

2-1. Sample-01

import pymssql

config = {
    'server': '서버IP',
    'port': '포트',
    'user': '아이디',
    'password': '패스워드',
    'database': 'DB명',
    'charset': 'utf8'
}

# Construct connection string
try:
    conn = pymssql.connect(**config)
except Exception as err:
    print(err)
    raise
else:
    cursor = conn.cursor()
    cursor.execute('SELECT @@version;')
    row = cursor.fetchone()
    while row:
        print(str(row[0]))
        row = cursor.fetchone()

    # Cleanup
    # conn.commit()
    cursor.close()
    conn.close()
    print("Done.")

2-2. Sample-02

import pymssql
import logging


################################################################################
class MSSQL(object):

    # ==========================================================================
    def __init__(self, dbhost, dbport, dbuser, dbpass, dbname, charset, logger):
        self.host, self.port, self.user, self.passwd, self.db, self.charset \
            = dbhost, dbport, dbuser, dbpass, dbname, charset
        self.logger = logger
        # for internal
        self.is_opened = False
        self.conn = None

    # ==========================================================================
    def open(self):
        self.logger.debug('MSSQL.open: trying to open')
        try:
            self.conn = pymssql.connect(
                host=self.host,
                user=self.user,
                password=self.passwd,
                port=self.port,
                database=self.db,
                charset=self.charset
            )
        except Exception as err:
            logger.error(err)
            logger.exception(err)
            raise
        else:
            self.is_opened = True
            self.logger.debug('MSSQL.open: opened!')
            return self.is_opened

    # ==========================================================================
    def close(self):
        self.logger.debug('MSSQL.open: trying to close')
        if self.conn is not None:
            self.conn.close()
            self.conn = None
            self.is_opened = False
            self.logger.debug('MSSQL.close: closed!')

    # ==========================================================================
    def sql_select(self, sql):
        self.logger.debug('MSSQL.sql_select: sql=<%s>' % sql)
        with self.conn.cursor() as cursor:
            cursor.execute(sql)
            # num_fields = len(cursor.description)
            field_names = [x[0] for x in cursor.description]
            # noinspection PyUnusedLocal
            field_types = [x[1] for x in cursor.description]
            print('%s' % ','.join(field_names))

            rows = cursor.fetchall()
            for row in rows:
                print(str(row[0]))
                row = cursor.fetchone()


################################################################################
# 로거 인스턴스 반환
def __get_logger():
    __logger = logging.getLogger('logger')

    # 로그 포멧 정의
    formatter = logging.Formatter(
        # '##%(levelname)s##%(asctime)s##@@file::%(filename)s@@line::%(lineno)s >> %(message)s')
        '#%(levelname)s##%(asctime)s##@@line::%(lineno)s >> %(message)s')
    # 스트림 핸들러 정의
    stream_handler = logging.StreamHandler()
    # 각 핸들러에 포멧 지정
    stream_handler.setFormatter(formatter)
    # 로거 인스턴스에 핸들러 삽입
    __logger.addHandler(stream_handler)
    # 로그 레벨 정의
    __logger.setLevel(logging.DEBUG)

    return __logger


if __name__ == '__main__':
    logger = __get_logger()
    MSSQL.__init__(MSSQL, '서버IP', '포트', '아이디', '패스워드', 'DB명', 'utf8', logger)
    result = MSSQL.open(MSSQL)
    logger.debug(result)

    query = 'SELECT @@version;'
    MSSQL.sql_select(MSSQL, query)

    MSSQL.close(MSSQL)