Muokkaa

Jaa

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

  • Artikkeli

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.

Prerequisites

To complete this quickstart, you need:

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.

    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.

Next steps