connect-query-python.md

title	Use Python to query a database
titleSuffix	Azure SQL Database & SQL Managed Instance
description	This topic 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	dzsquared
ms.author	drskwier
ms.reviewer	wiassaf, mathoma
ms.date	12/19/2020
ms.service	sql-database
ms.subservice	connect
ms.topic	quickstart
ms.custom	seo-python-october2019 sqldbrb=2 devx-track-python mode-api
ms.devlang	python
monikerRange	= azuresql || = azuresql-db || = azuresql-mi

Quickstart: Use Python to query a database

[!INCLUDEappliesto-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.

Prerequisites

To complete this quickstart, you need:

• An Azure account with an active subscription. Create an account for free.

• A database where you will run a query.

  [!INCLUDEcreate-configure-database]

• Python 3 and related software

  Action	macOS	Ubuntu	Windows
  Install the ODBC driver, SQLCMD, and the Python driver for SQL Server	Use steps 1.2, 1.3, and 2.1 in create Python apps using SQL Server on macOS. This will also install install Homebrew and Python. Although the linked article references SQL Server, these steps are also applicable to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.	Configure an environment for pyodbc Python development	Configure an environment for pyodbc Python development.
  Install Python and other required packages		Use sudo apt-get install python python-pip gcc g++ build-essential.
  Further information	Microsoft ODBC driver on macOS	Microsoft ODBC driver on Linux	Microsoft ODBC driver on Linux

To further explore Python and the database in Azure SQL Database, see Azure SQL Database libraries for Python, the pyodbc repository, and a pyodbc sample.

Create code to query your database

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

2. Add the following code. Get the connection information from the prerequisites section and substitute your own values for <server>, <database>, <username>, and <password>.

   import pyodbc
   server = '<server>.database.windows.net'
   database = '<database>'
   username = '<username>'
   password = '{<password>}'   
   driver= '{ODBC Driver 17 for SQL Server}'
   
   with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
       with conn.cursor() as cursor:
           cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
           row = cursor.fetchone()
           while row:
               print (str(row[0]) + " " + str(row[1]))
               row = cursor.fetchone()

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.

Next steps

• Design your first database in Azure SQL Database
• Microsoft Python drivers for SQL Server
• Python developer center