카테고리 없음

[파이썬] MS-SQL 서버 접속하기

imsk.coz 2021. 6. 15. 16:06

개발을 하다보면 SQL 서버에서 데이터를 인출, 수정, 저장하는 경우가 종종 있다. 기본적으로는 SQL 쿼리를 이용하면 되지만, 웹 사이트에서 받아온 데이터를 가공하여 저장할 때는 프로그래밍 언어에서 바로 연결하는 것이 편리하다. 이번 글에서는 파이썬에서 MS-SQL 서버에 연결하는 방법을 소개한다.

​

마이크로소프트에서 공식 가이드를 배포하고 있지만, 그 과정에서 발생하는 문제들을 해결하려면 깃헙과 스택 오버플로 여기저기를 배회해야 한다. 이를 통합해서 정리한 자료가 있으면 삽질하는 시간을 많이 아낄 수 있을 것이다.

​

아래의 예제들은 파이썬 3.9.x 버전을 기준으로 작성되었다. 파이썬에서 MS-SQL 접속에 사용하는 라이브러리에는 pyodbc와 pymssql이 있는데, 마이크로소프트 공식 가이드에서 pyodbc를 권장하고 있으며 나도 pyodbc가 편했다. 그래서 pyodbc를 이용한 방법만 서술한다.

​

서버에 연결하기

1) 원격 서버 연결

본인이 사용하는 터미널에서 "pip install pyodbc" 명령을 입력해 pyodbc를 설치한다. 일단 설치했다면 서버에 연결하는 것은 쉽다.

import pyodbc

server = 'server_name'
database = 'database_name'
username = 'username'
password = 'password'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=' + server + ';'
                      'DATABASE=' + database + ';'
                      'UID=' + username + ';'
                      'PWD=' + password + ';')

cursor = conn.cursor()

위의 예시는 원격 서버에 연결하는 파이썬 코드이다. 서버, 데이터베이스, 유저명, 비밀번호 값을 입력하고 pyodbc.connect 함수를 통해 서버에 연결한다.

​

쿼리 형태로 파라미터들을 넣어준다. 본인이 어떤 드라이버를 사용하는지 모른다면, pyodbc.drivers() 함수를 이용해서 내 컴퓨터에 설치된 SQL 드라이버 목록을 불러올 수 있다. 다만 오래된 드라이버로 접속하면 문제가 많이 생기니, ODBC Driver 17 이전 버전을 사용하고 있다면, ODBC Driver 17 공식 배포판을 설치한 다음 위의 코드를 그대로 사용할 것을 권한다.

​

연결을 마친 이후에는 cursor(일종의 클라이언트)를 지정하고, 이를 통해 서버에 명령을 넘겨줄 것이다.

​

2) 원격 접속 시 유의사항

원격 서버에서 화이트 리스트 형태로 접속 가능 IP를 관리하고 있다면, 서버 관리자가 접속하려는 기기의 IP를 등록해주어야 접속이 가능하다. IP를 등록했더라도 유동 IP를 이용하는 경우에는 값이 수시로 바뀔 수 있으니, 시간이 흘렀다면 재확인이 필요하다.

​

3) 로컬 서버 연결

윈도우 인증을 사용해서 로컬—내 컴퓨터에 직접 연결되어 있는 서버에 연결하는 경우에는 윈도우 계정을 알아서 입력하기 때문에 username과 password는 전달할 필요가 없다. 대신 윈도우 인증을 사용한다는 의미로 'Trusted_Connection=yes' 파라미터를 쿼리에 넣어준다.

import pyodbc

server = 'server_name'
database = 'database_name'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=' + server + ';'
                      'DATABASE=' + database + ';'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

​

명령 실행하기

위에서 접속에 성공했다면, 서버에 명령문(쿼리)을 전달해야 한다. 두 가지 기능을 구현해보자.

​

1) 테이블 생성

table_name = 'QUESTIONS_OF_HUMANITY'

cursor.execute("""
    CREATE TABLE [dbo].[""" + table_name + """](
        [Q_KEY] [nvarchar](255) NULL,
        [A_KEY] [numeric](18, 2) NULL,
    ) ON [PRIMARY]
    """
)

conn.commit()
conn.close()

SQL 문법 그대로 cursor.excute 함수에 입력하면 된다. 가독성을 위해 """ 여러줄 문자열을 이용했다.

실행문이 끝나면 commit() 함수를 사용해야만 변경 내용이 저장되므로 잊지 말아야 한다. 또 볼일이 끝났다면 close() 함수로 연결을 끊는 것도 중요하다.

​

기존에 동일한 이름의 테이블이 없을 때만 테이블을 만들려면 조건문을 더해서 아래와 같이 입력하면 된다.

table_name = 'QUESTIONS_OF_HUMANITY'

cursor.execute("""
    IF  NOT EXISTS (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'[dbo].[""" + table_name + """]') 
				AND type in (N'U'))
    BEGIN
        CREATE TABLE [dbo].[""" + table_name + """](
            [Q_KEY] [nvarchar](255) NULL,
            [A_KEY] [numeric](18, 2) NULL,
        ) ON [PRIMARY]
    END;
    """
)

conn.commit()
conn.close()

 

2) 데이터 삽입

아래는 위에서 만든 테이블에 데이터를 삽입하는 코드이다. SQL 쿼리 내용만 제외하면 테이블 생성 코드와 유사하다.

table_name = 'QUESTIONS_OF_HUMANITY'
great_question = 'What is truth?'
awesome_answer = 42

cursor.execute("""
    INSERT INTO """ + table_name + """ (
        [Q_KEY],
        [A_KEY]
    ) VALUES (
        ?,
        ?
    );
		""",
        great_question,
        awesome_answer
)

conn.commit()
conn.close()

여기서 유의할 점이 두 가지 있다.

  • table_name 앞 뒤 문자열에 공백 하나씩을 추가해야 한다. 그렇지 않으면 INTO와 테이블 이름을 붙여서 인식하기 때문에 에러가 발생한다.
  • 값을 전달할 때 파이썬의 플레이스홀더(%s, %d 등)를 사용할 수 없다. (SQL 인젝션 등의 보안 사고를 방지하기 위해서라고 한다.) 위의 코드처럼 변수 자리에 '?'를 입력하고 쿼리문 이후에 입력값을 쉼표로 구분해줘야 한다.

테이블에 동일한 키값이 존재하지 않는 경우에만 데이터를 삽입하는 코드는 아래와 같다.

table_name = 'QUESTIONS_OF_HUMANITY'
great_question = 'What is truth?'
awesome_answer = 42

cursor.execute("""
    IF NOT EXISTS (
        SELECT  *
        FROM """ + table_name + """ WHERE Q_KEY = ?
    )
    BEGIN
        INSERT INTO """ + table_name + """ (
            [Q_KEY],
            [A_KEY]
        ) VALUES (
            ?,
            ?
        )
    END;
		""",
            great_question,
            great_question,
            awesome_answer
)

conn.commit()
conn.close()