Python ODBC 통해 리눅스 환경에서 윈도우 SQL Server 연결하기

Python py4j-JDBC, pyodbc-ODBC, pymssql-FreeTDS

리눅스 환경에서 Python을 통해 윈도우 SQL Server에 접근하기 위해 필요한 단계별 가이드입니다. 여기에는 패키지 설치, ODBC 드라이버 확인 및 설치, 설정 파일 구성, 파이썬 코드 작성 및 실행이 포함됩니다.

1. 패키지 설치

ODBC 드라이버와 Python용 pyodbc 패키지를 설치해야 합니다.

1.1 ODBC 드라이버 설치

SQL Server에 접근하기 위해 Microsoft의 ODBC Driver를 설치해야 합니다.

# Microsoft 패키지 키 추가
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

# Microsoft 패키지 리포지토리 추가
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

# 패키지 리스트 업데이트
sudo apt-get update

# ODBC Driver 18 설치
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

# unixODBC 개발 도구 설치
sudo apt-get install -y unixodbc-dev

1.2 Python 패키지 설치

ODBC 드라이버를 사용하기 위해 pyodbc 라이브러리를 설치해야 합니다.

# 파이썬 패키지 설치
pip install pyodbc

2. ODBC 드라이버 설치 확인

설치된 ODBC 드라이버를 확인하려면 다음 명령어를 사용합니다.

odbcinst -j

이 명령어는 ODBC 설정 파일의 경로를 보여줍니다. 그런 다음, 다음 명령어를 사용하여 설치된 드라이버를 확인할 수 있습니다:

cat /etc/odbcinst.ini

이 파일에서 ODBC Driver 18 for SQL Server 항목이 있는지 확인합니다. 드라이버가 나열되지 않으면 위의 드라이버 설치 단계를 다시 확인하세요.

3. ODBC 설정 파일 구성

ODBC 드라이버가 올바르게 동작하도록 설정 파일을 구성해야 합니다.

3.1 odbcinst.ini 파일 설정

odbcinst.ini 파일은 드라이버의 정보를 제공합니다.

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1

이 파일은 보통 /etc/odbcinst.ini에 위치합니다. 해당 파일이 없으면 새로 생성합니다.

3.2 odbc.ini 파일 설정

odbc.ini 파일은 데이터 소스와 연결 설정을 포함합니다.

[YourDataSource]
Driver=ODBC Driver 18 for SQL Server
Server=your_server_name_or_ip
Database=your_database_name

이 파일은 /etc/odbc.ini에 위치해야 합니다.

4. 환경 변수 설정

ODBC 설정 파일의 위치를 시스템에 알리기 위해 환경 변수를 설정합니다.

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

이 설정을 영구적으로 적용하려면, 해당 라인을 .bashrc 또는 .bash_profile 파일에 추가합니다.

5. SQL Server 연결 테스트

isql 명령어를 사용해 SQL Server에 대한 연결을 테스트합니다.

isql -v YourDataSource your_username your_password

이 명령어가 성공적으로 실행되면, ODBC 설정이 올바르게 되어 SQL Server에 접근할 수 있습니다.

6. 파이썬 코드 작성

파이썬에서 SQL Server에 연결하는 코드를 작성합니다.

import pyodbc

# ODBC 연결 문자열 설정
odbc_connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=your_server_name_or_ip;"
    "DATABASE=your_database_name;"
    "UID=your_username;"
    "PWD=your_password"
)

try:
    # 연결 시도
    cnxn = pyodbc.connect(odbc_connection_string)
    cursor = cnxn.cursor()

    # SQL 쿼리 실행
    cursor.execute("SELECT @@version;")

    # 결과 출력
    row = cursor.fetchone()
    while row:
        print(row)
        row = cursor.fetchone()

    # 연결 종료
    cnxn.close()
    print("Connection successful")
except pyodbc.Error as ex:
    print("Connection failed:", ex)

7. 실행 및 검증

이제 위의 파이썬 코드를 실행하여 SQL Server에 성공적으로 연결되는지 확인합니다. 연결이 성공하면 SQL Server의 버전 정보가 출력될 것입니다. 이 과정을 모두 완료하면, 리눅스 환경에서 파이썬을 사용하여 윈도우 SQL Server에 성공적으로 접근할 수 있습니다. 문제가 발생하면 각 단계에서 설정 파일, 드라이버 설치 상태, 네트워크 설정 등을 다시 확인하세요.

 

참고로, Oracle 데이터베이스에 접근하여 특정 데이터를 가져오는 Python 코드를 작성하려면 cx_Oracle 패키지를 사용할 수 있습니다. 다음은 Oracle DB에 연결하여 데이터를 조회하는 기본적인 예제입니다.

1. cx_Oracle 패키지를 설치합니다.
2. pip install cx_Oracle
3. Python 코드를 작성하여 Oracle 데이터베이스에 접근하고 데이터를 가져옵니다.

import cx_Oracle

