Quickstart: Use Python to query a database in Azure SQL Database or Azure SQL Managed Instance
Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
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.
To complete this quickstart, you need:
An Azure account with an active subscription. Create an account for free.
A database where you'll run a query.
You can use one of these quickstarts to create and then configure a database:
Action SQL Database SQL Managed Instance SQL Server on Azure VM Azure Synapse Analytics Create Portal Portal Portal Portal CLI CLI PowerShell PowerShell PowerShell PowerShell Deployment template Deployment template Deployment template Deployment template Configure Server-level IP firewall rule Connectivity from a VM Connectivity settings Connectivity from on-premises Connect to a SQL Server instance Get connection information Azure SQL Azure SQL SQL VM Synapse SQL Python 3 and related software
Action macOS Linux Windows Install the ODBC driver, sqlcmd, and the Python driver for SQL Server Configure an environment for pyodbc Python development 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 Windows
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.
In a text editor, create a new file named sqltest.py.
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()
At a command prompt, run the following command:
python sqltest.py
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.
- Verify that the ODBC driver you installed is the same version as the
driver
variable in the code above. For example, the code shows 17, but you may have installed a different version. - 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.