SQLite di R

Seperti disebutkan di akhir tutorial sebelumnya, Panduan Pemula untuk SQLite, basis data SQLite paling kuat saat digunakan bersama R dan Python. Sejauh ini di DataCamp, Anda telah diperkenalkan cara beroperasi dengan basis data SQLite dari Python (lihat tutorial SQLite di Python oleh Sayak Paul untuk mempelajari cara memanipulasi basis data SQLite melalui paket sqlite3 di Python). Namun, dalam tutorial ini, kita akan berfokus pada cara menggunakan basis data SQLite di R dengan paket RSQLite.

Kita akan membahas dasar-dasar cara melakukan tugas penting seperti mengirim kueri ke basis data SQLite atau membuat tabel menggunakan RSQLite. Selain itu, saya akan membahas cara menggunakan kueri berparameter serta operasi seperti INSERT atau DELETE yang tidak mengembalikan hasil berbentuk tabel.

Membuat Basis Data dan Tabel

Langkah pertama, seperti yang mungkin Anda tebak, adalah membuat basis data. RSQLite dapat membuat basis data SQLite sementara di memori yang bersifat transien, seperti saat Anda membuka baris perintah SQLite. Namun, ini biasanya bukan yang Anda inginkan, jadi mari kita buat basis data yang layak untuk dataset mtcars menggunakan fungsi dbConnect(), yang menerima argumen berikut:

• drv: Pengandar basis data
• path: Jalur ke basis data SQLite. Jika Anda membuat yang baru, cukup beri nama sesuai pilihan Anda seperti yang saya lakukan di bawah. Jika Anda ingin bekerja dengan basis data sementara di memori, Anda dapat mengabaikan argumen path atau mengetik ":memory:").

# Load the RSQLite Library
library(RSQLite)
# Load the mtcars as an R data frame put the row names as a column, and print the header.
data("mtcars")
mtcars$car_names <- rownames(mtcars)
rownames(mtcars) <- c()
head(mtcars)
# Create a connection to our new database, CarsDB.db
# you can check that the .db file has been created on your working directory
conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")
Didukung oleh

Setelah basis data dibuat dan data Anda dalam bentuk yang tepat, Anda dapat langsung membuat tabel di dalam basis data menggunakan fungsi dbWriteTable(). Fungsi ini dapat menerima banyak argumen, tetapi untuk saat ini mari fokus pada yang berikut:

• conn: Koneksi ke basis data SQLite Anda
• name: Nama yang ingin Anda gunakan untuk tabel Anda
• value: Data yang ingin Anda masukkan. Harus berupa data frame R atau objek yang dapat dikonversi menjadi data frame R.

Setelah itu, Anda dapat menggunakan fungsi dbListTables() dengan argumen koneksi basis data SQLite untuk memeriksa bahwa Anda telah berhasil membuat tabel.

# Write the mtcars dataset into a table names mtcars_data
dbWriteTable(conn, "cars_data", mtcars)
# List all the tables available in the database
dbListTables(conn)
Didukung oleh

'cars_data'

Fitur yang sangat berguna saat membuat tabel menggunakan RSQLite adalah Anda dapat menambahkan lebih banyak data ke tabel yang sudah ada menggunakan loop jika Anda memiliki beberapa data frame dengan menetapkan argumen opsional append = TRUE di dalam dbWriteTable(). Misalnya, mari kita buat tabel contoh baru berisi beberapa mobil dan pabrikannya dengan menambahkan dua data frame berbeda:

# Create toy data frames
car <- c('Camaro', 'California', 'Mustang', 'Explorer')
make <- c('Chevrolet','Ferrari','Ford','Ford')
df1 <- data.frame(car,make)
car <- c('Corolla', 'Lancer', 'Sportage', 'XE')
make <- c('Toyota','Mitsubishi','Kia','Jaguar')
df2 <- data.frame(car,make)
# Add them to a list
dfList <- list(df1,df2)
# Write a table by appending the data frames inside the list
for(k in 1:length(dfList)){
    dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)
}
# List all the Tables
dbListTables(conn)
Didukung oleh

1. 'Cars_and_Makes'
2. 'cars_data'

Sekarang mari pastikan semua data ada di tabel baru:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")
Didukung oleh

Menjalankan Kueri SQL

Seperti yang Anda lihat di atas, memungkinkan untuk menjalankan kueri SQL yang valid melalui RSQLite menggunakan fungsi dbGetQuery(), yang memiliki argumen berikut:

• conn: Koneksi ke Basis Data SQLite
• query: Kueri SQL yang ingin Anda jalankan sebagai string

