mssql - API 연결

안녕하세요!

`AutoSave.py` 스크립트와 관련하여 주신 추가 정보에 감사드립니다. 현재 상황을 종합해보면 두 가지 주요 문제가 발생하고 있는 것 같습니다:

1. **SQL 명령어 실행 위치 및 방법**
2. **Python 스크립트에서 MSSQL 서버를 사용하도록 수정 필요 및 API 토큰 처리**

이 두 가지를 순차적으로 해결해 보겠습니다.

---

## **1. MySQL 명령어 실행 위치 및 방법**

먼저, 사용하시는 데이터베이스가 **MySQL**이 아니라 **Microsoft SQL Server (MSSQL)**임을 확인하셨습니다. 따라서 이전에 제공한 MySQL 관련 명령어(`SHOW DATABASES;`, `USE your_database;`, 등)는 MSSQL에서는 약간 다릅니다.

### **a. MSSQL에서 데이터베이스 및 테이블 구조 확인하기**

MSSQL에서 데이터베이스와 테이블을 확인하기 위한 SQL 명령어는 다음과 같습니다:

```sql
-- 모든 데이터베이스 나열
SELECT name FROM sys.databases;

-- 특정 데이터베이스 사용
USE your_database;  -- 실제 데이터베이스 이름으로 변경

-- 데이터베이스 내 모든 테이블 나열
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- 특정 테이블 구조 확인
EXEC sp_columns flights;  -- 'flights' 테이블 구조 확인
```

### **b. SQL 명령어를 실행할 위치**

MSSQL 명령어를 실행하기 위해서는 **SQL Server Management Studio (SSMS)** 또는 **Azure Data Studio**와 같은 도구를 사용하셔야 합니다.

#### **i. SQL Server Management Studio (SSMS) 사용하기**

1. **SSMS 다운로드 및 설치**
   - [SSMS 다운로드 링크](https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms)
   - 설치 파일을 다운로드 받아 설치를 진행하세요.

2. **SSMS 실행 및 서버에 연결**
   - SSMS를 실행하고, **서버 이름**에 `localhost` 또는 해당 SQL Server 인스턴스 이름을 입력합니다.
   - **인증 방식**을 선택합니다 (`Windows 인증` 또는 `SQL Server 인증`).
   - **로그인** 정보 입력 후 연결을 클릭합니다.

3. **SQL 명령어 실행**
   - SSMS 상단의 **New Query** 버튼을 클릭하여 새 쿼리 창을 엽니다.
   - 위의 MSSQL 명령어를 입력하고 **Execute** 버튼을 클릭하여 실행합니다.

#### **ii. Azure Data Studio 사용하기**

