KAL
Menu

Akses SQL dari Python

Membaca dari basis data PostgreSQL dengan psycopg dan mengeja komputasi yang tepat ditekan ke SQL.

Daftar isi

Sebagian besar data produksi berada di basis data, dan data scientist yang nyaman menariknya sendiri tidak menunggu antrean permintaan. Topik ini memandang dua arah pekerjaan itu, yaitu menyiapkan tabel contoh di PostgreSQL melalui psql, lalu membacanya ke pandas dengan pustaka psycopg, termasuk prinsip yang paling menentukan kinerjanya, yaitu menekan komputasi ke server sebanyak mungkin. Setelah membacanya, perpindahan dari basis data ke notebook dapat dilakukan dengan pola yang aman dan hemat.

Menyiapkan tabel contoh#

Contoh pada topik ini memakai basis data udara yang dibuat ulang setiap kali dijalankan, sehingga hasilnya selalu sama. Empat perintah psql berikut membuat basis data beserta tabel pengukuran enam barisnya, dan outputnya berupa konfirmasi singkat dari PostgreSQL.

bash 
# siapkan basis data contoh (dapat diulang dengan aman)
psql -h /home/kal/my-project/pgsock -U postgres -c "DROP DATABASE IF EXISTS udara"
psql -h /home/kal/my-project/pgsock -U postgres -c "CREATE DATABASE udara"
psql -h /home/kal/my-project/pgsock -U postgres -d udara -c "CREATE TABLE pengukuran (id serial PRIMARY KEY, waktu timestamp, stasiun text, pm25 numeric)"
psql -h /home/kal/my-project/pgsock -U postgres -d udara -c "INSERT INTO pengukuran (waktu, stasiun, pm25) VALUES ('2026-03-01 08:00','A',34.0), ('2026-03-01 09:00','A',41.5), ('2026-03-01 10:00','A',28.0), ('2026-03-01 08:00','B',52.3), ('2026-03-01 09:00','B',47.0), ('2026-03-01 10:00','B',61.2)"
text Keluaran 
DROP DATABASE
CREATE DATABASE
CREATE TABLE
INSERT 0 6

Pola drop lalu create ini bukan gaya kerja pada basis data milik orang lain, melainkan pola lingkungan latihan yang membuat topik dapat diulang tanpa keadaan sisa. Pada basis data produksi, pembuatan tabel menjadi tugas data engineer dan yang dikerjakan data scientist adalah membacanya, dengan izin yang diberikan melalui role sebagaimana dibahas pada jalur PostgreSQL.

Membaca hasil query ke pandas#

Pustaka psycopg adalah penghubung resmi Python ke PostgreSQL, dan polanya adalah membuka koneksi, menjalankan query lewat cursor, lalu menyusun hasil fetchall() menjadi DataFrame dengan nama kolom dari cur.description. Berkas baca_sql.py memperlihatkan tiga bentuk sekaligus, yaitu pembacaan utuh, agregasi di server, dan query berparameter.

python baca_sql.py 
"""Membaca dan menulis query PostgreSQL dari Python dengan psycopg."""
import pandas as pd
import psycopg

INFO = "host=/home/kal/my-project/pgsock dbname=udara user=postgres"

with psycopg.connect(INFO) as koneksi:
    with koneksi.cursor() as cur:
        cur.execute("SELECT waktu, stasiun, pm25 FROM pengukuran ORDER BY id")
        kolom = [d.name for d in cur.description]
        data = pd.DataFrame(cur.fetchall(), columns=kolom)
        print(data.to_string(index=False))
        print()

        # komputasi ditekan ke server: agregasi dihitung oleh PostgreSQL
        cur.execute(
            "SELECT stasiun, round(avg(pm25), 1) AS pm25_rata "
            "FROM pengukuran GROUP BY stasiun ORDER BY stasiun"
        )
        for stasiun, rata in cur.fetchall():
            print(stasiun, rata)
        print()

        # query berparameter: nilai dikirim terpisah dari teks SQL
        cur.execute(
            "SELECT waktu, pm25 FROM pengukuran WHERE stasiun = %s ORDER BY id",
            ("A",),
        )
        hasil = pd.DataFrame(cur.fetchall(), columns=["waktu", "pm25"])
        print(hasil.to_string(index=False))
text Keluaran 
              waktu stasiun pm25
