SQL Server: App Service PYODBC Connection 연결 알아 보기 #1

Posted on by
Contents hide
1 Commnads
1.1 Create the project
1.1.1 Note.
1.2 Install the pyodbc driver
1.3 Configure the local connection string
1.4 Run and test the app locally

Agenda

SQL IAM (Identity and Access Management) 설정에 App Service의 시스템 할당 관리 ID로 유저를 생성하고, 역할을 할당하는 것만으로는 앱이 SQL Database 와 연결되지 않았다. 커넥션 설정을 추가함으로 연결 및 쿼리를 실행할 수 있게 된다.

Microsoft Learn 의 문서를 참고하여 테스트를 진행했고, 몇 가지시행착오에 대해서 정리하려고 한다.

  1. Microsoft Learn 을 참고로 PyODBC 커넥트 설정에 대해서 알아 본다.
  2. 로컬 환경과 Azure 환경에서 Python 커넥션 동작에 대해서 알아 본다.
  3. SQL Server 네트워크 설정에 대해서 알아 본다.

Commnads

나는 테스트에 Powershell 을 사용해서, Microsoft 문서의 명령어를 참고하여 수정하였다.

  1. $app_name : App Service 리소스 이름이 중복되면 App Service 배포에 문제가 되므로, 중복되지 않는 값으로 수정한다.
  2. $connectino_string : 아래의 값을 자신의 환경에 맞춰 수정한다.
    • Server=tcp:<SQL_SERVER_NAME>.database.windows.net
    • Database=<SQL_DATABASE_NAME>
# Variable for Local Test
$env:AZURE_SQL_CONNECTIONSTRING = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:demo-kgineer-sql.database.windows.net,1433;Database=demo-kgineer-sqldb;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

# Run the app.py file 
uvicorn app:app --reload

# Variables
$rg_name = "azure-learn-rg"
$app_name = "azure-learn-demo-app"
$connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:demo-kgineer-sql.database.windows.net,1433;Database=demo-kgineer-sqldb;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

# Azure CLI Commands
az webapp up `
  --resource-group $rg_name `
  --name $app_name

az webapp config set `
  --resource-group $rg_name `
  --name $app_name `
  --startup-file start.sh

az webapp identity assign `
  --resource-group $rg_name `
  --name $app_name

# SQL Query
CREATE USER [azure-learn-demo-app] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [azure-learn-demo-app]
ALTER ROLE db_datawriter ADD MEMBER [azure-learn-demo-app]

# Azure Commands
az webapp config appsettings set `
  --resource-group $rg_name `
  --name $app_name `
  --settings AZURE_SQL_CONNECTIONSTRING=$connection_string

# App Service API URL
https://azure-learn-demo-app.azurewebsites.net/docs

Create the project

Python 프로젝트를 위한 디렉토리를 생성한다.

디렉토리 생성 위치는 중요하지 않으므로, 대충 아무곳이나 지정하면 된다.

# Open Visual Studio Code and create a new folder for your project and change directory into it.
New-Item -ItemType Directory -Name python-sql-azure
cd python-sql-azure

# Create a virtual environment for the app.
py -m venv .venv
.venv\scripts\activate
.venv 가상 환경에 진입

Note.

(.venv)는 PowerShell 명령줄 프롬프트에서 현재 활성화된 Python 가상 환경을 나타내는 것이다. 여기서 .venv는 가상 환경의 이름이며, 이 환경은 Python 패키지와 의존성을 프로젝트별로 격리하여 관리하도록 해준다. 가상 환경을 사용하면 시스프의 다른 Python 프로젝트와 독립적으로 작업할 수 있어, 의존성 충돌을 방지하고 프로젝트의 요구 사항을 더 쉽게 관리할 수 있게 된다.

Install the pyodbc driver

# Create a requirements.txt file with the following lines
pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

.vnev 디렉토리 하위에 requirements.txt 를 만들고, 값을 붙여 넣는다.

requirements.txt 를 지정하여 pip install 명령어를 실행한다.

# Install the requirements.
pip install -r requirements.txt

Configure the local connection string

준비한 Connection string 을 로컬 환경 변수로 지정한다.

Powershell 을 사용하고 있으므로, export 명령어는 실행되지 않는다. 대신에 $env:<variable_name> 을 사용한다.

$env:AZURE_SQL_CONNECTIONSTRING="Driver={ODBC Driver 18 for SQL Server};Server=tcp:demo-kgineer-sql.database.windows.net,1433;Database=demo-kgineer-sqldb;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

echo $env:AZURE_SQL_CONNECTIONSTRING

Microsoft 문서에 있는 예제 코드를 복사하여 app.py 파일을 작성한다.

import os
import pyodbc, struct
from azure import identity

from typing import Union
from fastapi import FastAPI
from pydantic import BaseModel

class Person(BaseModel):
    first_name: str
    last_name: Union[str, None] = None
    
connection_string = os.environ["AZURE_SQL_CONNECTIONSTRING"]

app = FastAPI()

@app.get("/")
def root():
    print("Root of Person API")
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Table should be created ahead of time in production app.
        cursor.execute("""
            CREATE TABLE Persons (
                ID int NOT NULL PRIMARY KEY IDENTITY,
                FirstName varchar(255),
                LastName varchar(255)
            );
        """)

        conn.commit()
    except Exception as e:
        # Table may already exist
        print(e)
    return "Person API"

@app.get("/all")
def get_persons():
    rows = []
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons")

        for row in cursor.fetchall():
            print(row.FirstName, row.LastName)
            rows.append(f"{row.ID}, {row.FirstName}, {row.LastName}")
    return rows

@app.get("/person/{person_id}")
def get_person(person_id: int):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons WHERE ID = ?", person_id)

        row = cursor.fetchone()
        return f"{row.ID}, {row.FirstName}, {row.LastName}"

@app.post("/person")
def create_person(item: Person):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute(f"INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", item.first_name, item.last_name)
        conn.commit()

    return item

def get_conn():
    credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn
app.py

Run and test the app locally

아래의 명령어는 app.py 파일 내에 정의된 app 객체를 사용하여 개발 서버를 시작하고, 소스 코드가 변경될 때마다 서버를 자동으로 다시 시작하도록 설정한다. 서버를 시작 한 후 http://127.0.0.1:8000/docs 로 접속하여 로컬 환경에서 테스트가 가능하다.

# Run the app.py file in Visual Studio Code.
uvicorn app:app --reload

# Running logs
INFO:     Will watch for changes in these directories: ['C:\\Users\\cchi9\\OneDrive\\github_repo\\azure-python\\python-sql-azure\\.venv']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [17276] using WatchFiles
INFO:     Started server process [27096]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
http://127.0.0.1:8000/docs

로컬 서버에서 API 를 이용하여 SQL DB 와 정상적으로 연동되고 있는 것을 확인할 수 있다.

root API 를 실행하여, SQL DB에 Persons 테이블이 생성되는 것을 테스트한다.

app = FastAPI()

@app.get("/")
def root():
    print("Root of Person API")
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Table should be created ahead of time in production app.
        cursor.execute("""
            CREATE TABLE Persons (
                ID int NOT NULL PRIMARY KEY IDENTITY,
                FirstName varchar(255),
                LastName varchar(255)
            );
        """)

        conn.commit()
    except Exception as e:
        # Table may already exist
        print(e)
    return "Person API"
http://127.0.0.1:8000/docs#/default/root__get API를 실행 후 Persons 테이블이 생성된 화면