Skip to content

connect-query-python.md

Latest commit

 

History

History
227 lines (149 loc) · 9.29 KB

connect-query-python.md

File metadata and controls

227 lines (149 loc) · 9.29 KB
title Use Python to Query a Database
titleSuffix Azure SQL Database & Azure SQL Managed Instance
description This article shows you how to use Python to create a program that connects to a database in Azure SQL Database and query it using Transact-SQL statements.
author dlevy-msft-sql
ms.author dlevy
ms.reviewer wiassaf, mathoma, randolphwest
ms.date 09/10/2025
ms.service azure-sql
ms.subservice connect
ms.topic quickstart
ms.custom
sqldbrb=2
devx-track-python
mode-api
py-fresh-zinc
sfi-ropc-nochange
ms.devlang python
monikerRange =azuresql || =azuresql-db || =azuresql-mi

Quickstart: Use Python to query a database in Azure SQL Database or Azure SQL Managed Instance

[!INCLUDE appliesto-sqldb-sqlmi]

In this quickstart, you use Python to connect to Azure SQL Database, Azure SQL Managed Instance, or Synapse SQL database and use T-SQL statements to query data.

mssql-python documentation | mssql-python source code | Package (PyPI)

Prerequisites

To complete this quickstart, you need:

Setting up

Follow these steps to configure your development environment to develop an application using the mssql-python Python driver.

Note

This driver uses the Tabular Data Stream (TDS) protocol, which is enabled by default in SQL Server, SQL database in Fabric and Azure SQL Database. No extra configuration is required.

Install the mssql-python package

Get the mssql-python package from PyPI.

  1. Open a command prompt in an empty directory.

  2. Install the mssql-python package.

    Windows

    pip install mssql-python

    Linux

    sudo apt-get -y install libltdl7
pip install mssql-python

    macOS

    brew install openssl
pip install mssql-python

Install python-dotenv package

Get the python-dotenv from PyPI.

  1. In the same directory, install the python-dotenv package.

    pip install python-dotenv

Check installed packages

You can use the PyPI command-line tool to verify that your intended packages are installed.

  1. Check the list of installed packages with pip list.

    pip list

Create new files

  1. In the current directory, create a new file named .env.

  2. Within the .env file, add an entry for your connection string named SQL_CONNECTION_STRING. Replace the <database-server-name> and <database-name> placeholders with your own values.

    The mssql-python driver has built-in support for Microsoft Entra authentication. Use the Authentication parameter to specify the authentication method.

    ActiveDirectoryDefault (Recommended)

    ActiveDirectoryDefault automatically discovers credentials from multiple sources without requiring interactive login. This is the recommended option for local development and works on Windows, macOS, and Linux.

    For the most reliable local development experience, sign in with Azure CLI first:

    az login

    Then use this connection string format in your .env file:

    SQL_CONNECTION_STRING="Server=<database-server-name>.database.windows.net;Database=<database-name>;Authentication=ActiveDirectoryDefault;Encrypt=yes;TrustServerCertificate=no"

    ActiveDirectoryDefault evaluates credentials in the following order:

    1. Environment variables (for service principal credentials)
    2. Managed identity (when running on Azure)
    3. Azure CLI (from az login)
    4. Visual Studio (Windows only)
    5. Azure PowerShell (from Connect-AzAccount)

    [!TIP] For production applications, use the specific authentication method for your scenario to avoid credential discovery latency:

    • Azure App Service/Functions: Use ActiveDirectoryMSI (managed identity)
    • Interactive user login: Use ActiveDirectoryInteractive
    • Service principal: Use ActiveDirectoryServicePrincipal

    Interactive Authentication

    Microsoft Entra Interactive Authentication uses multifactor authentication technology to set up a connection. In this mode, an Azure Authentication dialog appears and lets you enter your credentials to complete the connection.

    SQL_CONNECTION_STRING="Server=<database-server-name>.database.windows.net;Database=<database-name>;Authentication=ActiveDirectoryInteractive;Encrypt=yes;TrustServerCertificate=no"

    [!NOTE] On macOS, both ActiveDirectoryInteractive and ActiveDirectoryDefault work for Microsoft Entra authentication. ActiveDirectoryInteractive prompts you to sign in every time you run the script. To avoid repeated sign-in prompts, log in once via the Azure CLI by running az login, then use ActiveDirectoryDefault, which reuses the cached credential.

    SQL Authentication

    You can authenticate directly to a SQL Server instance using a username and password.

    SQL_CONNECTION_STRING="Server=<database-server-name>.database.windows.net;Database=<database-name>;UID=<user-name>;PWD=<user-password>;Encrypt=yes;TrustServerCertificate=no"

    [!WARNING] Use caution when managing connection strings that contain secrets such as usernames, passwords, or access keys. These secrets shouldn't be committed to source control or placed in unsecure locations where they might be accessed by unintended users. Add .env to your .gitignore file to prevent accidentally committing secrets.

    Fabric SQL Database

    To connect to a SQL database in Microsoft Fabric, use the same authentication methods. The server name follows the Fabric format.

    On Windows domain-joined machines, use ActiveDirectoryIntegrated for seamless authentication with no extra steps:

    SQL_CONNECTION_STRING="Server=<workspace-guid>.database.fabric.microsoft.com,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryIntegrated"

    On macOS, Linux, or non-domain Windows, use ActiveDirectoryDefault after signing in with Azure CLI (az login):

    SQL_CONNECTION_STRING="Server=<workspace-guid>.database.fabric.microsoft.com,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Authentication=ActiveDirectoryDefault"

    You can find your Fabric SQL database connection string in the Fabric portal under your database's settings.

    [!TIP]
    The connection string used here largely depends on the type of SQL database you're connecting to. For more information on connection strings and their syntax, see DSN and Connection String Keywords and Attributes.

  3. In a text editor, create a new file named sqltest.py.

  4. Add the following code.

    from os import getenv
from dotenv import load_dotenv
from mssql_python import connect

load_dotenv()

with connect(getenv("SQL_CONNECTION_STRING")) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        rows = cursor.fetchall()
        for row in rows:
            print(row.name, row.collation_name)

Run the code

  1. At a command prompt, run the following command:

    python sqltest.py

  2. Verify that the databases and their collations are returned, and then close the command window.

    If you receive an error:

    • Verify that the server name, database name, username, and password you're using are correct.

    • If you're running the code from a local environment, verify that the firewall of the Azure resource you're trying to access is configured to allow access from your environment's IP address.

Related content