How to Establish a Connection to Azure SQL Database Using Python

Azure SQL Database is a fully-managed, cloud-based relational database service from Microsoft Azure. It simplifies the process of setting up, managing, and scaling a SQL database by running on Azure’s secure and reliable cloud platform. This powerful service combines the capabilities of Microsoft SQL Server with the benefits of cloud technology, offering high availability, scalability, and robust security features. Azure SQL Database is designed to handle a wide range of database workloads, making it an ideal choice for everything from small applications to large enterprise systems.

To connect to an Azure SQL Database using Python, you can use the pyodbc or sqlalchemy libraries. You’ll need to follow these steps:

1. Using pyodbc

Step 1: Install pyodbc
Install the pyodbc package with pip:

pip install pyodbc

If you want to view data in a DataFrame, also install Pandas:.

pip install pandas

Step 2: Set Up the Connection

Use the following Python code to establish a connection with your Azure SQL Database:

import pyodbc

import pandas as pd

# Define your Azure SQL database connection details
server = “your_server_name.database.windows.net”
database = “your_database_name”
username = “your_username”
password = “your_password”
port = “1433”
driver = “{ODBC Driver 17 for SQL Server}”

# Create the connection
connection_string = f”DRIVER={driver};SERVER={server};PORT={port};DATABASE={database};UID={username};PWD={password}”
conn = pyodbc.connect(connection_string)

# Store your SQL query in a variable 
sqlQuery = "SELECT * FROM your_table_name"

# Execute the SQL query 
conn.execute(sqlQuery)

# View the data in a Pandas DataFrame
data = pd.read_Sql(sqlQuery, conn)
print(data)

# Close the connection
conn.close()

2. Using SQLAlchemy

Step 1: Install SQLAlchemy and pyodbc

Install both packages using pip:

pip install sqlalchemy pyodbc

Step 2: Set Up the Connection

Here is how you can connect using SQLAlchemy:

from sqlalchemy import create_engine

# Define your Azure SQL database connection details
server = “your_server_name.database.windows.net”
database = “your_database_name”
username = “your_username”
password = “your_password”
port = “1433”

# Create the engine
engine= create_engine(f'mssql+pyodbc://{username}:{password}@{server}:port/{database}?driver=ODBC+Driver+17+for+SQL+Server')

# Test the connection
with engine.connect() as connection:
data = connection.execute("SELECT @@VERSION")
for row in data:
print(row)

Note:

• Replace your_server_name, your_database_name, your_username, and your_password with your actual Azure SQL Database credentials.
• Make sure your Azure SQL Database is configured to allow connections from your IP address.
• If you’re behind a firewall or proxy, you might need to adjust your settings to enable the connection.
  Using these methods will help you successfully connect to Azure SQL Database with Python.

Conclusion

In summary, to connect Python to Azure SQL Database, use libraries such as pyodbc or SQLAlchemy. Enter your database credentials, and then you can store and run SQL queries to easily manage and analyze your data.