2026-03-01 08:00:00       A 34.0
2026-03-01 09:00:00       A 41.5
2026-03-01 10:00:00       A 28.0
2026-03-01 08:00:00       B 52.3
2026-03-01 09:00:00       B 47.0
2026-03-01 10:00:00       B 61.2

A 34.5
B 53.5

              waktu pm25
2026-03-01 08:00:00 34.0
2026-03-01 09:00:00 41.5
2026-03-01 10:00:00 28.0

Info koneksi pada INFO dan opsi -h pada psql menunjuk soket milik lingkungan latihan jalur ini, dan lingkungan lain cukup mengganti host, nama basis data, dan penggunanya. Perhatikan pula bahwa pm25 bertipe numeric di PostgreSQL dan tiba di Python sebagai desimal eksak, bukan bilangan pecahan, sehingga perhitungan yang menuntut presisi tetap terjaga sampai titik konversi yang disengaja.

Menekan komputasi ke server#

Prinsip query pushdown menyatakan satu hal sederhana, yaitu komputasi yang mampu dikerjakan server sebaiknya memang dikerjakan di sana, karena agregasi di server mengurangi ukuran data yang melewati jaringan dan memori. Kueri kedua pada skrip di atas adalah bentuk kecilnya, yaitu rata-rata per stasiun dihitung PostgreSQL dan yang tiba di Python hanya dua baris, bukan enam.

Perbedaannya menggigit pada skala nyata. Tabel pengukuran berjuta baris yang dirata-rata di server tiba sebagai satu baris per stasiun, sedangkan tabel yang ditarik utuh untuk dirata-rata di notebook memakai jaringan dan memori sebesar tabel itu sendiri. Aturan praktisnya berlapis, yaitu filter dan agregasi ditulis di SQL, sedangkan pekerjaan yang benar-benar membutuhkan seluruh baris, seperti pembelajaran mesin atas kolom mentah, ditarik ke pandas.

Keputusan itu juga soal kejujuran hasil. Satu query SQL yang sama dibaca dan diulang oleh siapa pun dengan hasil yang sama, sedangkan rantai transformasi pandas yang panjang di atas data utuh lebih sulit diaudit. Komputasi yang dipindah ke server biasanya ikut memindahkan definisi metriknya ke satu tempat yang dapat dirujuk.

Baris terakhir skrip memperlihatkan query berparameter, yaitu teks SQL memuat penanda %s sementara nilainya dikirim terpisah pada tuple. Pola ini bukan gaya, melainkan pertahanan, sebab penggabungan string dengan nilai dari pengguna membuka celah SQL injection, dan modul query berparameter pada jalur aplikasi membahasnya dari sisi keamanan yang sama.

Batas yang disiplin untuk analisis adalah selalu berparameter pada query yang menyentuh masukan luar, dan menuliskan filter sebagai parameter alih-alih menempelkan nilai dengan f-string. Hasilnya identik, tetapi satu pola itu membiasakan bentuk yang aman sejak notebook pertama.

Kesalahan umum#

Kesalahan pertama adalah menarik tabel utuh lalu memfilter di pandas. Kode tersebut berjalan, tetapi pada tabel besar ia membebani jaringan dan memori tanpa alasan, dan perbaikannya berpindah syarat WHERE ke dalam query.

Kesalahan kedua adalah membuka koneksi tanpa penutup. Sesi yang tertinggal memakan sambungan server sampai batas koneksi habis, sedangkan blok with pada contoh menutupnya segera setelah blok berakhir, baik selesai maupun error.

Kesalahan ketiga adalah mengasumsikan tipe hasil. numeric PostgreSQL tiba sebagai desimal, timestamp sebagai objek datetime, dan teks kosong sebagai None, sehingga kolom yang dibaca perlu diperiksa dtypes-nya seperti berkas biasa sebelum digabung dengan tabel pandas lain.

Ringkasan#

  • psycopg membuka koneksi, menjalankan query lewat cursor, dan hasilnya disusun menjadi DataFrame dengan nama kolom dari cur.description.
  • Komputasi yang dapat dikerjakan server, terutama filter dan agregasi, ditekan ke SQL agar yang melewati jaringan sekecil mungkin.
  • Query berparameter memisahkan nilai dari teks SQL dan menjadi bentuk baku yang aman untuk setiap query yang menyentuh masukan luar.
  • Lingkungan latihan dibuat ulang dengan pola drop lalu create, dan info koneksi disesuaikan dengan lingkungan masing-masing.

Referensi#