快速入門：使用 Python 連線至 [適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器] 並查詢其資料

發行項
08/15/2024

適用範圍：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

在本快速入門中，您會使用 Python 連線到適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體。接著，您可以使用 SQL 陳述式來查詢、插入、更新和刪除 macOS、Ubuntu Linux 和 Windows 平台中的資料庫所含的資料。

本文中的這些步驟包含兩種驗證方法：Microsoft Entra 驗證和 PostgreSQL 驗證。 [無密碼] 索引標籤會顯示 Microsoft Entra 驗證，而 [密碼] 索引標籤則會顯示 PostgreSQL 驗證。

Microsoft Entra 驗證是一種機制，會使用 Microsoft Entra ID 中所定義的身分識別以連線到適用於 PostgreSQL 的 Azure 資料庫。透過 Microsoft Entra 驗證，您可以在集中的位置管理資料庫使用者的身分識別和其他 Microsoft 服務，從而簡化權限管理。若要深入了解，請參閱 Microsoft Entra 驗證搭配適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器。

PostgreSQL 驗證會使用儲存在 PostgreSQL 中的帳戶。如果您選擇使用密碼作為帳戶的認證，則這些認證會儲存在 user 資料表中。由於這些密碼會儲存在 PostgreSQL 中，因此您必須自行管理密碼的輪替。

本文假設您已熟悉使用 Python 開發，但不熟悉適用於 PostgreSQL 的 Azure 資料庫彈性伺服器。

必要條件

具有有效訂用帳戶的 Azure 帳戶。免費建立帳戶。
適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體。若要建立適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體，請參閱使用 Azure 入口網站建立適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器。
Python 3.8+。
最新 pip 套件安裝程式。

新增用戶端工作站的防火牆規則

如果您使用私人存取 (VNet 整合) 建立適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體，就必須從與伺服器相同的 VNet 中的資源連線到伺服器。您可以建立虛擬機器，並新增至使用您適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體建立的 VNet。請參閱使用 Azure CLI 建立和管理適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器虛擬網路。
如果您使用公用存取 (允許的 IP 位址)建立適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體，可以將本機 IP 位址新增至伺服器上的防火牆規則清單。請參閱使用 Azure CLI 建立和管理適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器防火牆規則。

在伺服器上設定 Microsoft Entra 整合 (僅限無密碼)

如果您遵循無密碼驗證的步驟，必須為您的伺服器執行個體設定 Microsoft Entra 驗證，而且您必須指派為伺服器執行個體上的 Microsoft Entra 系統管理員。請遵循設定 Microsoft Entra 整合中的步驟，確保已設定 Microsoft Entra 驗證，且您已獲指派為伺服器執行個體上的 Microsoft Entra 系統管理員。

準備您的開發環境

變更為您要執行程式碼的資料夾，並建立和啟用虛擬環境。虛擬環境是特定 Python 版本的獨立式目錄，以及該應用程式所需的其他套件。

執行下列命令來建立並啟用虛擬環境：

Windows
macOS/Linux

py -3 -m venv .venv
.venv\Scripts\activate

python3 -m venv .venv
source .venv/bin/activate

安裝 Python 程式庫

安裝執行程式碼範例所需的 Python 程式庫。

無密碼 (建議)
密碼

安裝 azure 身分識別連結庫，此連結庫提供跨 Azure SDK Microsoft Entra 令牌驗證支援。

pip install azure-identity

安裝 psycopg 模組，以連線及查詢 PostgreSQL 資料庫。

pip install psycopg

新增驗證碼

在本節中，您會將驗證程式碼新增至工作目錄，並使用伺服器執行個體執行驗證和授權所需的任何額外步驟。

無密碼 (建議)
密碼

將下列程式碼複製到編輯器中，並將它儲存在名為 get_conn.py 的檔案中。

import urllib.parse
import os

from azure.identity import DefaultAzureCredential

