Menyambungkan dan mengkueri Azure SQL Database menggunakan Python dan driver pyodbc
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:
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.
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:
- Mengonfigurasi aturan firewall Azure SQL Database.
- Konfigurasikan jaringan virtual dengan titik akhir privat.
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.
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.
Buka Visual Studio Code dan buat folder baru untuk proyek Anda dan ubah direktori ke dalamnya.
mkdir python-sql-azure cd python-sql-azure
Buat lingkungan virtual untuk aplikasi.
py -m venv .venv .venv\scripts\activate
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 flask
paket , 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.
Buat file requirements.txt dengan baris berikut:
pyodbc fastapi uvicorn[standard] pydantic azure-identity
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:
Buka Azure SQL Server, pilih halaman database SQL untuk menemukan nama database Anda, dan pilih database.
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.
Jalankan
app.py
file di Visual Studio Code.uvicorn app:app --reload
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.
Ubah contoh JSON untuk menyertakan nilai untuk nama depan dan belakang. Pilih Jalankan untuk menambahkan rekaman baru ke database. API mengembalikan respons yang berhasil.
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.
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
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>
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
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.
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 jalankanCREATE
ulang perintah danALTER
. Untuk melihat pengguna, gunakanSELECT * FROM sys.database_principals
.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 denganpyodbc
.
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
- Migrasikan aplikasi Python untuk menggunakan koneksi tanpa kata sandi dengan Azure SQL Database - Menampilkan identitas terkelola yang ditetapkan pengguna.
- Koneksi tanpa kata sandi untuk layanan Azure
- Rekomendasi praktik terbaik identitas terkelola