Use Python to query an Azure SQL database

2 min to read Contributors
  • Carl Rabeler
  • Kris Crider
  • Jennifer Hubbard

This quick start demonstrates how to use Python to connect to an Azure SQL database and use Transact-SQL statements to query data.

Prerequisites

To complete this quick start tutorial, make sure you have the following:

  • An Azure SQL database. This quick start uses the resources created in one of these quick starts:

  • A server-level firewall rule for the public IP address of the computer you use for this quick start tutorial.

  • You have installed 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 Step 1.2, 1.3, and 2.1.
    • Ubuntu: Install Python and other required packages, and then install the Python Driver for SQL Server. See Step 1.2 and 2.1.
    • Windows: Install the newest version of Python (environment variable is now configured for you), install the ODBC driver and SQLCMD, and then install the Python Driver for SQL Server. See Step 1.2, 1.3, and 2.1.

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.

  3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.

    server-name

  4. If you forget your server login information, navigate to the SQL Database server page to view the server admin name and, if necessary, reset the password.

Insert code to query SQL database

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

  2. Replace the contents with the following code and add the appropriate values for your server, database, user, and password.

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()

Run the code

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

    python sqltest.py

  2. Verify that the top 20 rows are returned and then close the application window.

Next steps