# Prepared statement otomatis di psycopg 3.
# Koneksi membaca variabel lingkungan PGHOST/PGPORT/PGUSER/PGDATABASE,
# sama seperti pola libpq yang dipakai psql.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

print("prepare_threshold bawaan:", conn.prepare_threshold)

EMAIL = [
    "budi@example.com",
    "siti@example.com",
    "ani@example.com",
    "budi@example.com",
    "siti@example.com",
    "ani@example.com",
    "budi@example.com",
]

for i, email in enumerate(EMAIL, start=1):
    # Pola panggilan psycopg: teks query yang sama berulang kali,
    # parameter yang berubah. Driver yang memutuskan kapan query
    # cukup sering untuk disiapkan sebagai prepared statement.
    cur.execute("SELECT id, nama FROM pelanggan WHERE email = %s", (email,))
    id_pelanggan, nama = cur.fetchone()
    # Query pemantau diberi nomor unik lewat komentar supaya query
    # pemantau itu sendiri tidak ikut terhitung sebagai kandidat.
    jumlah = cur.execute(
        f"SELECT count(*) FROM pg_prepared_statements -- pantau {i}"
    ).fetchone()[0]
    print(f"eksekusi ke-{i}: {email} -> id {id_pelanggan} ({nama}), "
          f"statement di server = {jumlah}")

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
for nama_statement, teks in cur.fetchall():
    print("statement di server:", nama_statement, "|", teks)

cur.close()
conn.close()

# Threshold dapat diatur sebelum eksekusi pertama.
conn2 = psycopg.connect()
conn2.prepare_threshold = 1
cur2 = conn2.cursor()
for i in range(2):
    cur2.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur2.fetchone()
print("prepare_threshold=1, statement di server:",
      cur2.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])

# None mematikan prepared statement sepenuhnya.
conn3 = psycopg.connect()
conn3.prepare_threshold = None
cur3 = conn3.cursor()
for i in range(7):
    cur3.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur3.fetchone()
print("prepare_threshold=None setelah 7 eksekusi, statement di server:",
      cur3.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])
cur2.close()
cur3.close()
conn2.close()
conn3.close()