適用於 Python 的 Azure SQL Database 程式庫Azure SQL Database libraries for Python

發行項
07/03/2019

概觀Overview

透過 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 驅動程式和 pyodbcInstall 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 DatabaseConnect 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()

pyodbc 範例pyodbc sample

連線至 ORMConnecting to ORMs

pyodbc 可搭配其他 ORM，例如 SQLAlchemy 和 Django。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
)

探索管理 APIExplore the Management APIs

共用方式為