Pengantar Basis Data di Python
BasicSkill Level
4 jam
102.1K learners
Apa itu SQL
Structured Query Language (SQL) adalah bahasa yang paling umum digunakan untuk menjalankan beragam tugas analisis data. SQL juga digunakan untuk memelihara basis data relasional, misalnya: menambahkan tabel, menghapus nilai, dan mengoptimalkan basis data. Basis data relasional sederhana terdiri dari beberapa tabel yang saling terhubung, dan setiap tabel terdiri dari baris dan kolom.
Rata-rata, perusahaan teknologi menghasilkan jutaan titik data setiap hari. Diperlukan solusi penyimpanan yang andal dan efektif agar mereka dapat menggunakan data tersebut untuk meningkatkan sistem saat ini atau menghadirkan produk baru. Basis data relasional seperti MySQL, PostgreSQL, dan SQLite menyelesaikan masalah ini dengan menyediakan pengelolaan basis data yang andal, keamanan, dan performa tinggi.
Fungsi inti SQL
- Membuat tabel baru dalam basis data
- Menjalankan kueri terhadap basis data
- Mengambil data dari basis data
- Menyisipkan rekaman ke dalam basis data
- Memperbarui rekaman dalam basis data
- Menghapus rekaman dari basis data
- Mengoptimalkan basis data apa pun
SQL adalah keterampilan yang sangat diminati yang akan membantu Anda mendapatkan pekerjaan di industri teknologi. Perusahaan seperti Meta, Google, dan Netflix selalu mencari profesional data yang dapat mengekstrak informasi dari basis data SQL dan menghadirkan solusi inovatif. Anda dapat mempelajari dasar-dasar SQL dengan mengikuti tutorial Introduction to SQL di DataCamp.
Mengapa menggunakan SQL dengan Python dan R?
SQL dapat membantu kita mengetahui kinerja perusahaan, memahami perilaku pelanggan, dan memantau metrik kesuksesan kampanye pemasaran. Sebagian besar analis data dapat melakukan mayoritas tugas business intelligence dengan menjalankan kueri SQL, lalu mengapa kita memerlukan alat seperti PoweBI, Python, dan R? Dengan kueri SQL, Anda dapat mengetahui apa yang terjadi di masa lalu, tetapi Anda tidak dapat memprediksi proyeksi masa depan. Alat-alat ini membantu kita memahami lebih banyak tentang kinerja saat ini dan potensi pertumbuhan.
Python dan R adalah bahasa serbaguna yang memungkinkan profesional menjalankan analisis statistik tingkat lanjut, membangun model machine learning, membuat API data, dan pada akhirnya membantu perusahaan berpikir melampaui KPI. Dalam tutorial ini, kita akan belajar menyambungkan basis data SQL, mengisi basis data, dan menjalankan kueri SQL menggunakan Python dan R.
Catatan: Jika Anda baru memulai dengan SQL, ikuti SQL skill track untuk memahami dasar-dasar penulisan kueri SQL.
Tutorial Python
Tutorial Python akan membahas dasar-dasar menyambungkan ke berbagai basis data (MySQL dan SQLite), membuat tabel, menambahkan rekaman, menjalankan kueri, dan mempelajari fungsi Pandas read_sql.
Penyiapan
Kita dapat menyambungkan basis data menggunakan SQLAlchemy, tetapi dalam tutorial ini, kita akan menggunakan paket bawaan Python SQLite3 untuk menjalankan kueri pada basis data. SQLAlchemy menyediakan dukungan untuk semua jenis basis data dengan menyediakan API terpadu. Jika Anda tertarik mempelajari lebih lanjut tentang SQLAlchemy dan cara kerjanya dengan basis data lain, lihat kursus Introduction to Databases in Python.
MySQL adalah mesin basis data paling populer di dunia, dan banyak digunakan oleh perusahaan seperti Youtube, Paypal, LinkedIn, dan GitHub. Di sini kita akan belajar cara menyambungkan basis data. Langkah-langkah lainnya untuk menggunakan MySQL serupa dengan paket SQLite3.
Pertama, instal paket mysql menggunakan '!pip install mysql' lalu buat mesin basis data lokal dengan memberikan nama pengguna, kata sandi, dan nama basis data Anda.
import mysql.connector as sql
conn = sql.connect(
host="localhost",
user="abid",
password="12345",
database="datacamp_python"
)
Serupa dengan itu, kita dapat membuat atau memuat basis data SQLite dengan menggunakan fungsi sqlite3.connect. SQLite adalah pustaka yang mengimplementasikan mesin basis data yang mandiri, tanpa konfigurasi, dan tanpa server. SQLite ramah DataLab, jadi kita akan menggunakannya dalam proyek kita untuk menghindari kesalahan local host.
import sqlite3
import pandas as pd
conn= sqlite3.connect("datacamp_python.db")
Mulai Belajar Python dan SQL Secara Gratis
Dalam kursus ini, Anda akan mempelajari dasar-dasar basis data relasional dan cara berinteraksi dengannya.
Pengantar SQL
BasicSkill Level
2 jam
1.6M learners
Membuat Basis Data
Di bagian ini, kita akan mempelajari cara memuat dataset dampak COVID-19 pada lalu lintas bandara, di bawah lisensi CC BY-NC-SA 4.0, ke dalam basis data SQLite kita. Kita juga akan mempelajari cara membuat tabel dari awal.
Dataset lalu lintas bandara berisi persentase volume lalu lintas selama periode dasar dari 1 Februari 2020 hingga 15 Maret 2020. Kita akan memuat file CSV menggunakan fungsi Pandas read_csv dan kemudian menggunakan fungsi to_sql untuk mentransfer dataframe ke tabel SQLite kita. Fungsi to_sql memerlukan nama tabel (String) dan koneksi ke mesin SQLite.
data = pd.read_csv("data/covid_impact_on_airport_traffic.csv")
data.to_sql(
'airport', # Name of the sql table
conn, # sqlite.Connection or sqlalchemy.engine.Engine
if_exists='replace'
)
Sekarang kita akan menguji apakah kita berhasil dengan menjalankan kueri SQL singkat. Sebelum menjalankan kueri, kita perlu membuat cursor yang akan membantu mengeksekusi kueri, seperti yang ditunjukkan pada blok kode di bawah. Anda dapat memiliki beberapa cursor pada basis data yang sama dalam satu koneksi.
Pada kasus kita, kueri SQL mengembalikan tiga kolom dan lima baris dari tabel airport. Untuk menampilkan baris pertama, kita akan menggunakan cursor.fetchone().
cursor = conn.cursor()
cursor.execute("""SELECT Date, AirportName, PercentOfBaseline
FROM airport
LIMIT 5""")
cursor.fetchone()
>>> ('2020-04-03', 'Kingsford Smith', 64)
Untuk menampilkan sisa rekaman, kita akan menggunakan cursor.fetchall(). Dataset bandara berhasil dimuat ke dalam basis data hanya dengan beberapa baris kode.
cursor.fetchall()
>>> [('2020-04-13', 'Kingsford Smith', 29),
('2020-07-10', 'Kingsford Smith', 54),
('2020-09-02', 'Kingsford Smith', 18),
('2020-10-31', 'Kingsford Smith', 22)]
Sekarang, mari belajar membuat tabel dari awal dan mengisinya dengan menambahkan nilai sampel. Kita akan membuat tabel studentinfo dengan id (integer, primary key, auto-increment), name (teks), dan subject (teks).
Catatan: Sintaks SQLite sedikit berbeda. Disarankan untuk melihat lembar contekan SQLite guna memahami kueri SQL yang disebutkan dalam tutorial ini.
cursor.execute("""
CREATE TABLE studentinfo
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
subject TEXT
)
""")
Mari kita periksa berapa banyak tabel yang telah kita tambahkan ke basis data dengan menjalankan kueri SQLite sederhana.
cursor.execute("""
SELECT name
FROM sqlite_master
WHERE type='table'
""")
cursor.fetchall()
>>> [('airport',), ('studentinfo',)]
Menjalankan Kueri
Pada bagian ini, kita akan menambahkan nilai ke tabel studentinfo dan menjalankan kueri SQL sederhana. Dengan menggunakan INSERT INTO, kita dapat menambahkan satu baris ke tabel studentinfo.
Untuk menyisipkan nilai, kita perlu memberikan argumen query dan value ke fungsi execute. Fungsi tersebut akan mengisi input “?” dengan nilai yang kita berikan.
query = """
INSERT INTO studentinfo
(name, subject)
VALUES (?, ?)
"""
value = ("Marry", "Math")
cursor.execute(query,value)
Ulangi kueri di atas dengan menambahkan beberapa rekaman.
query = """
INSERT INTO studentinfo
(name, subject)
VALUES (?, ?)
"""
values = [("Abid", "Stat"),
("Carry", "Math"),
("Ali","Data Science"),
("Nisha","Data Science"),
("Matthew","Math"),
("Henry","Data Science")]
cursor.executemany(query,values)
Sudah saatnya memverifikasi rekaman. Untuk itu kita akan menjalankan kueri SQL sederhana yang akan mengembalikan baris di mana subject adalah Data Science.
cursor.execute("""
SELECT *
FROM studentinfo
WHERE subject LIKE 'Data Science'
""")
cursor.fetchall()
>>> [(4, 'Ali', 'Data Science'),
(5, 'Nisha', 'Data Science'),
(7, 'Henry', 'Data Science')]
Perintah DISTINCT subject digunakan untuk menampilkan nilai unik yang ada pada kolom subject. Pada kasus kita, nilainya adalah Math, Stat, dan Data Science.
cursor.execute("SELECT DISTINCT subject from studentinfo")
cursor.fetchall()
>>> [('Math',), ('Stat',), ('Data Science',)]
Untuk menyimpan semua perubahan, kita akan menggunakan fungsi commit(). Tanpa commit, data akan hilang setelah mesin dimulai ulang.
conn.commit()
SQL dengan Pandas
Di bagian ini kita akan mempelajari cara mengekstrak data dari basis data SQLite dan mengonversinya menjadi dataframe Pandas hanya dengan satu baris kode. read_sql menyediakan lebih dari sekadar menjalankan kueri SQL. Kita dapat menggunakannya untuk mengatur kolom indeks, mengurai tanggal dan waktu, menambahkan nilai, dan memfilter nama kolom. Pelajari lebih lanjut tentang mengimpor data di Python dengan mengikuti kursus singkat DataCamp.
read_sql memerlukan dua argumen: kueri SQL dan koneksi ke mesin SQLite. Keluaran berisi lima baris teratas dari tabel airport tempat PercentOfBaseline lebih besar dari 20.
data_sql_1 = pd.read_sql("""
SELECT Date,City,PercentOfBaseline
FROM airport
WHERE PercentOfBaseline > 20
ORDER BY Date DESC
""",
conn)
print(data_sql_1.head())
Date City PercentOfBaseline
0 2020-12-02 Sydney 27
1 2020-12-02 Santiago 48
2 2020-12-02 Calgary 99
3 2020-12-02 Leduc County 100
4 2020-12-02 Richmond 86
Menjalankan analisis data pada basis data relasional menjadi lebih mudah dengan integrasi Pandas. Kita juga dapat menggunakan data ini untuk memprediksi nilai dan menjalankan analisis statistik yang kompleks.
Fungsi plot digunakan untuk memvisualisasikan histogram kolom PercentOfBaseline.
data_sql_1.plot(y="PercentOfBaseline",kind="hist");
Demikian pula, kita dapat membatasi nilai ke 20 teratas dan menampilkan bagan garis deret waktu.
data_sql_2 = pd.read_sql("""
SELECT Date,City,PercentOfBaseline
FROM airport
WHERE PercentOfBaseline > 20
ORDER BY Date DESC
LIMIT 20
""",
conn)
data_sql_2.plot(x="Date",y="PercentOfBaseline",kind="line");
Terakhir, kita akan menutup koneksi untuk membebaskan sumber daya. Sebagian besar paket melakukan ini secara otomatis, tetapi sebaiknya tutup koneksi setelah menyelesaikan perubahan.
conn.close()
Tutorial R
Kita akan mereplikasi semua tugas dari tutorial Python menggunakan R. Tutorial ini mencakup membuat koneksi, menulis tabel, menambahkan baris, menjalankan kueri, dan analisis data dengan dplyr.
Penyiapan
Paket DBI digunakan untuk menyambungkan ke basis data paling populer seperti MariaDB, Postgres, Duckdb, dan SQLite. Misalnya, instal paket RMySQL dan buat basis data dengan memberikan nama pengguna, kata sandi, nama basis data, dan alamat host.
install.packages("RMySQL")
library(RMySQL)
conn = dbConnect(
MySQL(),
user = 'abid',
password = '1234',
dbname = 'datacamp_R',
host = 'localhost'
)
Dalam tutorial ini, kita akan membuat basis data SQLite dengan memberikan nama dan fungsi SQLite.
library(RSQLite)
library(DBI)
library(tidyverse)
conn = dbConnect(SQLite(), dbname = 'datacamp_R.db')
Membuat Basis Data
Dengan mengimpor pustaka tidyverse, kita akan memiliki akses ke dplyr, ggplot, dan dataset bawaan.
Fungsi dbWriteTable menerima data.frame dan menambahkannya ke tabel SQL. Fungsi ini menerima tiga argumen: koneksi ke SQLite, nama tabel, dan data frame. Dengan dbReadTable, kita dapat melihat seluruh tabel. Untuk melihat 6 baris teratas, kita menggunakan head.
dbWriteTable(conn, "cars", mtcars)
head(dbReadTable(conn, "cars"))
mpg cyl disp hp drat wt qsec vs am gear carb
1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1
dbExecute memungkinkan kita mengeksekusi kueri SQLite apa pun, jadi kita akan menggunakannya untuk membuat tabel bernama idcard.
Untuk menampilkan nama tabel dalam basis data, kita akan menggunakan dbListTables.
dbExecute(conn, 'CREATE TABLE idcard (id int, name text)')
dbListTables(conn)
>>> 'cars''idcard'
Mari tambahkan satu baris ke tabel idcard dan gunakan dbGetQuery untuk menampilkan hasil.
Catatan: dbGetQuery menjalankan kueri dan mengembalikan rekaman sedangkan dbExecute menjalankan kueri SQL tetapi tidak mengembalikan rekaman apa pun.
dbExecute(conn, "INSERT INTO idcard (id,name)\
VALUES(1,'love')")
dbGetQuery(conn,"SELECT * FROM idcard")
id name
1 love
Sekarang kita akan menambahkan dua baris lagi dan menampilkan hasil dengan menggunakan dbReadTable.
dbExecute(conn,"INSERT INTO idcard (id,name)\
VALUES(2,'Kill'),(3,'Game')
")
dbReadTable(conn,'idcard')
id name
1 love
2 Kill
3 Game
dbCreateTable memungkinkan kita membuat tabel tanpa repot. Fungsi ini memerlukan tiga argumen; koneksi, nama tabel, dan karakter vektor atau data.frame. Karakter vektor terdiri dari nama (nama kolom) dan nilai (tipe). Pada kasus kita, kita akan memberikan data.frame populasi bawaan untuk membuat struktur awal.
dbCreateTable(conn,'population',population)
dbReadTable(conn,'population')
country year population
Kemudian, kita akan menggunakan dbAppendTable untuk menambahkan nilai ke tabel population.
dbAppendTable(conn,'population',head(population))
dbReadTable(conn,'population')
country year population
Afghanistan 1995 17586073
Afghanistan 1996 18415307
Afghanistan 1997 19021226
Afghanistan 1998 19496836
Afghanistan 1999 19987071
Afghanistan 2000 20595360
Menjalankan Kueri
Kita akan menggunakan dbGetQuery untuk melakukan semua tugas analitik data kita. Mari coba menjalankan kueri sederhana dan kemudian mempelajari fungsi lainnya.
dbGetQuery(conn,"SELECT * FROM idcard")
id name
1 love
2 Kill
3 Game
Anda juga dapat menjalankan kueri SQL yang kompleks untuk memfilter horsepower dan menampilkan baris serta kolom yang terbatas.
dbGetQuery(conn, "SELECT mpg,hp,gear\
FROM cars\
WHERE hp > 50\
LIMIT 5")
mpg hp gear
21.0 110 4
21.0 110 4
22.8 93 4
21.4 110 3
18.7 175 3
Untuk menghapus tabel, gunakan dbRemoveTable. Seperti yang dapat kita lihat sekarang, kita telah berhasil menghapus tabel idcard.
dbRemoveTable(conn,'idcard')
dbListTables(conn)
>>> 'cars''population'
Untuk memahami lebih jauh tentang tabel, kita akan menggunakan dbListFields yang akan menampilkan nama kolom dalam tabel tertentu.
dbListFields(conn, "cars")
>>> 'mpg''cyl''disp''hp''drat''wt''qsec''vs''am''gear''carb'
SQL dengan dplyr
Pada bagian ini, kita akan menggunakan dplyr untuk membaca tabel lalu menjalankan kueri menggunakan filter, select, dan collect. Jika Anda tidak ingin mempelajari sintaks SQL dan ingin melakukan semua tugas menggunakan R murni, maka metode ini untuk Anda. Kita telah mengambil tabel cars, memfilternya berdasarkan gears dan mpg, lalu memilih tiga kolom seperti di bawah ini.
cars_results <-
tbl(conn, "cars") %>%
filter(gear %in% c(4, 3),
mpg >= 14,
mpg <= 21) %>%
select(mpg, hp, gear) %>%
collect()
cars_results
mpg hp gear
21.0 110 4
21.0 110 4
18.7 175 3
18.1 105 3
14.3 245 3
... ... ...
Kita dapat menggunakan data frame yang telah difilter untuk menampilkan grafik boxplot menggunakan ggplot.
ggplot(cars_results,aes(fill=as.character(gear), y=mpg)) +
geom_boxplot()
Atau kita dapat menampilkan grafik titik facet yang dibagi berdasarkan jumlah gears.
ggplot(cars_results,
aes(mpg, ..count.. ) ) +
geom_point(stat = "count", size = 4) +
coord_flip()+
facet_grid( as.character(gear) ~ . )
Kesimpulan
Pada tutorial ini, kita telah mempelajari pentingnya menjalankan kueri SQL dengan Python dan R, membuat basis data, menambahkan tabel, dan melakukan analisis data menggunakan kueri SQL. Kita juga telah mempelajari bagaimana Pandas dan dplyr membantu kita menjalankan kueri hanya dengan satu baris kode.
SQL adalah keterampilan wajib dipelajari untuk semua pekerjaan terkait teknologi. Jika Anda memulai karier sebagai analis data, kami merekomendasikan untuk menyelesaikan jalur karier Data Analyst with SQL Server dalam dua bulan. Jalur karier ini akan mengajarkan Anda segala hal tentang kueri SQL, server, dan pengelolaan sumber daya.
Anda dapat menjalankan semua skrip yang digunakan dalam tutorial ini secara gratis:
- Proyek SQL dengan Python tersedia di buku kerja DataLab ini.
- Proyek SQL dengan R tersedia di buku kerja DataLab ini.