# Oracle 데이터베이스 연결 설정
dsn_tns = cx_Oracle.makedsn('your_host', 'your_port', service_name='your_service_name')
connection = cx_Oracle.connect(user='your_username', password='your_password', dsn=dsn_tns)

try:
    # 커서 생성
    cursor = connection.cursor()

    # SQL 쿼리 작성
    query = "SELECT * FROM your_table WHERE your_column = :value"

    # 쿼리 실행
    cursor.execute(query, value='your_value')

    # 결과 가져오기
    rows = cursor.fetchall()

    # 결과 출력
    for row in rows:
        print(row)

finally:
    # 커서 및 연결 닫기
    cursor.close()
    connection.close()

1. 패키지 임포트: cx_Oracle 패키지를 사용합니다.
2. 데이터베이스 연결 설정: cx_Oracle.makedsn을 사용하여 데이터베이스의 DSN (Data Source Name)을 설정하고, cx_Oracle.connect를 사용하여 연결합니다.
   • your_host: Oracle 데이터베이스 서버의 호스트명 또는 IP 주소.
   • your_port: Oracle 데이터베이스 서버의 포트 번호.
   • your_service_name: Oracle 데이터베이스 서비스 이름.
   • your_username: Oracle 데이터베이스 사용자 이름.
   • your_password: Oracle 데이터베이스 사용자 비밀번호.
3. 커서 생성: connection.cursor()를 사용하여 커서를 생성합니다.
4. SQL 쿼리 작성: 필요한 SQL 쿼리를 작성합니다. 여기서는 특정 컬럼의 값에 따라 데이터를 조회하는 예제입니다.
5. 쿼리 실행: cursor.execute(query, value='your_value')를 사용하여 쿼리를 실행합니다.
6. 결과 가져오기: cursor.fetchall()을 사용하여 쿼리 결과를 가져옵니다.
7. 결과 출력: 반복문을 사용하여 결과를 출력합니다.
8. 커서 및 연결 닫기: 작업이 끝난 후 커서와 연결을 닫아 리소스를 해제합니다.

이 코드를 사용하여 Oracle 데이터베이스에 접근하고 필요한 데이터를 가져올 수 있습니다. 필요한 경우 쿼리와 연결 설정을 수정하여 사용할 수 있습니다.

 

unixODBC를 사용하여 MSSQL 서버에 접속할 수 있도록 설정하는 방법으로 Docker 환경에서 구성하는 방법입니다. unixODBC는 ODBC 드라이버를 관리하는 시스템으로, MSSQL용 msodbcsql17 드라이버와 함께 사용할 수 있습니다.

1. unixODBC 기반 Dockerfile 작성

아래는 unixODBC와 MSSQL용 ODBC 드라이버를 설치하고 설정하는 Dockerfile 예시입니다.

# 베이스 이미지 설정 (Python 3.9)
FROM python:3.9-slim