# IMPORTANT! This code is for demonstration purposes only. It's not suitable for use in production. 
# For example, tokens issued by Microsoft Entra ID have a limited lifetime (24 hours by default). 
# In production code, you need to implement a token refresh policy.

def get_connection_uri():

    # Read URI parameters from the environment
    dbhost = os.environ['DBHOST']
    dbname = os.environ['DBNAME']
    dbuser = urllib.parse.quote(os.environ['DBUSER'])
    sslmode = os.environ['SSLMODE']

    # Use passwordless authentication via DefaultAzureCredential.
    # IMPORTANT! This code is for demonstration purposes only. DefaultAzureCredential() is invoked on every call.
    # In practice, it's better to persist the credential across calls and reuse it so you can take advantage of token
    # caching and minimize round trips to the identity provider. To learn more, see:
    # https://github.com/Azure/azure-sdk-for-python/blob/main/sdk/identity/azure-identity/TOKEN_CACHING.md 
    credential = DefaultAzureCredential()

    # Call get_token() to get a token from Microsft Entra ID and add it as the password in the URI.
    # Note the requested scope parameter in the call to get_token, "https://ossrdbms-aad.database.windows.net/.default".
    password = credential.get_token("https://ossrdbms-aad.database.windows.net/.default").token

    db_uri = f"postgresql://{dbuser}:{password}@{dbhost}/{dbname}?sslmode={sslmode}"
    return db_uri

取得資料庫連線資訊。
1. 在 Azure 入口網站中，搜尋並選取適用於 PostgreSQL 的 Azure 資料庫彈性伺服器名稱。
2. 在伺服器的 [概觀] 頁面上，複製完整的伺服器名稱。完整的伺服器名稱格式一律為 <my-server-name>.postgres.database.azure.com。
3. 在左側功能表的 [安全性] 底下，選取 [驗證]。請確定您的帳戶列在 [Microsoft Entra 管理員] 底下。如果不是，請完成在伺服器上設定 Microsoft Entra 整合 (僅限無密碼) 中的步驟。
設定連線 URI 元素的環境變數：
- Windows
- macOS/Linux
```
set DBHOST=<server-name>
set DBNAME=<database-name>
set DBUSER=<username>
set SSLMODE=require
```
```
export DBHOST=<server-name>
export DBNAME=<database-name>
export DBUSER=<username>
export SSLMODE=require
```
取代命令中的下列預留位置值：
- <server-name>：替換為從 Azure 入口網站複製的值。
- <username>：替換為 Azure 使用者名稱；例如， john@contoso.com.
- <database-name>：替換為適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器資料庫的名稱。當您建立伺服器時，系統已自動建立名為 postgres 的預設資料庫。您可以使用 SQL 命令來使用該資料庫或建立新的資料庫。
在工作站上登入 Azure。您可以使用 Azure CLI、Azure PowerShell 或 Azure Developer CLI 登入。例如，若要透過 Azure CLI 登入，請輸入下列命令：
```
az login
```
驗證碼會使用 DefaultAzureCredential 向 Microsoft Entra ID 進行驗證，並取得授權您在伺服器執行個體上執行作業的權杖。 DefaultAzureCredential 支援一系列驗證認證類型。支援的認證包括用來登入開發人員工具的認證，例如 Azure CLI、Azure PowerShell 或 Azure Developer CLI。

將下列程式碼複製到編輯器中，並將它儲存在名為 get_conn.py 的檔案中。

import urllib.parse
import os

def get_connection_uri():

    # Read URI parameters from the environment
    dbhost = os.environ['DBHOST']
    dbname = os.environ['DBNAME']
    dbuser = urllib.parse.quote(os.environ['DBUSER'])
    password = os.environ['DBPASSWORD']
    sslmode = os.environ['SSLMODE']
    db_uri = f"host={dbhost} dbname={dbname} user={dbuser} password={password} sslmode ={sslmode}"
    # Construct connection URI
    return db_uri

