快速入門:使用 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 GitHub 存放庫,及 pyodbc 範例For further SDK details, check out our reference documentation, the pyodbc GitHub repository, and a pyodbc sample.

必要條件Prerequisites

若要完成本快速入門,請確定您具備下列項目︰To complete this quickstart, make sure you have the following:

取得 SQL Server 連線資訊Get SQL server connection information

取得連線到 Azure SQL 資料庫所需的連線資訊。Get the connection information you need to connect to the 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 受控執行個體] 頁面。Navigate to the SQL databases or SQL managed instances page.

  3. 在 [概觀] 頁面上,針對單一資料庫檢閱 [伺服器名稱] 旁的完整伺服器名稱,若為受控執行個體,則檢閱 [主機] 旁的完整伺服器名稱。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要複製伺服器名稱或主機名稱,請將滑鼠暫留在其上方,然後選取 [複製] 圖示。To copy the server name or host name, hover over it and select the Copy icon.

建立程式碼以查詢您的 SQL DatabaseCreate code to query your SQL 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.windows.net'
    database = '<database>'
    username = '<username>'
    password = '<password>'
    driver= '{ODBC Driver 17 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 a command prompt, run the following command:

    python sqltest.py
    
  2. 請確認傳回前 20 個的類別/產品資料列,然後關閉命令視窗。Verify that the top 20 Category/Product rows are returned, then close the command window.

後續步驟Next steps