Panduan Pemula untuk PostgreSQL

Mampu melakukan kueri pada sistem basis data relasional adalah keterampilan yang wajib dimiliki seorang data scientist. SQL atau Structured Query Language memungkinkan Anda melakukan hal ini dengan sangat efisien. SQL tidak hanya memungkinkan Anda mengajukan pertanyaan yang bermakna kepada data, tetapi juga memungkinkan Anda memanipulasi data dengan berbagai cara. Tanpa basis data, praktis tidak ada aplikasi dunia nyata yang mungkin dibuat. Jadi, pengetahuan tentang basis data dan kemampuan untuk menanganinya merupakan bagian penting dari peralatan seorang data scientist.

Fakta cepat: SQL juga disebut SE-QU-EL. Ini memiliki makna historis — nama awal SQL adalah Simple English Query Language.

Secara umum, basis data relasional tampak seperti berikut ini -

Relasi juga disebut tabel. Ada sejumlah cara untuk merepresentasikan basis data. Ini hanyalah salah satunya dan yang paling populer.

Tutorial ini memperkenalkan empat operasi paling umum yang dilakukan dengan SQL, yaitu Create, Read, Update, dan Delete. Keempat operasi ini secara kolektif sering disebut sebagai CRUD. Dalam aplikasi apa pun yang melibatkan interaksi pengguna, keempat operasi ini selalu ada.

Anda akan menggunakan PostgreSQL sebagai sistem manajemen basis data relasional. PostgreSQL sangat ringan, dan juga gratis. Dalam tutorial ini, Anda akan -

• Mulai dan Menjalankan PostgreSQL
• Menyambung ke basis data PostgreSQL
• Membuat, membaca, memperbarui, dan menghapus tabel di basis data tersebut
• Menjalankan SQL di Jupyter Notebook
• Menjalankan SQL di Python

Mari kita mulai.

Mulai dan Menjalankan PostgreSQL

PostgreSQL adalah RDBMS yang ringan dan bersumber terbuka. Ini sangat diterima dengan baik oleh industri. Anda dapat mempelajari lebih lanjut tentang PostgreSQL dari situs resminya.

Untuk dapat mulai menulis dan mengeksekusi kueri di PostgreSQL, Anda perlu memasangnya di mesin Anda. Proses pemasangannya sangat mudah. Dua video singkat berikut menunjukkan cara mengunduh dan memasang PostgreSQL pada mesin Windows-7 32-bit:

• Unduh PostgreSQL
• Pasang PostgreSQL

Catatan: Saat Anda memasang PostgreSQL, catat kata sandi dan nomor port yang Anda masukkan.

Setelah Anda berhasil memasang PostgreSQL di mesin Anda, buka pgAdmin. pgAdmin adalah utilitas praktis yang disertakan dalam pemasangan PostgreSQL, dan memungkinkan Anda melakukan tugas-tugas basis data rutin melalui antarmuka grafis yang baik. Antarmuka pgAdmin terlihat seperti -

Saat Anda membuka pgAdmin, Anda akan melihat server bernama "PostgreSQL 9.4 (localhost:5432)" tercantum di antarmuka -

Catatan: Versi Anda mungkin berbeda dari yang di atas dan begitu pula nomor portnya (5432).

Sambungkan ke server dengan memasukkan kata sandi yang Anda berikan saat pemasangan. Untuk referensi - https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Setelah Anda berhasil tersambung ke server basis data lokal, Anda akan mendapatkan antarmuka yang mirip dengan berikut ini -

Tugas pertama Anda adalah membuat basis data, dan Anda dapat melakukannya dengan mengklik kanan tab Databases lalu memilih New Database dari menu tarik-turun. Mari buat basis data bernama DataCamp_Courses. Setelah basis data dibuat, Anda dapat melanjutkan ke bagian berikutnya dari tutorial ini.

Operasi CRUD di PostgreSQL

Membuat tabel sesuai spesifikasi yang diberikan -

Untuk dapat beroperasi pada sebuah basis data, Anda memerlukan tabel. Jadi mari kita buat tabel sederhana (juga disebut relasi) bernama datacamp_courses dengan spesifikasi (skema) berikut -

Spesifikasi tersebut memberi kita beberapa informasi tentang kolom-kolom tabel -

• Kunci utama tabel harus course_id (perhatikan hanya yang ini yang dicetak tebal) dan tipe datanya harus integer. Primary key adalah kendala yang memaksa nilai kolom tidak boleh null dan harus unik. Ini memungkinkan Anda mengidentifikasi secara unik suatu instance tertentu atau sekumpulan instance yang ada di tabel.
• Sisa informasi dalam spesifikasi sekarang seharusnya mudah ditafsirkan.

Untuk membuat tabel, klik kanan pada basis data yang baru dibuat DataCamp_Courses dan pilih CREATE Script dari opsi yang tersedia. Anda akan mendapatkan sesuatu yang mirip berikut ini -

Sekarang mari eksekusi kueri berikut -

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

Untuk mengeksekusi kueri, cukup pilih kuerinya dan klik tombol eksekusi dari bilah menu -

Hasil keluarannya seharusnya -