取得資料庫連線資訊。
1. 在 Azure 入口網站中，搜尋並選取適用於 PostgreSQL 的 Azure 資料庫彈性伺服器名稱。
2. 在伺服器的 [概觀] 頁面上，複製完整的 [伺服器名稱] 和 [管理系統使用者登入名稱]。完整的伺服器名稱格式一律為 <my-server-name>.postgres.database.azure.com。
  
  也需要您的管理員密碼。如果您忘記了，可以使用 [概觀] 頁面頂端的 [重設密碼] 按鈕來重設。
設定連線 URI 元素的環境變數：
- Windows
- macOS/Linux
```
set DBHOST=<server-name>
set DBNAME=<database-name>
set DBUSER=<username>
set DBPASSWORD=<password>
set SSLMODE=require
```
```
export DBHOST=<server-name>
export DBNAME=<database-name>
export DBUSER=<username>
export DBPASSWORD=<password>
export SSLMODE=require
```
取代命令中的下列預留位置值：
- 將 <server-name> 和 <username> 取代為您從 Azure 入口網站中複製的值。
- 將 <password> 取代為伺服器密碼。
- <database-name>：替換為適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器資料庫的名稱。當您建立伺服器時，系統已自動建立名為 postgres 的預設資料庫。您可以使用 SQL 命令來使用該資料庫或建立新的資料庫。

如何執行 Python 範例

針對本文中的每個程式碼範例：

在文字編輯器中建立新的檔案。
將程式碼範例新增至檔案。
使用 .py 作為副檔名，將檔案儲存在您的專案資料夾中，例如 postgres-insert.py。針對 Windows，請務必在儲存檔案時選取 UTF-8 編碼。
在您的專案資料夾類型中，python 後面接著檔名，例如 python postgres-insert.py。

建立資料表及插入資料

下列程式碼範例會使用 psycopg.connect 函式來連線到適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫，並使用 SQL INSERT 陳述式來載入資料。 cursor.execute 函式會針對資料庫執行 SQL 查詢。

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print("Finished dropping table (if existed)")

# Create a table
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table")

# Insert some data into the table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
print("Inserted 3 rows of data")

# Clean up
conn.commit()
cursor.close()
conn.close()

程式碼會在成功執行後產生下列輸出：

Connection established
Finished dropping table (if existed)
Finished creating table
Inserted 3 rows of data

讀取資料

下列程式碼範例會連線到適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫，並使用 cursor.execute 搭配 SQL SELECT 陳述式來讀取資料。此函式會接受查詢並使用 cursor.fetchall() 傳回用於反覆查詢的結果集。

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Fetch all rows from table
cursor.execute("SELECT * FROM inventory;")
rows = cursor.fetchall()

# Print all rows
for row in rows:
    print("Data row = (%s, %s, %s)" %(str(row[0]), str(row[1]), str(row[2])))

# Cleanup
conn.commit()
cursor.close()
conn.close()

程式碼會在成功執行後產生下列輸出：

Connection established
Data row = (1, banana, 150)
Data row = (2, orange, 154)
Data row = (3, apple, 100)

更新資料

下列程式碼範例會連線到適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫，並使用 cursor.execute 搭配 SQL UPDATE 陳述式來更新資料。

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Update a data row in the table
cursor.execute("UPDATE inventory SET quantity = %s WHERE name = %s;", (200, "banana"))
print("Updated 1 row of data")

# Cleanup
conn.commit()
cursor.close()
conn.close()

刪除資料

下列程式碼範例會連線到適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫，並使用 cursor.execute 搭配 SQL DELETE 陳述式來刪除先前插入的清查項目。

import psycopg
from get_conn import get_connection_uri

conn_string = get_connection_uri()

conn = psycopg.connect(conn_string) 
print("Connection established")
cursor = conn.cursor()

# Delete data row from table
cursor.execute("DELETE FROM inventory WHERE name = %s;", ("orange",))
print("Deleted 1 row of data")

# Cleanup
conn.commit()
cursor.close()
conn.close()

下一步

使用傾印和還原遷移資料庫

共用方式為