Azure SQL Database libraries for Python
Work with data stored in Azure SQL Database from Python with the pyodbc ODBC database driver. View our quickstart on connecting to an Azure SQL Database and using Transact-SQL statements to query data and getting started sample with pyodbc.
pip install pyodbc
More details about installing the python and database communication libraries.
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()
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 works with other ORMs such as SQLAlchemy and Django.
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
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
)
Azure SDK for Python feedback
Azure SDK for Python is an open source project. Select a link to provide feedback: