Quickstart: Use Python to query an Azure SQL database

• 03/25/2019
• 2 minutes to read
• Contributors
  • 
  • 
  • 
  • 
  • 
  • all

This quickstart demonstrates how to use Python to connect to an Azure SQL database and use Transact-SQL statements to query data. For further SDK details, check out our reference documentation, the pyodbc GitHub repository, and a pyodbc sample.

Prerequisites

To complete this quickstart, make sure you have the following:

• An Azure SQL database. You can use one of these quickstarts to create and then configure a database in Azure SQL Database:

  	Single database	Managed instance
  Create	Portal	Portal
  	CLI	CLI
  	PowerShell	PowerShell
  Configure	Server-level IP firewall rule	Connectivity from a VM
  		Connectivity from on-site
  Load data	Adventure Works loaded per quickstart	Restore Wide World Importers
  		Restore or import Adventure Works from BACPAC file from GitHub

  Important

  The scripts in this article are written to use the Adventure Works database. With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.

• Python and related software for your operating system:

  • MacOS: Install Homebrew and Python, install the ODBC driver and SQLCMD, and then install the Python driver for SQL Server. See Steps 1.2, 1.3, and 2.1 in Create Python apps using SQL Server on macOS. For more information, see Install the Microsoft ODBC Driver on Linux and macOS.

  • Ubuntu: Install Python and other required packages with sudo apt-get install python python-pip gcc g++ build-essential. Download and install the ODBC driver, SQLCMD, and the Python driver for SQL Server. For instructions, see Configure a development environment for pyodbc Python development.

  • Windows: Install Python, the ODBC driver and SQLCMD, and the Python driver for SQL Server. For instructions, see Configure a development environment for pyodbc Python development.

Get SQL server connection information

Get the connection information you need to connect to the Azure SQL database. You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

1. Sign in to the Azure portal.

2. Navigate to the SQL databases or SQL managed instances page.

3. On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. To copy the server name or host name, hover over it and select the Copy icon.

Create code to query your SQL database

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

2. Add the following code. Substitute your own values for <server>, <database>, <username>, and <password>.

   Important

   The code in this example uses the sample AdventureWorksLT data, which you can choose as source when creating your database. If your database has different data, use tables from your own database in the SELECT query.

   import pyodbc
   server = '<server>.database.windows.net'
   database = '<database>'
   username = '<username>'
   password = '<password>'
   driver= '{ODBC Driver 17 for SQL Server}'
   cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;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()
   

Run the code

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

   python sqltest.py
   

2. Verify that the top 20 Category/Product rows are returned, then close the command window.

Next steps

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