Connection Pool Manual dengan PyMySQL (Tanpa pymysqlpool)

Jika Anda tidak dapat menggunakan pymysqlpool karena kendala sistem, Anda masih bisa membangun connection pool secara manual dengan queue.Queue.

---

🔧 Prasyarat Sistem (Linux)

Debian/Ubuntu

sudo apt update
sudo apt install -y python3 python3-pip

CentOS/RHEL

sudo yum install -y python3 python3-pip

Instal PyMySQL

pip install pymysql

---

🧱 Struktur Pool Manual

Buat file db_pool.py untuk mengelola koneksi pool.

db_pool.py

import pymysql
from queue import Queue
import threading
import os

POOL_SIZE = 5  # Atur sesuai kapasitas database Anda

DB_CONFIG = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME"),
    "charset": "utf8mb4",
    "cursorclass": pymysql.cursors.DictCursor,
}

_pool = Queue(maxsize=POOL_SIZE)
_lock = threading.Lock()

def init_pool():
    with _lock:
        while not _pool.full():
            conn = pymysql.connect(**DB_CONFIG)
            _pool.put(conn)

def get_conn():
    conn = _pool.get()
    if not conn.open:
        conn = pymysql.connect(**DB_CONFIG)
    return conn

def release_conn(conn):
    if conn.open:
        _pool.put(conn)
    else:
        _pool.put(pymysql.connect(**DB_CONFIG))

---

🚀 Cara Menggunakan di Flask

Inisialisasi di app.py atau app_guru.py

from db_pool import init_pool
init_pool()

Contoh route dengan koneksi pool

from flask import Blueprint, render_template
from db_pool import get_conn, release_conn

guru_bp = Blueprint("guru", __name__)

@guru_bp.route("/dashboard")
def dashboard():
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM kelas")
            data = cur.fetchall()
        return render_template("guru/dashboard.html", data=data)
    finally:
        release_conn(conn)

---

⚠️ Tips Keamanan & Performa

• Simpan variabel DB_HOST, DB_USER, DB_PASSWORD, dan DB_NAME di .env atau diatur via server environment (bukan hardcoded).
• Gunakan .env loader seperti python-dotenv jika perlu.
• Jangan lupa try...finally untuk menjamin release_conn() selalu dijalankan.
• Sesuaikan POOL_SIZE dengan beban aplikasi dan kapasitas server MySQL Anda.