Mengelola Database PostgreSQL di Python dengan psycopg2

Membuat tabel di PostgreSQL

Sudah saatnya membuat tabel pertama Anda di database datacamp_courses. Kita ingin membuat tabel dengan informasi tentang beberapa kursus dalam katalog kursus DataCamp. Tabel tersebut memiliki skema berikut: 

Spesifikasi memberikan cukup banyak informasi tentang kolom tabel. Kunci utama tabel harus course_id, dan tipe datanya harus integer. Kunci utama adalah sebuah constraint yang memastikan nilai kolom tidak null dan unik. Ini memungkinkan Anda mengidentifikasi secara unik suatu instance tertentu atau sekumpulan instance dalam tabel. 

Kolom lainnya menyediakan informasi tentang nama kursus, nama instruktur kursus, dan topik kursus.

Sebelum membuat tabel, penting untuk menjelaskan cara kerja instance koneksi yang baru saja Anda buat. Intinya, koneksi merangkum sebuah sesi database, dan memungkinkan Anda mengeksekusi perintah dan kueri SQL, seperti SELECT, INSERT, CREATE, UPDATE, atau DELETE menggunakan metode cursor(), dan membuat perubahan menjadi persisten menggunakan metode commit() . 

Setelah Anda membuat instance cursor, Anda dapat mengirim perintah ke database menggunakan metode execute() dan mengambil data dari tabel menggunakan fetchone(), fetchmany(), atau fetchall().

Terakhir, penting untuk menutup cursor dan koneksi ke database setiap kali Anda telah menyelesaikan operasi. Jika tidak, keduanya akan terus menahan sumber daya di sisi server. Untuk melakukannya, Anda dapat menggunakan metode close() .

Di bawah ini, Anda dapat menemukan kode untuk membuat tabel datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""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);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Ini adalah contoh sangat dasar tentang cara membuat tabel di PostgreSQL, namun hal ini dapat menjadi jauh lebih kompleks. Jika Anda ingin mempelajari lebih lanjut cara membuat database PostgreSQL dan mengeksplorasi struktur, tipe data, serta cara menormalkan database, lihat kursus Creating PostgreSQL Database kami.

Mengeksekusi Kueri PostgreSQL Dasar di Python

Tabel datacamp_courses sudah siap; sekarang saatnya menggunakan SQL untuk menjalankan beberapa kueri dasar!

INSERT 

Anda mungkin memperhatikan bahwa tabel belum memiliki nilai sejauh ini. Untuk membuat rekaman di tabel datacamp_courses, kita memerlukan perintah INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

Tabel yang dihasilkan akan terlihat seperti ini:

SELECT 

Membaca data di database SQL mungkin akan sering Anda lakukan dalam perjalanan data science Anda. Ini biasanya disebut kueri SELECT. Untuk saat ini, mari lihat bagaimana kondisi tabel datacamp_courses.

Kita akan memanggil pernyataan klasik SELECT * FROM database_name untuk membaca semua data yang tersedia di tabel. Lalu, kita gunakan metode fetchall() untuk mengambil semua baris yang tersedia. Perhatikan bahwa PostgreSQL secara otomatis membuat indeks numerik untuk kolom course_id. 

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

UPDATE 

Data sering kali datang dengan kesalahan. Anda mungkin memperhatikan pada bagian sebelumnya bahwa topik yang terkait dengan kursus “Introduction to SQL” adalah Julia. Setelah memeriksa informasi tentang kursus tersebut, kita menemukan kesalahan. Kita perlu mengubahnya dan menuliskan “SQL” sebagai gantinya. Ini dapat dilakukan dengan pernyataan UPDATE sebagai berikut:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

DELETE 

Terakhir, Anda mungkin ingin menghapus salah satu rekaman di tabel Anda. Misalnya, mari hapus kursus Introduction to Statistics in R:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Kueri PostgreSQL Lanjutan di Python

Pada bagian sebelumnya, kita membahas kueri SQL paling dasar. Namun masih banyak yang bisa dipelajari. Mari lihat beberapa kueri yang lebih maju.

ORDER BY

Misalkan Anda ingin mengurutkan database berdasarkan nama instruktur. Anda dapat menggunakan pernyataan ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GROUP BY

Anda mungkin ingin melakukan beberapa fungsi agregat dalam kelompok data yang berbeda. Misalnya, Anda tertarik menghitung jumlah kursus berdasarkan instruktur kursus yang berbeda. Anda dapat melakukan operasi seperti ini dengan fungsi GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Sampai titik ini, kita hanya bekerja dengan tabel datacamp_course. Namun, Anda baru mulai memanfaatkan potensi penuh database relasional, seperti PostgreSQL, ketika Anda bekerja dengan beberapa tabel sekaligus. 

