快速入门:使用 Python 查询 Azure SQL 数据库中的数据库或 Azure SQL 托管实例Quickstart: Use Python to query a database in Azure SQL Database or Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

在本快速入门中,你将使用 Python 连接到 Azure SQL 数据库或 Azure SQL 托管实例,并使用 T-SQL 语句来查询数据。In this quickstart, you use Python to connect to Azure SQL Database or Azure SQL Managed Instance, and use T-SQL statements to query data.

先决条件Prerequisites

若要完成本快速入门,你需要:To complete this quickstart, you need:


重要

本文中脚本的编写目的是使用 Adventure Works 数据库。The scripts in this article are written to use the Adventure Works database.

备注

可以选择使用 Azure SQL 托管实例。You can optionally choose to use an Azure SQL Managed Instance.

若要执行创建和配置操作,请使用 Azure 门户PowerShellCLI,然后设置本地VM 连接性。To create and configure, use the Azure portal, PowerShell, or the CLI, and then set up on-premises or VM connectivity.

若要加载数据,请参阅通过 BACPAC 进行还原(使用 Adventure Works 文件),或参阅还原 Wide World Importers 数据库To load data, see restore with BACPAC with the Adventure Works file, or see restore the Wide World Importers database.

若要进一步了解 Python 和 Azure SQL 数据库中的数据库,请参阅适用于 Python 的 Azure SQL 数据库库pyodbc 存储库pyodbc 示例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.

获取服务器连接信息Get server connection information

获取连接到 Azure SQL 数据库中的数据库所需的连接信息。Get the connection information you need to connect to the database in Azure SQL Database. 在后续过程中,将需要完全限定的服务器名称或主机名称、数据库名称和登录信息。You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. 登录 Azure 门户Sign in to the Azure portal.

  2. 转到“SQL 数据库”或“SQL 托管实例”页 。Go to the SQL Databases or SQL Managed Instances page.

  3. 在“概述”页上,在“Server 名称”旁查看 Azure SQL 数据库中的数据库的完全限定服务器名称,或在“Host”旁边查看 Azure VM 上 Azure SQL 托管实例中或 SQL Server 的完全限定服务器名称(或 IP 地址) 。On the Overview page, review the fully qualified server name next to Server name for the database in Azure SQL Database or the fully qualified server name (or IP address) next to Host for an Azure SQL Managed Instance or SQL Server on Azure VM. 若要复制服务器名称或主机名称,请将鼠标悬停在其上方,然后选择“复制”图标。To copy the server name or host name, hover over it and select the Copy icon.

备注

有关 Azure VM 上的 SQL Server 的连接信息,请参阅连接到 SQL Server 实例For connection information for SQL Server on Azure VM, see Connect to a SQL Server instance.

创建用于查询数据库的代码Create code to query your database

  1. 在文本编辑器中,创建新文件 sqltest.py。In a text editor, create a new file named sqltest.py.

  2. 添加以下代码。Add the following code. 将 <server>、<database>、<username> 和 <password> 的值替换为自己的值。Substitute your own values for <server>, <database>, <username>, and <password>.

    重要

    本示例中的代码使用示例 AdventureWorksLT 数据,在创建数据库时可以选择该数据作为源。The code in this example uses the sample AdventureWorksLT data, which you can choose as source when creating your database. 如果数据库有不同数据,请在 SELECT 查询中使用自己数据库中的表。If your database has different data, use tables from your own database in the SELECT query.

    import pyodbc
    server = '<server>.database.chinacloudapi.cn'
    database = '<database>'
    username = '<username>'
    password = '<password>'   
    driver= '{ODBC Driver 17 for SQL Server}'
    
    with pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
        with conn.cursor() as 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. 确认已返回“类别/产品”的前 20 行,然后关闭命令窗口。Verify that the top 20 Category/Product rows are returned, and then close the command window.

后续步骤Next steps