Kursus MariaDB / Menghubungkan Python dan Menggunakan Kueri Berparameter

Saat menghubungkan Python ke MariaDB, berikan teks SQL dan nilai masukan pengguna secara terpisah melalui antarmuka pengandar. Kueri berparameter menjaga nilai agar tidak dirangkai menjadi sintaks SQL. Akun dengan hak terbatas serta komit atau rollback yang eksplisit mengendalikan perubahan basis data.

Siapkan lingkungan dan akun aplikasi

Contoh ini memakai Python 3, PyMySQL 1.1.2, dan koneksi TCP lokal ke MariaDB. Perilaku intinya diverifikasi dengan Python 3.12 dan MariaDB 12.3.3. PyMySQL adalah pengandar basis data untuk komunikasi dari Python; penanda tempat nilai pada contoh ini adalah %s. Jangan menggantinya dengan tanda tanya dari pengandar lain.

Jalankan penyiapan berikut melalui koneksi administrator. Ganti placeholder publik Replace_With_Your_Own_Lab_Password dengan kata sandi latihan Anda sendiri. CREATE USER digunakan untuk penyiapan pertama; periksa akun dan tabel yang ada sebelum mengulang blok. Jika appops_lab sudah ada, pastikan basis data tersebut memang milik rangkaian latihan ini. Panduan akun dan hak akses MariaDB menjelaskan pembagian tanggung jawabnya.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

Host akun 127.0.0.1 mengasumsikan akses TCP ke server yang berjalan langsung pada komputer lokal. Kontainer atau basis data jarak jauh dapat melihat asal klien yang berbeda. Periksa pencocokan host akun dan jalur koneksi daripada mengatasi masalah dengan membolehkan akses administrator dari sembarang host.

Buat proyek terpisah dan simpan program lengkap

Buat folder proyek baru dan buka PowerShell di dalamnya. Python harus terpasang dan tersedia melalui perintah python. Buat lingkungan virtual, yaitu lingkungan paket Python tersendiri untuk proyek, lalu pasang versi pengandar yang dipakai contoh:

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

Memanggil Python lingkungan secara langsung tidak memerlukan perubahan kebijakan eksekusi PowerShell untuk skrip aktivasi. Pada Linux atau macOS, gunakan python3 -m venv .venv, lalu .venv/bin/python -m pip install PyMySQL==1.1.2. Simpan program lengkap berikut sebagai berkas UTF-8 notes_app.py di folder proyek:

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

Program meminta kata sandi saat dijalankan melalui getpass, bukan menyimpannya dalam kode sumber atau teks SQL. Koneksi memakai utf8mb4 untuk Unicode serta batas waktu koneksi, baca, dan tulis. Pesan Database password: meminta kata sandi basis data.

Konfigurasi ini ditujukan untuk latihan lokal. Koneksi melalui jaringan lain juga memerlukan Transport Layer Security (TLS), yaitu enkripsi koneksi, dengan verifikasi sertifikat server. Penggunaan kata sandi saja tidak melindungi jalur pengiriman data.

Coba nama biasa dan nama dengan apostrof

Di folder notes_app.py, jalankan kedua perintah sebagai eksekusi program yang terpisah:

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

Masukkan kata sandi pada setiap eksekusi. Perintah pertama menyimpan Notebook, sedangkan yang kedua menyimpan O'Reilly termasuk apostrofnya. Keduanya kemudian mencari baris dengan nama yang sama. Pada tabel baru, masing-masing perintah yang dijalankan sekali menghasilkan satu baris yang sesuai. Jangan mensyaratkan ID AUTO_INCREMENT tertentu karena data sebelumnya dan INSERT yang dibatalkan dapat memengaruhi penomoran.

Menjalankan nama yang sama lagi menambah baris lain dan mengembalikan semua baris yang cocok. Contoh ini tidak membuat nama unik. Jika proses bisnis memerlukan pencegahan duplikasi, tentukan identitas catatan dan batasan basis data yang sesuai secara eksplisit.

Pisahkan nilai dari sintaks SQL