1. **Azure Data Studio 다운로드 및 설치**
   - [Azure Data Studio 다운로드 링크](https://docs.microsoft.com/ko-kr/sql/azure-data-studio/download-azure-data-studio)
   - 설치 파일을 다운로드 받아 설치를 진행하세요.

2. **Azure Data Studio 실행 및 서버에 연결**
   - Azure Data Studio를 실행하고, **New Connection**을 클릭합니다.
   - **서버 이름**, **인증 방식**, **로그인** 정보를 입력 후 연결합니다.

3. **SQL 명령어 실행**
   - 상단의 **New Query** 버튼을 클릭하여 새 쿼리 창을 엽니다.
   - 위의 MSSQL 명령어를 입력하고 **Run** 버튼을 클릭하여 실행합니다.

---

## **2. `AutoSave.py` 스크립트 MSSQL 서버에 맞게 수정하기**

현재 `AutoSave.py` 스크립트는 MySQL을 사용하도록 설정되어 있습니다. 이를 MSSQL 서버와 호환되도록 수정해야 합니다. MSSQL에 연결하기 위해서는 `pyodbc` 또는 `pymssql`과 같은 라이브러리를 사용할 수 있습니다. 여기서는 `pyodbc`를 사용하는 방법을 안내드리겠습니다.

### **a. 필요한 패키지 설치**

먼저, MSSQL 서버에 연결하기 위해 필요한 패키지와 드라이버를 설치해야 합니다.

1. **ODBC Driver for SQL Server 설치**
   - [ODBC Driver for SQL Server 다운로드 링크](https://docs.microsoft.com/ko-kr/sql/connect/odbc/download-odbc-driver-for-sql-server)
   - 운영체제에 맞는 드라이버를 다운로드 받고 설치합니다.

2. **Python 패키지 설치**
   - `pyodbc`와 `requests` 패키지를 설치합니다.
   - 가상 환경을 사용하는 것을 권장합니다.

   ```powershell
   python -m venv venv
   .\venv\Scripts\Activate
   pip install pyodbc requests
   ```

### **b. `AutoSave.py` 수정 예시**

아래는 MSSQL 서버를 사용하도록 수정된 `AutoSave.py`의 예시입니다. API 토큰은 환경 변수로 관리하며, 보안상 코드 내에 직접 입력하지 않습니다.

```python
import os
import time
import logging
import pyodbc
import requests
from datetime import datetime

# 로깅 설정
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler("autosave.log"),
        logging.StreamHandler()
    ]
)

def fetch_flight_data():
    """
    비행기 데이터 API를 호출하여 데이터를 가져오는 함수.
    API URL과 토큰은 실제 API 엔드포인트 및 인증 방식에 맞게 변경하세요.
    """
    API_URL = 'https://api.example.com/data' # 실제 API URL로 변경
    API_TOKEN = os.getenv('API_TOKEN')  # 환경 변수에서 API 토큰 가져오기

    if not API_TOKEN:
        logging.error("API 토큰이 설정되지 않았습니다.")
        print("API 토큰이 설정되지 않았습니다.")
        return None

    headers = {
        'Authorization': f'Bearer {API_TOKEN}',
        'Content-Type': 'application/json'
    }

    try:
        response = requests.get(API_URL, headers=headers)
        response.raise_for_status()  # HTTP 에러 발생 시 예외 발생
        logging.info("API 호출 성공.")
        return response.json()
    except requests.exceptions.RequestException as e:
        logging.error(f"API 호출 오류: {e}")
        return None

def store_flight_data(conn, data):
    """
    비행기 데이터를 MSSQL 데이터베이스에 저장하는 함수.
    테이블 이름과 컬럼을 실제 DB 구조에 맞게 변경하세요.
    """
    if not data:
        logging.info("저장할 데이터가 없습니다.")
        print("저장할 데이터가 없습니다.")
        return

    try:
        cursor = conn.cursor()
        for flight in data.get('data', []):
            cursor.execute("""
                INSERT INTO flights (
                    fr24_id, hex, callsign, lat, lon, track, alt, gspeed, vspeed, squawk, timestamp, source
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, 
                flight.get('fr24_id'),
                flight.get('hex'),
                flight.get('callsign'),
                flight.get('lat'),
                flight.get('lon'),
                flight.get('track'),
                flight.get('alt'),
                flight.get('gspeed'),
                flight.get('vspeed'),
                flight.get('squawk'),
                datetime.strptime(flight.get('timestamp'), '%Y-%m-%dT%H:%M:%SZ'),  # ISO 8601 형식 변환
                flight.get('source')
            )
            logging.debug(f"삽입된 데이터: {flight}")
        conn.commit()
        logging.info("데이터베이스에 데이터 삽입 완료.")
        print("데이터가 성공적으로 저장되었습니다.")
    except pyodbc.Error as e:
        logging.error(f"MSSQL 데이터 삽입 오류: {e}")
        print(f"MSSQL 데이터 삽입 오류: {e}")
    finally:
        cursor.close()

def main():
    # 환경 변수에서 데이터베이스 비밀번호 가져오기
    DB_PASSWORD = os.getenv('DB_PASSWORD')
    if not DB_PASSWORD:
        logging.error("데이터베이스 비밀번호가 설정되지 않았습니다.")
        print("데이터베이스 비밀번호가 설정되지 않았습니다.")
        return

    # 환경 변수에서 데이터베이스 사용자명 및 다른 정보 가져오기
    DB_SERVER = os.getenv('DB_SERVER', 'localhost')  # 기본값: localhost
    DB_NAME = os.getenv('DB_NAME', 'your_database')  # 실제 데이터베이스 이름으로 변경
    DB_USER = os.getenv('DB_USER', 'your_user')      # 실제 데이터베이스 사용자 이름으로 변경

    # 데이터베이스 연결 설정
    try:
        conn = pyodbc.connect(
            f'DRIVER={{ODBC Driver 17 for SQL Server}};'
            f'SERVER={DB_SERVER};'
            f'DATABASE={DB_NAME};'
            f'UID={DB_USER};'
            f'PWD={DB_PASSWORD}'
        )
        logging.info("MSSQL에 성공적으로 연결되었습니다.")
        print("MSSQL에 성공적으로 연결되었습니다.")
    except pyodbc.Error as err:
        logging.error(f"MSSQL 연결 에러: {err}")
        print(f"MSSQL 연결 에러: {err}")
        return

    try:
        while True:
            data = fetch_flight_data()
            if data:
                flights = data.get('flights', [])
                if not flights:
                    logging.info("비행기 데이터가 없습니다.")
                    print("비행기 데이터가 없습니다.")
                else:
                    store_flight_data(conn, data)
            else:
                logging.info("API 응답이 없습니다.")
                print("API 응답이 없습니다.")
            
            print("15초 대기 중...")
            time.sleep(15)  # 15초 대기
    except KeyboardInterrupt:
        logging.info("사용자에 의해 스크립트가 중단되었습니다.")
        print("\n스크립트가 중단되었습니다.")
    except Exception as e:
        logging.error(f"예상치 못한 에러: {e}")
        print(f"예상치 못한 에러: {e}")
    finally:
        if conn:
            conn.close()
            logging.info("데이터베이스 연결이 종료되었습니다.")
            print("데이터베이스 연결이 종료되었습니다.")

if __name__ == "__main__":
    main()
```

### **c. 주요 수정 사항 설명**

1. **MSSQL 연결을 위한 `pyodbc` 사용:**
   - `pyodbc`를 통해 MSSQL 서버에 연결합니다.
   - `SERVER`, `DATABASE`, `UID`, `PWD` 등의 연결 정보를 환경 변수로 관리합니다.

2. **API 토큰 관리:**
   - API 토큰을 `API_TOKEN` 환경 변수로 관리하고, `fetch_flight_data()` 함수에서 헤더에 포함시켜 요청합니다.

3. **환경 변수 추가:**
   - `DB_SERVER`, `DB_NAME`, `DB_USER` 등의 정보를 환경 변수로 관리하여 보안과 유연성을 높입니다.

4. **타임스탬프 형식 변환:**
   - API에서 받은 `timestamp`를 `datetime` 객체로 변환하여 MSSQL `DATETIME` 필드에 맞춥니다.

5. **로깅 및 출력 강화:**
   - `autosave.log` 파일과 콘솔에 로그가 기록되도록 설정하여 문제 발생 시 쉽게 추적할 수 있습니다.

### **d. 환경 변수 설정하기**

스크립트가 실행되기 전에 필요한 환경 변수를 설정해야 합니다. 이는 보안상의 이유로 권장되는 방법입니다.

#### **Windows (PowerShell)에서 환경 변수 설정:**

```powershell
# 현재 세션에만 적용
$env:DB_PASSWORD = "your_actual_db_password"
$env:DB_SERVER = "localhost"           # 또는 실제 서버 주소
$env:DB_NAME = "your_database"         # 실제 데이터베이스 이름
$env:DB_USER = "your_user"             # 실제 데이터베이스 사용자 이름
$env:API_TOKEN = "9e098..."            # 실제 API 토큰

# 스크립트 실행
python .\AutoSave.py
```

#### **영구적으로 환경 변수 설정 (Windows):**

1. **제어판 > 시스템 및 보안 > 시스템 > 고급 시스템 설정 > 환경 변수**
2. **사용자 변수** 또는 **시스템 변수**에 다음을 추가:
   - `DB_PASSWORD`
   - `DB_SERVER`
   - `DB_NAME`
   - `DB_USER`
   - `API_TOKEN`

3. **변경 사항 적용을 위해 터미널을 다시 시작**

#### **macOS/Linux에서 환경 변수 설정:**

1. **셸 프로파일에 추가 (bash 사용 시):**

   ```bash
   echo 'export DB_PASSWORD="your_actual_db_password"' >> ~/.bashrc
   echo 'export DB_SERVER="localhost"' >> ~/.bashrc
   echo 'export DB_NAME="your_database"' >> ~/.bashrc
   echo 'export DB_USER="your_user"' >> ~/.bashrc
   echo 'export API_TOKEN="9e098..."' >> ~/.bashrc
   source ~/.bashrc
   ```

2. **현재 세션에서만 설정:**

   ```bash
   export DB_PASSWORD="your_actual_db_password"
   export DB_SERVER="localhost"
   export DB_NAME="your_database"
   export DB_USER="your_user"
   export API_TOKEN="9e098..."
   ```

### **e. 데이터베이스 스키마 확인**

`AutoSave.py` 스크립트가 데이터를 저장하는 `flights` 테이블의 스키마가 스크립트에서 사용하는 데이터와 일치하는지 확인해야 합니다. 예를 들어, 다음과 같은 테이블 스키마를 사용할 수 있습니다:

```sql
CREATE TABLE flights (
    id INT IDENTITY(1,1) PRIMARY KEY,
    fr24_id VARCHAR(50),
    hex VARCHAR(10),
    callsign VARCHAR(50),
    lat DECIMAL(10, 6),
    lon DECIMAL(10, 6),
    track INT,
    alt INT,
    gspeed INT,
    vspeed INT,
    squawk VARCHAR(10),
    timestamp DATETIME,
    source VARCHAR(50)
);
```

#### **테이블 스키마 확인**

1. **SSMS 또는 Azure Data Studio에서 `flights` 테이블 찾기**
2. **테이블 이름을 마우스 우클릭 > 디자이너 또는 스키마 보기 선택**
3. **각 칼럼 이름과 데이터 타입이 스크립트와 일치하는지 확인**

### **f. 스크립트 실행 및 데이터 확인**

1. **환경 변수 설정 후 스크립트 실행**

   ```powershell
   python .\AutoSave.py
   ```

2. **콘솔 출력 및 로그 파일 (`autosave.log`) 확인**
   - 정상적인 경우, "MSSQL에 성공적으로 연결되었습니다.", "API 호출 성공.", "데이터가 성공적으로 저장되었습니다.", "15초 대기 중..." 등의 메시지가 표시됩니다.
   - 오류 발생 시, 로그 파일에 상세한 오류 메시지가 기록됩니다.

3. **데이터베이스에서 데이터 확인**

   SSMS 또는 Azure Data Studio에서 다음과 같은 쿼리를 실행하여 데이터가 정상적으로 삽입되었는지 확인합니다:

   ```sql
   USE your_database;  -- 실제 데이터베이스 이름으로 변경
   SELECT TOP 10 * FROM flights ORDER BY id DESC;
   ```

---

## **추가 팁 및 권장 사항**

### **1. API 토큰 보안 강화**

API 토큰은 **환경 변수**를 통해 관리하는 것이 안전합니다. 코드 내에 직접 입력하는 것보다 환경 변수를 사용하는 것이 보안상 좋습니다.

### **2. 에러 핸들링 및 로깅 강화**

- **예외 처리:** 모든 중요한 단계에서 예외 처리를 통해 오류를 포착하고 로그에 기록해야 합니다.
- **로깅 레벨 조정:** 개발 단계에서는 `DEBUG` 레벨로 로그를 기록하고, 운영 단계에서는 `INFO` 또는 `WARNING` 수준으로 조정할 수 있습니다.

### **3. 가상 환경 사용**

가상 환경을 사용하면 프로젝트별로 독립적인 패키지 관리가 가능하여 충돌을 방지할 수 있습니다.

```powershell
python -m venv venv
.\venv\Scripts\Activate
pip install pyodbc requests
```

### **4. 테스트 스크립트 작성**

데이터베이스 연결과 데이터 삽입이 정상적으로 동작하는지 확인하기 위해 간단한 테스트 스크립트를 작성할 수 있습니다.

```python
import os
import logging
import pyodbc
from datetime import datetime

# 로깅 설정
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler("test_connection.log"),
        logging.StreamHandler()
    ]
)

def test_connection():
    DB_PASSWORD = os.getenv('DB_PASSWORD')
    DB_SERVER = os.getenv('DB_SERVER', 'localhost')
    DB_NAME = os.getenv('DB_NAME', 'your_database')
    DB_USER = os.getenv('DB_USER', 'your_user')

    if not all([DB_PASSWORD, DB_USER, DB_NAME]):
        logging.error("필요한 환경 변수가 설정되지 않았습니다.")
        return
    
    try:
        conn = pyodbc.connect(
            f'DRIVER={{ODBC Driver 17 for SQL Server}};'
            f'SERVER={DB_SERVER};'
            f'DATABASE={DB_NAME};'
            f'UID={DB_USER};'
            f'PWD={DB_PASSWORD}'
        )
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO flights (
                fr24_id, hex, callsign, lat, lon, track, alt, gspeed, vspeed, squawk, timestamp, source
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, 
            'test_id',
            'ABC123',
            'TESTCALL',
            0.0,
            0.0,
            0,
            0,
            0,
            0,
            '0000',
            datetime.now(),
            'TEST_SOURCE'
        )
        conn.commit()
        logging.info("테스트 데이터 삽입 성공.")
    except pyodbc.Error as e:
        logging.error(f"MSSQL 연결 또는 삽입 오류: {e}")
    finally:
        if conn:
            cursor.close()
            conn.close()
            logging.info("MSSQL 연결 종료.")

if __name__ == "__main__":
    test_connection()
```

이 스크립트를 실행하여 데이터베이스 연결과 데이터 삽입이 정상적으로 이루어지는지 확인할 수 있습니다. 정상적으로 동작하면 `flights` 테이블에 테스트 데이터가 삽입됩니다.

### **5. 환경 변수 확인**

스크립트를 실행하기 전에 환경 변수가 올바르게 설정되었는지 확인해야 합니다.

**PowerShell에서 환경 변수 확인:**

```powershell
echo $env:DB_PASSWORD
echo $env:DB_SERVER
echo $env:DB_NAME
echo $env:DB_USER
echo $env:API_TOKEN
```

각 환경 변수가 올바른 값을 가지고 있는지 확인하세요.

### **6. 데이터베이스 권한 확인**

`your_user` 계정이 `your_database`에서 데이터 삽입 권한 (`INSERT`)을 가지고 있는지 확인해야 합니다. 필요하다면, SSMS에서 해당 사용자 계정의 권한을 확인하고 조정하세요.

#### **권한 부여 예시:**

```sql
USE your_database;
GO

-- 사용자 추가 (이미 존재하는 경우 생략)
CREATE USER your_user FOR LOGIN your_user;

-- 권한 부여
ALTER ROLE db_datawriter ADD MEMBER your_user;
GO
```

---

## **결론**

현재 겪고 계신 문제는 **MySQL과 MSSQL의 혼동**과 **API 토큰 관리의 미흡**에서 비롯된 것으로 보입니다. 위에서 안내드린 내용들을 기반으로 다음 단계를 따라 진행하시기 바랍니다:

1. **MSSQL 환경 설정 및 연결 확인:**
   - SSMS 또는 Azure Data Studio를 사용하여 데이터베이스와 테이블 구조를 정확히 확인하세요.
   - 테스트 스크립트를 통해 MSSQL 서버와의 연결 및 데이터 삽입이 정상적으로 이루어지는지 확인하세요.

2. **`AutoSave.py` 스크립트 수정:**
   - `pyodbc`를 사용하여 MSSQL 서버에 연결하도록 스크립트를 수정하세요.
   - API 토큰을 환경 변수로 관리하고, 스크립트에서 이를 사용하도록 설정하세요.
   - 로깅을 통해 스크립트의 실행 상태와 오류를 모니터링하세요.

3. **환경 변수 설정:**
   - 필요한 모든 환경 변수를 올바르게 설정하고, 스크립트가 이를 참조하도록 하세요.

4. **데이터 삽입 확인:**
   - 스크립트를 실행한 후, 데이터베이스에서 데이터가 정상적으로 삽입되었는지 확인하세요.
   - 로그 파일과 콘솔 출력을 통해 스크립트의 동작 상태를 모니터링하세요.

이 과정을 통해 `AutoSave.py` 스크립트가 정상적으로 API 데이터를 MSSQL 서버에 저장할 수 있을 것입니다. 추가적인 문제가 발생하거나 도움이 필요하시면 언제든지 문의해 주세요.

성공적인 프로젝트 진행을 기원합니다! 😊