Untuk lebih menunjukkan kemampuan menjalankan kueri SQL dengan RSQLite, mari kita bahas beberapa contoh kueri lagi pada tabel cars_data:

Catatan: Penting untuk diingat bahwa melalui RSQLite, Anda dapat menjalankan kueri apa pun yang valid untuk basis data SQLite, mulai dari pernyataan SELECT sederhana hingga JOIN (Kecuali RIGHT OUTER JOIN dan FULL OUTER JOIN, yang tidak diizinkan di SQLite).

# Gather the first 10 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
Didukung oleh

# Get the car names and horsepower of the cars with 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE cyl = 8")
Didukung oleh

# Get the car names and horsepower starting with M that have 6 or 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE car_names LIKE 'M%' AND cyl IN (6,8)")
Didukung oleh

# Get the average horsepower and mpg by number of cylinder groups
dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp', AVG(mpg) AS 'average_mpg' FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")
Didukung oleh

Untuk menyimpan hasil kueri Anda agar dapat melakukan operasi lebih lanjut di R sebagai data frame, caranya sesederhana menetapkan hasil kueri Anda ke sebuah variabel.

avg_HpCyl <- dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp'FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")
avg_HpCyl
class(avg_HpCyl)
Didukung oleh

'data.frame'

Menyisipkan Variabel ke dalam Kueri (Kueri Berparameter)

Salah satu keunggulan terbesar bekerja dengan basis data SQLite dari R adalah kemampuan menggunakan kueri berparameter. Artinya, kemampuan untuk mengambil variabel yang tersedia di ruang kerja R Anda dan menggunakannya untuk mengkueri basis data SQLite Anda. Berikut contoh cara menggunakan variabel di dalam kueri SQLite:

# Lets assume that there is some user input that asks us to look only into cars that have over 18 miles per gallon (mpg)
# and more than 6 cylinders
mpg <-  18
cyl <- 6
Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result
Didukung oleh

Seperti yang Anda lihat, satu-satunya perbedaan antara mengirim kueri biasa dan kueri berparameter terletak pada nilai placeholder di dalam kueri (>= ?) dan argumen params dari dbGetQuery(), yang menerima list atau vektor berisi nilai yang ingin Anda tetapkan ke placeholder (dalam hal ini kita memiliki vektor yang berisi variabel mpg dan cyl).

Sekarang, bagaimana jika Anda ingin menjalankan kueri yang berbeda? Pada contoh di atas, seperti yang Anda lihat, saya cukup menyusun kueri secara manual. Kueri itu hanya memungkinkan masukan nilai mpg dan cyl, dan kueri hanya akan mengambil mobil dengan mpg dan cyl yang lebih tinggi atau sama. Namun, mungkin ada situasi ketika Anda ingin lebih fleksibel. Bagaimana jika seorang pengguna ingin melihat mobil dengan tenaga kuda dan bobot yang lebih tinggi atau sama dari nilai tertentu juga? Dengan kueri di atas, Anda harus kembali dan menulis ulang, tetapi Anda dapat menulis fungsi yang membuat langkah itu tidak diperlukan. Mari kita lihat salah satunya:

# Assemble an example function that takes the SQLite database connection, a base query,
# and the parameters you want to use in the WHERE clause as a list
assembleQuery <- function(conn, base, search_parameters){
    parameter_names <- names(search_parameters)
    partial_queries <- ""
    # Iterate over all the parameters to assemble the query
    for(k in 1:length(parameter_names)){
        filter_k <- paste(parameter_names[k], " >= ? ")
        # If there is more than 1 parameter, add an AND statement before the parameter name and placeholder
        if(k > 1){
            filter_k <- paste("AND ", parameter_names[k], " >= ?")
        }
        partial_queries <- paste(partial_queries, filter_k)
    }
    # Paste all together into a single query using a WHERE statement
    final_paste <- paste(base, " WHERE", partial_queries)
    # Print the assembled query to show how it looks like
    print(final_paste)
    # Run the final query. I unlist the values from the search_parameters list into a vector since it is needed
    # when using various anonymous placeholders (i.e. >= ?)
    values <- unlist(search_parameters, use.names = FALSE)
    result <- dbGetQuery(conn, final_paste, params = values)
    # return the executed query
    return(result)
}

base <- "SELECT car_names, mpg, hp, wt FROM cars_data"
search_parameters <- list("mpg" = 16, "hp" = 150, "wt" = 2.1)
result <- assembleQuery(conn, base, search_parameters)
result
Didukung oleh

[1] "SELECT car_names, mpg, hp, wt FROM cars_data  WHERE  mpg  >= ?  AND  hp  >= ? AND  wt  >= ?"
Didukung oleh

