您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

快速入门:使用 Python 查询 Azure SQL 数据库Quickstart: Use Python to query an Azure SQL database

本快速入门演示了如何使用 Python 连接到 Azure SQL 数据库,然后使用 Transact-SQL 语句查询数据。This quickstart demonstrates how to use Python to connect to an Azure SQL database and use Transact-SQL statements to query data. 如需进一步的 SDK 详细信息,请签出参考文档、pyodbc 示例以及 pyodbc GitHub 存储库。For further sdk details, checkout our reference documentation, a pyodbc sample, and the pyodbc GitHub repository.

先决条件Prerequisites

若要完成本快速入门,请确保符合以下条件:To complete this quickstart, make sure you have the following:

  • Azure SQL 数据库。An Azure SQL database. 可以使用以下方法之一来创建数据库:You can use one of these techniques to create a database:

  • 针对用于本快速入门的计算机的公共 IP 地址制定服务器级防火墙规则A server-level firewall rule for the public IP address of the computer you use for this quickstart.

  • 已为操作系统安装 Python 和相关软件:You have installed Python and related software for your operating system:

    • MacOS:安装 Homebrew 和 Python,接着安装 ODBC 驱动程序和 SQLCMD,再安装 Python Driver for SQL Server。MacOS: Install Homebrew and Python, install the ODBC driver and SQLCMD, and then install the Python Driver for SQL Server. 请参阅步骤 1.2、1.3 和 2.1See Steps 1.2, 1.3, and 2.1.
    • Ubuntu:安装 Python 和其他所需包,然后安装 Python Driver for SQL Server。Ubuntu: Install Python and other required packages, and then install the Python Driver for SQL Server. 请参阅步骤 1.2、1.3 和 2.1See Steps 1.2, 1.3, and 2.1.
    • Windows:安装最新版的 Python(现已为你配置环境变量),安装 ODBC 驱动程序和 SQLCMD,然后安装 Python Driver for SQL Server。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. 请参阅步骤 1.2、1.3 和 2.1See Step 1.2, 1.3, and 2.1.

SQL Server 连接信息SQL server connection information

获取连接到 Azure SQL 数据库所需的连接信息。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. 登录到 Azure 门户Sign in to the Azure portal.

  2. 从左侧菜单中选择“SQL 数据库”,并单击“SQL 数据库”页上的数据库。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. 如果忘了服务器的登录信息,可以单击“服务器名称”以导航到“SQL 服务器”页并查看服务器管理员名称。If you forget your server login information, you can click on the Server name to navigate to the SQL server page and view the Server admin name. 根据需要选择“重置密码”。If necessary, select Reset password.

插入用于查询 SQL 数据库的代码Insert code to query SQL database

  1. 在喜欢的文本编辑器中,创建新文件 sqltest.py。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+';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 the command prompt, run the following commands:

    python sqltest.py
    
  2. 验证是否已返回前 20 行,然后关闭应用程序窗口。Verify that the top 20 rows are returned and then close the application window.

后续步骤Next steps