Pemanggilan utamanya adalah cur.execute(sql, (name,)). SQL dan tuple nilai diberikan sebagai argumen terpisah; koma membentuk tuple dengan satu elemen. Jangan menambahkan tanda kutip SQL sendiri di sekitar %s. Hindari memasukkan nilai pengguna melalui operator % Python, f-string, atau penggabungan teks. Contoh resmi PyMySQL memperlihatkan pemberian nilai secara terpisah.

Nama seperti x' OR 1=1 -- harus tetap menjadi satu nilai yang disimpan dan dicari secara harfiah, bukan berubah menjadi kondisi yang memilih semua baris. Istilah kueri berparameter di sini menjelaskan antarmuka pengandar. Istilah tersebut tidak berarti setiap pengandar selalu memakai protokol prepared statement pada server.

Pengenal dan kata kunci SQL, termasuk nama kolom serta ASC atau DESC, tidak dapat digantikan oleh parameter nilai biasa. Bagian ORDER BY berasal hanya dari pemetaan columns yang ditentukan pengembang. argparse dan fungsi sama-sama memeriksa pilihannya sehingga pemanggil fungsi lain juga tidak dapat melewati daftar pilihan yang diperbolehkan. Lihat referensi argumen Cursor.execute.

Periksa transaksi dan hak akses bersama-sama

Dengan autocommit=False, save_and_find yang selesai tanpa kesalahan belum menyimpan INSERT secara permanen. main melakukan komit setelah INSERT dan SELECT berhasil, rollback ketika muncul pengecualian, lalu menutup koneksi dalam finally. Bedakan batas komit dari data yang dapat dilihat koneksi lain. Panduan transaksi InnoDB, COMMIT, dan ROLLBACK menjelaskan batas tersebut.

Fungsi menolak nama kosong, nama lebih dari 80 karakter, serta kunci pengurutan yang tidak dikenal sebelum menambahkan baris. Pesan Name must contain 1 to 80 characters berarti nama harus berisi 1–80 karakter; Unknown sort key berarti pilihan pengurutan tidak dikenal. Jika terjadi pengecualian sesudah INSERT dan sebelum COMMIT, ROLLBACK harus mencegah baris tersebut tersimpan.

Akun aplikasi hanya mempunyai SELECT dan INSERT pada app_notes. Program tidak membuat atau menghapus tabel sehingga tidak membutuhkan CREATE atau DROP. Hak terbatas melengkapi penanganan parameter yang benar dan mengurangi operasi yang mungkin dilakukan jika program memiliki cacat lain. Referensi koneksi PyMySQL menjelaskan autocommit serta batas waktu.

Diagnosis kesalahan dan bersihkan latihan

Gejala Hal yang diperiksa
ModuleNotFoundError Jalankan dengan Python .venv yang sama seperti saat memasang PyMySQL
Access denied Nama pengguna, kata sandi, host akun, dan alamat klien yang terlihat oleh server
Koneksi ditolak atau waktu habis Status server, DB_HOST, DB_PORT, firewall, dan jalur jaringan yang sebenarnya
Baris tidak ada setelah pemrosesan Pengecualian yang memicu rollback serta basis data dan keadaan komit yang sedang diperiksa
Kesalahan hanya pada apostrof Apakah nilai dirangkai sendiri ke SQL alih-alih diberikan terpisah melalui execute

Pengaturan koneksi yang bukan rahasia dapat ditimpa pada PowerShell, misalnya $env:DB_PORT = "3307". Pertahankan permintaan kata sandi interaktif. Hapus pengaturan port yang tidak lagi diperlukan dengan Remove-Item Env:DB_PORT, atau pulihkan nilai sebelumnya jika sudah ada sebelum latihan.

Saat selesai, hentikan program dan minta administrator memastikan nama akun serta tabel berikut benar-benar menunjuk objek latihan yang boleh dibuang:

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;

Jangan memakai akun atau tabel bisnis pada pembersihan ini. Penghapusan tersebut tidak dapat dibatalkan dengan ROLLBACK biasa. Cadangkan terlebih dahulu isi yang perlu dipertahankan.