Bagikan melalui

Menyambungkan dan mengkueri Azure SQL Database menggunakan Python dan driver pyodbc

  • Artikel

Berlaku untuk: Azure SQL Database

Mulai cepat ini menjelaskan cara menyambungkan aplikasi ke database di Azure SQL Database dan melakukan kueri menggunakan Python dan Driver Python SQL - pyodbc. Mulai cepat ini mengikuti pendekatan tanpa kata sandi yang direkomendasikan untuk menyambungkan ke database. Anda dapat mempelajari lebih lanjut tentang koneksi tanpa kata sandi di hub tanpa kata sandi.

Prasyarat

  • Langganan Azure.
  • Database Azure SQL yang dikonfigurasi dengan autentikasi Microsoft Entra. Anda bisa membuatnya menggunakan mulai cepat Buat database.
  • Versi terbaru dari Azure CLI.
  • Visual Studio Code dengan ekstensi Python.
  • Python 3.8 atau yang lebih baru. Jika Anda menggunakan komputer klien Linux, lihat Menginstal driver ODBC.

Mengonfigurasi database

Koneksi aman tanpa kata sandi ke Azure SQL Database memerlukan konfigurasi database tertentu. Verifikasi pengaturan berikut di server logis Anda di Azure untuk menyambungkan dengan benar ke Azure SQL Database di lingkungan lokal dan yang dihosting:

  1. Untuk koneksi pengembangan lokal, pastikan server logis Anda dikonfigurasi untuk memungkinkan alamat IP komputer lokal Anda dan layanan Azure lainnya tersambung:

    • Navigasi ke halaman Jaringan server Anda.

    • Alihkan tombol radio Jaringan terpilih untuk menampilkan opsi konfigurasi tambahan.

    • Pilih Tambahkan alamat IPv4 klien Anda(xx.xx.xx.xx) untuk menambahkan aturan firewall yang akan mengaktifkan koneksi dari alamat IPv4 komputer lokal Anda. Atau, Anda juga dapat memilih + Tambahkan aturan firewall untuk memasukkan alamat IP tertentu pilihan Anda.

    • Pastikan kotak centang Izinkan layanan dan sumber daya Azure untuk mengakses server ini dipilih.

      Cuplikan layar memperlihatkan cara mengonfigurasi aturan firewall.

      Peringatan

      Mengaktifkan pengaturan Izinkan layanan dan sumber daya Azure untuk mengakses server ini bukanlah praktik keamanan yang direkomendasikan untuk skenario produksi. Aplikasi nyata harus menerapkan pendekatan yang lebih aman, seperti pembatasan firewall yang lebih kuat atau konfigurasi jaringan virtual.

      Anda bisa membaca selengkapnya tentang konfigurasi keamanan database pada sumber daya berikut:

  2. Server juga harus mengaktifkan autentikasi Microsoft Entra dan memiliki akun admin Microsoft Entra yang ditetapkan. Untuk koneksi pengembangan lokal, akun admin Microsoft Entra harus menjadi akun yang juga dapat Anda masuk ke Visual Studio atau Azure CLI dengan secara lokal. Anda dapat memverifikasi apakah server Anda mengaktifkan autentikasi Microsoft Entra di halaman ID Microsoft Entra server logis Anda.

    Cuplikan layar memperlihatkan cara mengaktifkan autentikasi Microsoft Entra.

  3. Jika Anda menggunakan akun Azure pribadi, pastikan Anda memiliki penyiapan Microsoft Entra dan dikonfigurasi untuk Azure SQL Database untuk menetapkan akun Anda sebagai admin server. Jika Anda menggunakan akun perusahaan, ID Microsoft Entra kemungkinan besar sudah dikonfigurasi untuk Anda.

Membuat proyek

Buat proyek Python baru menggunakan Visual Studio Code.

  1. Buka Visual Studio Code dan buat folder baru untuk proyek Anda dan ubah direktori ke dalamnya.

    mkdir python-sql-azure
cd python-sql-azure

  2. Buat lingkungan virtual untuk aplikasi.

    py -m venv .venv
.venv\scripts\activate

  3. Buat file Python baru yang disebut app.py.

Menginstal driver pyodbc

Untuk menyambungkan ke Azure SQL Database menggunakan Python, instal pyodbc driver. Paket ini bertindak sebagai penyedia data untuk menyambungkan ke database, menjalankan perintah, dan mengambil hasil. Dalam mulai cepat ini, Anda juga menginstal flaskpaket , uvicorn, dan pydantic untuk membuat dan menjalankan API.

Untuk detail dan instruksi khusus untuk menginstal pyodbc driver di semua sistem operasi, lihat Mengonfigurasi lingkungan pengembangan untuk pengembangan Pyodbc Python.

  1. Buat file requirements.txt dengan baris berikut:

    pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity

  2. Instal persyaratan.

    pip install -r requirements.txt