Struktur umum kueri pembuatan tabel di PostgreSQL tampak seperti -

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)

Kita tidak menentukan table_constraints saat membuat tabel. Itu bisa diabaikan untuk sekarang. Hal lain cukup mudah dibaca kecuali kata kunci SERIAL. Serial di PostgreSQL memungkinkan Anda membuat kolom auto-increment. Secara bawaan, ini membuat nilai bertipe integer. Serial membebaskan kita dari beban mengingat primary key terakhir yang disisipkan/diperbarui pada sebuah tabel, dan merupakan praktik yang baik menggunakan auto-increment untuk primary key. Anda dapat mempelajari lebih lanjut tentang serial dari sini.

Menyisipkan beberapa rekaman ke tabel yang baru dibuat -

Pada langkah ini, Anda akan menyisipkan beberapa rekaman ke tabel. Rekaman Anda harus berisi -

• Nama kursus
• Nama instruktur kursus
• Topik kursus

Nilai untuk kolom course_id akan ditangani oleh PostgreSQL sendiri. Struktur umum kueri insert di PostgreSQL tampak seperti -

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);

Mari sisipkan beberapa rekaman -

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

Perhatikan bahwa Anda tidak menentukan primary key secara eksplisit. Anda akan melihat dampaknya sebentar lagi.

Saat Anda mengeksekusi dua kueri di atas, Anda akan mendapatkan hasil berikut setelah penyisipan berhasil -

Kueri berhasil dijalankan: satu baris terpengaruh, waktu eksekusi 11 ms.

Membaca/menampilkan data dari tabel -

Ini mungkin sesuatu yang akan sering Anda lakukan dalam perjalanan data science Anda. Untuk saat ini, mari lihat bagaimana kondisi tabel datacamp_courses.

Ini umumnya disebut kueri select, dan struktur generik kueri select tampak seperti -

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

Mari pilih semua kolom dari tabel datacamp_courses

SELECT * FROM datacamp_courses;

Dan Anda mendapatkan -

Perhatikan primary key sekarang. Jika Anda hanya ingin melihat nama-nama kursus, Anda dapat melakukannya dengan -

SELECT course_name from datacamp_courses;

Dan Anda mendapatkan -

Anda dapat menyebutkan sebanyak mungkin nama kolom yang ingin Anda lihat dalam hasil, asalkan kolom tersebut ada di tabel. Jika Anda menjalankan select course_name, number_particpants from datacamp_courses; Anda akan mengalami galat karena kolom number_particpants memang ada di tabel. Sekarang Anda akan melihat bagaimana memperbarui satu rekaman tertentu di tabel.

Memperbarui sebuah rekaman di tabel -

Struktur umum kueri update dalam SQL tampak seperti berikut:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;

Anda akan memperbarui rekaman di mana course_instructor = "Chester Ismay" dan mengatur course_name menjadi "Joining Data in SQL". Anda kemudian akan memverifikasi apakah rekaman tersebut diperbarui. Kueri untuk melakukan ini adalah -

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';

Mari lihat apakah kueri update Anda memberikan efek yang diinginkan dengan menjalankan kueri select -

Anda dapat melihat kueri update Anda berjalan persis seperti yang Anda inginkan. Sekarang Anda akan melihat bagaimana menghapus sebuah rekaman dari tabel.

Menghapus sebuah rekaman di tabel -

Struktur umum kueri delete dalam SQL tampak seperti berikut:

DELETE FROM table
WHERE condition;

Anda akan menghapus rekaman di mana course_name = "Deep Learning in Python" dan kemudian memverifikasi apakah rekaman tersebut terhapus. Mengikuti strukturnya, Anda dapat melihat bahwa kueri berikut seharusnya dapat melakukan ini -

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';

Perlu diingat bahwa kata kunci tidak peka huruf besar/kecil dalam SQL, tetapi data peka huruf besar/kecil. Inilah sebabnya Anda melihat campuran huruf besar dan huruf kecil dalam kueri.

Mari lihat apakah rekaman yang dimaksud terhapus dari tabel atau tidak -

Dan ya, rekaman yang dimaksud memang terhapus.

Struktur generik kueri seperti yang disebutkan dalam tutorial ini dirujuk dari PostgreSQL Tutorial.

Sekarang Anda tahu cara melakukan kueri CRUD dasar di SQL. Sebagian dari Anda mungkin sering menggunakan Jupyter Notebook dan mungkin berpikir akan sangat bagus jika ada opsi untuk mengeksekusi kueri ini langsung dari Jupyter Notebook. Pada bagian berikutnya, Anda akan melihat bagaimana mewujudkannya.

SQL + Jupyter Notebook

Untuk dapat menjalankan kueri SQL dari Jupyter Notebook, langkah pertama adalah memasang paket ipython-sql.

Jika belum terpasang, pasang dengan:

pip install ipython-sql

Setelah selesai, muat ekstensi sql di Jupyter Notebook Anda dengan mengeksekusi -

%load_ext sql

Langkah berikutnya adalah menyambung ke basis data PostgreSQL. Anda akan tersambung ke basis data yang Anda buat — DataCamp_Courses.