# 필수 패키지 업데이트 및 설치
RUN apt-get update && apt-get install -y \
    curl \
    apt-transport-https \
    gnupg \
    unixodbc \
    unixodbc-dev \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update && ACCEPT_EULA=Y apt-get install -y \
    msodbcsql17 \
    && apt-get clean -y \
    && rm -rf /var/lib/apt/lists/*

# 환경 변수 설정
ENV PATH="$PATH:/opt/mssql-tools/bin"

# 작업 디렉토리 설정
WORKDIR /app

# 필요한 패키지 설치
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# ODBC 설정 파일 복사 (필요 시 커스텀 설정)
COPY odbc.ini /etc/odbc.ini
COPY odbcinst.ini /etc/odbcinst.ini

# Python 스크립트 복사
COPY . .

# 컨테이너 실행 시 실행될 명령어
CMD ["python", "your_script.py"]

2. odbc.ini 및 odbcinst.ini 파일 구성

unixODBC를 통해 MSSQL 서버에 연결하려면 odbc.ini 및 odbcinst.ini 파일을 설정해야 합니다.

 

odbcinst.ini 파일은 ODBC 드라이버를 정의합니다.

[ODBC Drivers]
ODBC Driver 17 for SQL Server=Installed

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1

odbc.ini 파일은 데이터 소스 이름(DSN)을 설정합니다.

[ODBC Data Sources]
MSSQLServer=ODBC Driver 17 for SQL Server

[MSSQLServer]
Driver=ODBC Driver 17 for SQL Server
Server=your_server
Database=your_database
UID=your_username
PWD=your_password

odbc.ini 파일에서 Server, Database, UID, PWD 값을 실제 MSSQL 서버 정보로 대체해야 합니다.

3. 도커 이미지 빌드 및 실행

위의 Dockerfile과 odbc.ini, odbcinst.ini 파일을 사용하여 도커 이미지를 빌드하고 실행합니다.

1. 도커 이미지 빌드:
2. docker build -t mssql-data-collector .
3. 도커 컨테이너 실행:
4. docker run -d --name mssql-collector mssql-data-collector

4. 추가 설명

• unixODBC: ODBC 드라이버 및 DSN을 관리하는 시스템입니다.
• odbcinst.ini와 odbc.ini 파일은 각각 드라이버와 데이터 소스를 정의하는 설정 파일입니다.
• msodbcsql17 드라이버는 unixODBC와 함께 사용되어 Python 스크립트에서 MSSQL에 연결할 수 있게 합니다.

 

이 구성을 통해 MSSQL 서버에 연결하고 데이터를 수집하는 Python 스크립트를 Docker 컨테이너에서 실행할 수 있습니다. odbc.ini와 odbcinst.ini 파일을 통해 ODBC 설정을 쉽게 관리할 수 있으며, 필요에 따라 추가적인 설정을 할 수 있습니다.

 

MSSQL 서버에 접속하여 정기적으로 데이터를 수집하는 Python 스크립트를 작성하고, 이를 Docker 이미지로 빌드하기 위한 Dockerfile을 작성하는 방법입니다. 데이터베이스 접속 정보는 Docker 컨테이너 실행 시 환경 변수로 입력받아 Python 코드에서 사용할 수 있도록 구성합니다. 이 방법은 보안과 유연성을 고려하여 크리덴셜 정보를 코드에 하드코딩하지 않고, Docker 컨테이너 구동 시 전달하는 방식입니다.

Python 코드 수정

데이터베이스 접속 정보를 환경 변수로 받아 사용하는 Python 코드를 작성합니다.

import pyodbc
import os
import time

# 환경 변수로부터 DB 접속 정보 가져오기
server = os.getenv('DB_SERVER')
database = os.getenv('DB_DATABASE')
username = os.getenv('DB_USERNAME')
password = os.getenv('DB_PASSWORD')

# MSSQL 서버에 접속
def connect_to_mssql():
    connection_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'
    connection = pyodbc.connect(connection_str)
    return connection

# 데이터를 수집하는 함수
def collect_data():
    connection = connect_to_mssql()
    cursor = connection.cursor()

    # 쿼리 예시
    cursor.execute("SELECT * FROM your_table")

    # 결과 처리
    for row in cursor.fetchall():
        print(row)

    # 연결 닫기
    cursor.close()
    connection.close()

# 정기적으로 데이터를 수집하는 함수
def run_periodically(interval_seconds):
    while True:
        collect_data()
        time.sleep(interval_seconds)

if __name__ == "__main__":
    # 10분(600초)마다 데이터 수집
    run_periodically(600)

Dockerfile 작성

Python 코드와 환경 변수 설정을 반영한 Dockerfile을 작성합니다.

# 베이스 이미지 설정 (Python 3.9)
FROM python:3.9-slim

# 필수 패키지 업데이트 및 설치
RUN apt-get update && apt-get install -y \
    curl \
    apt-transport-https \
    gnupg \
    unixodbc \
    unixodbc-dev \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update && ACCEPT_EULA=Y apt-get install -y \
    msodbcsql17 \
    && apt-get clean -y \
    && rm -rf /var/lib/apt/lists/*

# 환경 변수 설정 (필요 시 커스텀 설정)
ENV PATH="$PATH:/opt/mssql-tools/bin"

# 작업 디렉토리 설정
WORKDIR /app

# 필요한 패키지 설치
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# Python 스크립트 복사
COPY . .

# 컨테이너 실행 시 실행될 명령어
CMD ["python", "your_script.py"]

requirements.txt 파일 작성

pyodbc 패키지를 설치하기 위해 requirements.txt에 아래 내용을 추가합니다.

pyodbc==4.0.32

도커 이미지 빌드

이제 위의 Dockerfile을 사용하여 도커 이미지를 빌드합니다.

docker build -t mssql-data-collector .

도커 컨테이너 실행

컨테이너를 실행할 때 환경 변수를 전달하여 DB 접속 정보를 설정합니다.

docker run -d --name mssql-collector \
  -e DB_SERVER=your_server \
  -e DB_DATABASE=your_database \
  -e DB_USERNAME=your_username \
  -e DB_PASSWORD=your_password \
  mssql-data-collector

• Python 코드 수정: 환경 변수로부터 DB 접속 정보를 읽어오는 방식으로 수정합니다.
• Dockerfile 작성: Python 스크립트를 포함한 도커 이미지를 생성합니다. 이 이미지에는 pyodbc와 unixODBC 설정이 포함됩니다.
• 도커 이미지 빌드: Dockerfile을 사용하여 이미지를 빌드합니다.
• 도커 컨테이너 실행: docker run 명령어를 사용해 컨테이너를 실행하면서 환경 변수로 DB 접속 정보를 전달합니다.

이렇게 구성하면 민감한 정보를 코드에 하드코딩하지 않고, Docker 컨테이너 실행 시 안전하게 전달할 수 있습니다. 또한, 동일한 이미지를 다양한 환경에서 쉽게 재사용할 수 있습니다.