Fungsi di atas mungkin sederhana, tetapi mengilustrasikan bagaimana memungkinkan menulis kode R yang menghasilkan kueri SQL yang dapat dijalankan pada basis data SQLite. Saya mendorong Anda untuk terus mengeksplorasi jika Anda tertarik pada kasus penggunaan ini. Fungsi di atas dapat ditingkatkan dalam beberapa cara, dimulai dengan menghilangkan asumsi bahwa pengguna yang memberikan parameter pasti ingin melihat nilai yang lebih tinggi atau sama dari yang dimasukkan.

Pernyataan yang Tidak Mengembalikan Hasil Berbentuk Tabel

Terkadang, Anda mungkin ingin menjalankan kueri SQL yang tidak selalu mengembalikan data berbentuk tabel. Contoh operasi ini adalah menyisipkan, memperbarui, atau menghapus rekaman tabel. Untuk ini, kita dapat menggunakan fungsi dbExecute(), yang menerima argumen koneksi basis data SQLite dan kueri SQL. Beberapa contoh dapat dilihat di bawah:

# Visualize the table before deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
# Delete the column belonging to the Mazda RX4. You will see a 1 as the output.
dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")
# Visualize the new table after deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
Didukung oleh

1

Sekarang mari coba menyisipkan kembali data Mazda RX4:

# Insert the data for the Mazda RX4. This will also ouput a 1
dbExecute(conn, "INSERT INTO cars_data VALUES (21.0,6,160.0,110,3.90,2.620,16.46,0,1,4,4,'Mazda RX4')")
# See that we re-introduced the Mazda RX4 succesfully at the end
dbGetQuery(conn, "SELECT * FROM cars_data")
Didukung oleh

1

Seperti yang Anda lihat, baris kode terakhir menambahkan Mazda RX4 ke akhir tabel. Setelah Anda selesai bekerja dengan basis data SQLite dalam R, penting untuk memanggil fungsi dbDisconnect(). Ini memastikan kita melepaskan sumber daya apa pun yang digunakan oleh koneksi basis data, yang selalu merupakan praktik yang baik.

# Close the database connection to CarsDB
dbDisconnect(conn)
Didukung oleh

Kesimpulan

Dalam tutorial ini, kita telah membahas fungsi-fungsi penting yang diperlukan untuk memanipulasi basis data SQLite di R menggunakan RSQLite. Basis data SQLite, jika digunakan dengan tepat, adalah alat yang sangat bermanfaat saat digabungkan di dalam skrip R Anda. Contoh kekuatan persilangan antara R dan SQLite dapat dilihat pada kueri berparameter, yang dapat digunakan jika Anda perlu mengkueri basis data untuk menampilkan informasi berdasarkan masukan pengguna di aplikasi R Shiny. Contoh kasus penggunaan lain untuk kueri berparameter bisa berupa asisten virtual atau chatbot. Jika Anda ingin mempelajari lebih lanjut tentang ini, saya mendorong Anda untuk melihat kursus DataCamp Building Chatbots with Python.

Menulis tabel SQLite dengan menambahkan data frame juga bisa sangat kuat. Seperti yang saya catat dalam tutorial Panduan Pemula untuk SQLite, kemampuan ini memungkinkan saya menyelesaikan proyek analisis jaringan sosial dengan menambahkan pengikut dari beberapa pengguna Twitter yang diminati ke sebuah tabel seiring saya mengumpulkannya. Saya menggunakan basis data SQLite untuk ini, guna menghemat waktu dengan menghindari kebutuhan untuk memulai dari awal jika terjadi pemadaman listrik atau pembaruan Windows, yang dapat memaksa komputer saya mati. Jika hal seperti itu terjadi, yang perlu saya lakukan hanyalah melanjutkan mengumpulkan pengikut dari pengguna yang posisinya setelah yang terakhir saya miliki di basis data. Untuk memberi gambaran, waktu komputasi untuk mengumpulkan pengikut semua pengguna Twitter yang diminati memakan waktu hampir 4 minggu karena batas laju API Twitter. Oleh karena itu, memulai dari nol akibat pemadaman listrik atau pembaruan Windows akan sangat tidak diinginkan.

Seperti biasa, saya mendorong Anda untuk terus belajar tentang basis data SQLite dan cara berinteraksi dengannya melalui R. Saya sangat mendorong Anda untuk menyelami lebih dalam dan memeriksa tutorial sebelumnya yang membahas penggunaannya dengan Python dan baris perintah, yang dapat semakin membantu Anda menjadi ahli SQLite. Terus belajar; langit adalah batasnya!