Python용 Azure SQL Database 라이브러리Azure SQL Database libraries for Python

개요Overview

pyodbc ODBC 데이터베이스 드라이버를 사용하여 Python의 Azure SQL Database에 저장된 데이터로 작업합니다.Work with data stored in Azure SQL Database from Python with the pyodbc ODBC database driver. 빠른 시작을 보면서 Azure SQL 데이터베이스에 연결하고 Transact-SQL 문을 사용하여 데이터를 쿼리하고 pyodbc를 사용하여 샘플을 시작합니다.View our quickstart on connecting to an Azure SQL database and using Transact-SQL statements to query data and getting started sample with pyodbc.

ODBC 드라이버 및 pyodbc 설치Install ODBC driver and pyodbc

pip install pyodbc

Python 및 데이터베이스 통신 라이브러리 설치에 대한 세부 정보입니다.More details about installing the python and database communication libraries.

SQL 쿼리 연결 및 실행Connect and execute a SQL query

SQL 데이터베이스에 연결Connect to a SQL database

import pyodbc

server = 'your_server.database.windows.net'
database = 'your_database'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 13 for SQL Server}'

cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

SQL 쿼리 실행Execute a SQL query

cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

ORM에 연결Connecting to ORMs

pyodbc는 SQLAlchemy 및 Django 등의 다른 ORM과 함께 작동합니다.pyodbc works with other ORMs such as SQLAlchemy and Django.

관리 APIManagement API

관리 API를 사용하여 구독의 Azure SQL Database 리소스를 만들고 관리합니다.Create and manage Azure SQL Database resources in your subscription with the management API.

pip install azure-common
pip install azure-mgmt-sql
pip install azure-mgmt-resource

예Example

SQL Database 리소스를 만들고 방화벽 규칙을 사용하여 IP 주소 범위에 대한 액세스를 제한합니다.Create a SQL Database resource and restrict access to a range of IP addresses using a firewall rule.

from azure.common.client_factory import get_client_from_cli_profile
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.sql import SqlManagementClient

RESOURCE_GROUP = 'YOUR_RESOURCE_GROUP_NAME'
LOCATION = 'eastus'  # example Azure availability zone, should match resource group
SQL_SERVER = 'yourvirtualsqlserver'
SQL_DB = 'YOUR_SQLDB_NAME'
USERNAME = 'YOUR_USERNAME'
PASSWORD = 'YOUR_PASSWORD'

# create resource client
resource_client = get_client_from_cli_profile(ResourceManagementClient)
# create resource group
resource_client.resource_groups.create_or_update(RESOURCE_GROUP, {'location': LOCATION})

sql_client = get_client_from_cli_profile(SqlManagementClient)

# Create a SQL server
server = sql_client.servers.create_or_update(
    RESOURCE_GROUP,
    SQL_SERVER,
    {
        'location': LOCATION,
        'version': '12.0', # Required for create
        'administrator_login': USERNAME, # Required for create
        'administrator_login_password': PASSWORD # Required for create
    }
)

# Create a SQL database in the Basic tier
database = sql_client.databases.create_or_update(
    RESOURCE_GROUP,
    SQL_SERVER,
    SQL_DB,
    {
        'location': LOCATION,
        'collation': 'SQL_Latin1_General_CP1_CI_AS',
        'create_mode': 'default',
        'requested_service_objective_name': 'Basic'
    }
)

# Open access to this server for IPs
firewall_rule = sql_client.firewall_rules.create_or_update(
    RESOURCE_GROUP,
    SQL_DB,
    "firewall_rule_name_123.123.123.123",
    "123.123.123.123", # Start ip range
    "167.220.0.235"  # End ip range
)