Librerie del database SQL di Azure per PythonAzure SQL Database libraries for Python

Articolo
07/03/2019

PanoramicaOverview

Usare i dati archiviati nel database SQL di Azure da Python con il driver di database ODBC pyodbc.Work with data stored in Azure SQL Database from Python with the pyodbc ODBC database driver. Visualizzare la guida introduttiva sulla connessione a un database SQL di Azure e sull'uso di istruzioni Transact-SQL per eseguire query sui dati e ottenere un esempio introduttivo con 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.

Installare il driver ODBC e pyodbcInstall ODBC driver and pyodbc

pip install pyodbc

Altri dettagli sull'installazione delle librerie di comunicazione Python e dei database.More details about installing the python and database communication libraries.

Connettersi ed eseguire una query SQLConnect and execute a SQL query

Connettersi al database SQLConnect 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()

Eseguire una query SQLExecute 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()

Esempio di pyodbcpyodbc sample

Connessione a soluzioni ORMConnecting to ORMs

pyodbc interagisce con altre soluzioni ORM, ad esempio SQLAlchemy e Django.pyodbc works with other ORMs such as SQLAlchemy and Django.

API di gestioneManagement API

Creare e gestire le risorse del database SQL di Azure nella sottoscrizione con l'API di gestione.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

EsempioExample

Creare una risorsa del database SQL e limitare l'accesso a un intervallo di indirizzi IP usando una regola del firewall.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
)

Esplorare le API di gestioneExplore the Management APIs

Condividi tramite