Mengonfigurasi string koneksi lokal

Untuk pengembangan lokal dan menyambungkan ke Azure SQL Database, tambahkan variabel lingkungan berikut AZURE_SQL_CONNECTIONSTRING . <database-server-name> Ganti tempat penampung dan <database-name> dengan nilai Anda sendiri. Contoh variabel lingkungan ditampilkan untuk shell Bash.

Autentikasi interaktif menyediakan opsi tanpa kata sandi saat Anda berjalan secara lokal.

Di Windows, Autentikasi Interaktif Microsoft Entra dapat menggunakan teknologi autentikasi multifaktor Microsoft Entra untuk menyiapkan koneksi. Dalam mode ini, dengan memberikan ID masuk, dialog Autentikasi Azure dipicu dan memungkinkan pengguna untuk memasukkan kata sandi untuk menyelesaikan koneksi.

export AZURE_SQL_CONNECTIONSTRING='Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

Untuk informasi selengkapnya, lihat Menggunakan ID Microsoft Entra dengan Driver ODBC. Jika Anda menggunakan opsi ini, cari jendela yang meminta kredensial kepada Anda.

Anda bisa mendapatkan detail untuk membuat string koneksi dari portal Azure:

  1. Buka Azure SQL Server, pilih halaman database SQL untuk menemukan nama database Anda, dan pilih database.

  2. Pada database, buka halaman String koneksi untuk mendapatkan informasi string koneksi. Lihat di bawah tab ODBC .

Catatan

Jika Anda telah menginstal Azure Arc dan mengaitkannya dengan langganan Azure, Anda juga dapat menggunakan pendekatan identitas terkelola yang ditampilkan untuk aplikasi yang disebarkan ke App Service.

Menambahkan kode untuk menyambungkan ke Azure SQL Database

Di folder proyek, buat file app.py dan tambahkan kode sampel. Kode ini membuat API yang:

  • Mengambil string koneksi Azure SQL Database dari variabel lingkungan.
  • Persons Membuat tabel dalam database selama startup (hanya untuk skenario pengujian).
  • Menentukan fungsi untuk mengambil semua Person rekaman dari database.
  • Menentukan fungsi untuk mengambil satu Person rekaman dari database.
  • Menentukan fungsi untuk menambahkan rekaman baru Person ke database.
import os
import pyodbc, struct
from azure import identity

from typing import Union
from fastapi import FastAPI
from pydantic import BaseModel

class Person(BaseModel):
    first_name: str
    last_name: Union[str, None] = None
    
connection_string = os.environ["AZURE_SQL_CONNECTIONSTRING"]

app = FastAPI()

@app.get("/")
def root():
    print("Root of Person API")
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Table should be created ahead of time in production app.
        cursor.execute("""
            CREATE TABLE Persons (
                ID int NOT NULL PRIMARY KEY IDENTITY,
                FirstName varchar(255),
                LastName varchar(255)
            );
        """)

        conn.commit()
    except Exception as e:
        # Table may already exist
        print(e)
    return "Person API"

@app.get("/all")
def get_persons():
    rows = []
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons")

        for row in cursor.fetchall():
            print(row.FirstName, row.LastName)
            rows.append(f"{row.ID}, {row.FirstName}, {row.LastName}")
    return rows

@app.get("/person/{person_id}")
def get_person(person_id: int):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons WHERE ID = ?", person_id)

        row = cursor.fetchone()
        return f"{row.ID}, {row.FirstName}, {row.LastName}"

@app.post("/person")
def create_person(item: Person):
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute(f"INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", item.first_name, item.last_name)
        conn.commit()

    return item

def get_conn():
    credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

Peringatan

Kode sampel menunjukkan pernyataan SQL mentah, yang seharusnya tidak digunakan dalam kode produksi. Sebagai gantinya, gunakan paket Object Relational Mapper (ORM) seperti SqlAlchemy yang menghasilkan lapisan objek yang lebih aman untuk mengakses database Anda.

Jalankan dan uji aplikasi secara lokal

Aplikasi ini siap untuk diuji secara lokal.

  1. Jalankan app.py file di Visual Studio Code.

    uvicorn app:app --reload

  2. Pada halaman UI Swagger untuk aplikasi http://127.0.0.1:8000/docs, perluas metode POST dan pilih Cobalah.

    Anda juga dapat menggunakan try /redoc untuk melihat bentuk lain dari dokumentasi yang dihasilkan untuk API.

  3. Ubah contoh JSON untuk menyertakan nilai untuk nama depan dan belakang. Pilih Jalankan untuk menambahkan rekaman baru ke database. API mengembalikan respons yang berhasil.

  4. Perluas metode GET pada halaman UI Swagger dan pilih Coba. Pilih Jalankan, dan orang yang baru saja Anda buat dikembalikan.