Untuk dapat menyambung ke basis data yang sudah dibuat di sistem Anda, Anda harus memberi tahu Python agar mendeteksi dialeknya. Dalam istilah sederhana, Anda harus memberi tahu Python bahwa itu adalah basis data PostgreSQL. Untuk itu, Anda memerlukan psycopg2 yang dapat dipasang menggunakan:

pip install psycopg2

Setelah Anda memasang psycopg sambungkan ke basis data menggunakan -

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses

'Connected: postgres@DataCamp_Courses'

Perhatikan penggunaan %sql. Ini adalah magic command. Ini memungkinkan Anda mengeksekusi pernyataan SQL dari Jupyter Notebook. Yang mengikuti %sql disebut URL koneksi basis data di mana Anda menentukan -

• Dialek (postgres)
• Nama pengguna (postgres)
• Kata sandi (postgres)
• Alamat server (localhost)
• Nomor port (5432)
• Nama basis data (DaaCamp_Courses)

Sekarang Anda dapat melakukan semua hal dari Jupyter Notebook yang Anda lakukan di antarmuka pgAdmin. Mari mulai dengan membuat tabel _datacampcourses dengan skema yang sama persis.

Namun sebelum itu Anda harus menjatuhkan (drop) tabel karena SQL tidak akan membiarkan Anda menyimpan dua tabel dengan nama yang sama. Anda dapat menjatuhkan sebuah tabel dengan -

%sql DROP table datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Tabel _datacampcourses kini dihapus dari PostgreSQL dan karena itu Anda dapat membuat tabel baru dengan nama ini.

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Perhatikan penggunaan %sql %%sql di sini. Untuk mengeksekusi satu baris kueri, Anda dapat menggunakan %sql, tetapi jika Anda ingin mengeksekusi beberapa kueri sekaligus, Anda harus menggunakan %%sql.

Mari sisipkan beberapa rekaman -

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] 

Lihat tabelnya untuk memastikan penyisipan dilakukan sesuai harapan -

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Mari lanjutkan alurnya. Selanjutnya, Anda akan memperbarui sebuah rekaman di tabel -

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

Perhatikan baik-baik saat Anda berurusan dengan string dalam SQL. Berbeda dengan bahasa pemrograman tradisional, nilai string perlu dibungkus menggunakan tanda kutip tunggal.

Sekarang mari verifikasi apakah kueri update Anda memberikan efek yang diinginkan -

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Sekarang mari hapus sebuah rekaman dan verifikasi -

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Sekarang Anda sudah memiliki gambaran jelas tentang mengeksekusi operasi CRUD di PostgreSQL dan bagaimana melakukannya melalui Jupyter Notebook. Jika Anda familier dengan Python dan tertarik mengakses basis data melalui kode Python Anda, Anda juga dapat melakukannya. Bagian berikutnya membahas hal itu.

Mulai dengan SQLAlchemy dan Menggabungkannya dengan Magic Command SQL

Untuk bagian ini, Anda memerlukan paket SQLAlchemy. Ini biasanya sudah ada dalam distribusi Anaconda. Anda juga dapat memasangnya dengan pip. Setelah terpasang, Anda dapat mengimpornya dengan -

import sqlalchemy

Untuk dapat berinteraksi dengan basis data Anda menggunakan SQLAlchemy, Anda perlu membuat engine untuk RDBMS tempat basis data Anda disimpan. Dalam kasus Anda, itu adalah PostgreSQL. SQLAlchemy memungkinkan Anda membuat engine RDBMS hanya dengan satu pemanggilan create_engine(), dan metode ini menerima URL koneksi basis data yang telah Anda lihat sebelumnya.

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Memungkinkan Anda melihat nama-nama tabel yang ada di basis data

['datacamp_courses']

Anda dapat melihat tabel bernama _datacampcourses yang semakin mengonfirmasi bahwa Anda berhasil membuat engine. Mari jalankan kueri select sederhana untuk melihat rekaman tabel _datacampcourses dan menyimpannya dalam objek DataFrame pandas.

Anda akan menggunakan metode read_sql() (disediakan oleh pandas) yang menerima string kueri SQL dan sebuah engine.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()

Anda juga dapat memasangkan magic command %sql di dalam kode Python Anda.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Perjalanan Anda sudah jauh!

Selamat dan tepuk bahu Anda sendiri! Dalam tutorial ini, Anda tidak hanya mulai menggunakan PostgreSQL, tetapi juga melihat bagaimana mengeksekusi kueri SQL dengan beberapa cara. Sebagai langkah berikutnya, Anda sebaiknya menonton webinar ini oleh David Robinson (Chief Data Scientist di DataCamp) yang menunjukkan bagaimana ia menggunakan SQL dalam tugas sehari-harinya. Lihat juga tutorial kami Cara Memasang PostgreSQL di Windows dan Mac OS X. Jika Anda ingin meningkatkan keterampilan SQL Anda, berikut beberapa sumber yang sangat baik untuk memulai -

• Pengantar SQL untuk Data Science
• SQL oleh Kaggle Learn
• Joining Data in SQL