Alat andalan untuk menggabungkan beberapa tabel adalah operasi JOIN. Bayangkan kita memiliki tabel kedua di database bernama programming_languages yang memuat informasi dasar tentang bahasa pemrograman teratas untuk data science, termasuk nama, posisi di Indeks TIOBE, dan jumlah kursus tentang bahasa pemrograman tersebut di Datacamp. Tabelnya terlihat seperti ini:

Bayangkan Anda ingin menggabungkan kedua tabel untuk mendapatkan informasi kursus beserta posisi bahasa pada indeks TIOBE. Kita akan menggunakan INNER JOIN untuk hanya mendapatkan informasi bahasa pemrograman yang muncul di tabel datacamp_course. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Ini hanyalah satu contoh, namun potensi join SQL tidak terbatas, sehingga menjadi topik yang wajib dikuasai. Kursus, tutorial, dan cheatsheet kami tentang join akan membantu Anda memulai.

Memulai dengan SQLAlchemy

Psycopg2 adalah alat yang hebat untuk terhubung dengan database PostgreSQL. Tetapi bagaimana jika Anda harus bekerja dengan database yang di-host di berbagai database SQL, seperti Oracle dan MySQL? Dalam skenario ini, mungkin lebih baik menggunakan alat yang dapat terhubung ke berbagai jenis database SQL. Untuk itu, SQLAlchemy adalah kandidat yang hebat. 

Sederhananya, SQLAlchemy memungkinkan pengguna menghubungkan database menggunakan bahasa Python, menjalankan kueri SQL dengan pemrograman berbasis objek, dan menyederhanakan alur kerja. 

Meskipun sintaksnya berbeda dibandingkan psycopg2, Anda dapat melakukan hampir semua hal yang sama. Di bawah ini Anda dapat menemukan contoh untuk membuat engine guna terhubung dengan dataset datacamp_courses kita, dan membaca semua data yang tersedia di tabel datacap_course

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy adalah alat hebat lainnya yang layak dipelajari. Untuk melihat sekilas cara kerjanya, lihat Tutorial SQLALchemy kami.  

Menggunakan PostgreSQL di DataCamp DataLab

Di DataCamp, kami bekerja keras untuk memudahkan data science dan membuatnya dapat diakses semua orang. Itulah alasan di balik pembuatan DataCamp DataLab, notebook berbasis cloud yang dirancang khusus untuk data science. Anda dapat menganggap DataCamp DataLab sebagai perpaduan Jupyter Notebook dan Google Docs, yang memungkinkan pengguna menulis kode, menganalisis data secara individu atau kolaboratif, dan berbagi wawasan data.

Menghubungkan DataLab ke database PostgreSQL, serta database SQL lainnya, sangat mudah. DataLab memungkinkan Anda terhubung ke sumber data secara aman. Pada saat yang sama, Anda dapat dengan mulus melanjutkan analisis data dengan Python atau R. Anda hanya perlu membuat sebuah integrasi, mengikuti proses tanpa kode yang sederhana, di mana Anda perlu memasukkan kredensial koneksi Anda.

Terakhir, jika Anda ingin mencoba integrasi tetapi tidak memiliki database PostgreSQL untuk dihubungkan, Anda dapat menggunakan salah satu database PostgreSQL contoh kami!

Penasaran dengan DataCamp DataLab? Lihat artikel ini untuk mengetahui lebih lanjut tentang notebook generasi berikutnya untuk data science.

Kesimpulan

Kerja bagus! Dalam tutorial ini, Anda berkenalan dengan PostgreSQL dan mempelajari cara mengelola database tanpa meninggalkan Python, menggunakan paket psycopg2. Namun ini baru awal perjalanan. SQL adalah salah satu bahasa pemrograman yang paling banyak digunakan dalam data science. Bahasa ini juga cukup mudah dipelajari, jadi tidak ada alasan lagi.  

Untungnya, Datacamp siap membantu. Berikut beberapa sumber daya yang dapat membantu Anda menjadi mahir dalam PostgreSQL dan SQL secara umum:

• Tutorial Pengenalan PostgreSQL untuk Pemula
• Kursus Creating PostgreSQL Databases
• Kursus Improving Query Performance in PostgreSQL
• Kursus Managing Databases in SQL
• Kursus Functions for Manipulating Data in PostgreSQL