Menyebarkan ke Azure App Service

Aplikasi ini siap disebarkan ke Azure.

  1. Buat file start.sh sehingga gunicorn di Azure App Service dapat menjalankan uvicorn. start.sh memiliki satu baris:

    gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app

  2. Gunakan az webapp hingga untuk menyebarkan kode ke App Service. (Anda dapat menggunakan opsi -dryrun untuk melihat apa yang dilakukan perintah tanpa membuat sumber daya.)

    az webapp up \
    --resource-group <resource-group-name> \
    --name <web-app-name>

  3. Gunakan perintah az webapp config set untuk mengonfigurasi App Service untuk menggunakan file start.sh.

    az webapp config set \
    --resource-group <resource-group-name> \
    --name <web-app-name> \
    --startup-file start.sh

  4. Gunakan perintah az webapp identity assign untuk mengaktifkan identitas terkelola yang ditetapkan sistem untuk App Service.

    az webapp identity assign \
    --resource-group <resource-group-name> \
    --name <web-app-name>

    Dalam mulai cepat ini, identitas terkelola yang ditetapkan sistem digunakan untuk demonstrasi. Identitas terkelola yang ditetapkan pengguna lebih efisien dalam berbagai skenario yang lebih luas. Untuk informasi selengkapnya, lihat Rekomendasi praktik terbaik identitas terkelola. Untuk contoh penggunaan identitas terkelola yang ditetapkan pengguna dengan pyodbc, lihat Memigrasikan aplikasi Python untuk menggunakan koneksi tanpa kata sandi dengan Azure SQL Database.

Menyambungkan App Service ke Azure SQL Database

Di bagian Konfigurasikan database , Anda mengonfigurasi jaringan dan autentikasi Microsoft Entra untuk server database Azure SQL. Di bagian ini, Anda menyelesaikan konfigurasi database dan mengonfigurasi App Service dengan string koneksi untuk mengakses server database.

Untuk menjalankan perintah ini, Anda dapat menggunakan alat atau IDE apa pun yang dapat tersambung ke Azure SQL Database, termasuk SQL Server Management Studio (SSMS), Azure Data Studio, dan Visual Studio Code dengan ekstensi mssql server SQL. Selain itu, Anda dapat menggunakan portal Azure seperti yang dijelaskan dalam Mulai Cepat: Gunakan editor kueri portal Azure untuk mengkueri Azure SQL Database.

  1. Tambahkan pengguna ke Azure SQL Database dengan perintah SQL untuk membuat pengguna dan peran untuk akses tanpa kata sandi.

    CREATE USER [<web-app-name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<web-app-name>]
ALTER ROLE db_datawriter ADD MEMBER [<web-app-name>]

    Untuk informasi selengkapnya, lihat Pengguna Database Mandiri - Membuat Database Anda Portabel. Untuk contoh yang menunjukkan prinsip yang sama tetapi diterapkan ke Azure VM, lihat Tutorial: Menggunakan identitas terkelola yang ditetapkan sistem Komputer Virtual Windows untuk mengakses Azure SQL. Untuk informasi selengkapnya tentang peran yang ditetapkan, lihat Peran database tetap.

    Jika Anda menonaktifkan lalu mengaktifkan identitas terkelola yang ditetapkan sistem App Service, jatuhkan pengguna dan buat ulang. Jalankan DROP USER [<web-app-name>] dan jalankan CREATE ulang perintah dan ALTER . Untuk melihat pengguna, gunakan SELECT * FROM sys.database_principals.

  2. Gunakan perintah az webapp config appsettings set untuk menambahkan pengaturan aplikasi untuk string koneksi.

    az webapp config appsettings set \
    --resource-group <resource-group-name> \
    --name <web-app-name> \
    --settings AZURE_SQL_CONNECTIONSTRING="<connection-string>"

    Untuk aplikasi yang disebarkan, string koneksi harus menyerupai:

    Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30

    <dabaser-server-name> Isi dan <database-name> dengan nilai Anda.

    string koneksi tanpa kata sandi tidak berisi nama pengguna atau kata sandi. Sebagai gantinya, saat aplikasi berjalan di Azure, kode menggunakan DefaultAzureCredential dari pustaka Azure Identity untuk mendapatkan token yang akan digunakan dengan pyodbc.

Menguji aplikasi yang disebarkan

Telusuri ke URL aplikasi untuk menguji bahwa koneksi ke Azure SQL Database berfungsi. Anda dapat menemukan URL aplikasi Anda di halaman ringkasan App Service.

https://<web-app-name>.azurewebsites.net

Tambahkan /docs ke URL untuk melihat antarmuka pengguna Swagger dan menguji metode API.

Selamat! Aplikasi Anda sekarang terhubung ke Azure SQL Database di lingkungan lokal dan yang dihosting.

Konten terkait