SQLite trong R

Như đã đề cập ở cuối hướng dẫn trước của tôi, Hướng dẫn cho người mới bắt đầu với SQLite, cơ sở dữ liệu SQLite phát huy tối đa sức mạnh khi sử dụng cùng R và Python. Trên DataCamp, bạn đã được làm quen với cách thao tác cơ sở dữ liệu SQLite từ Python (xem hướng dẫn SQLite trong Python của Sayak Paul để học cách thao tác cơ sở dữ liệu SQLite qua gói sqlite3 trong Python). Tuy nhiên, trong hướng dẫn này, chúng ta sẽ tập trung vào cách dùng cơ sở dữ liệu SQLite trong R với gói RSQLite.

Chúng ta sẽ điểm qua những điều cơ bản để thực hiện các tác vụ thiết yếu như gửi truy vấn đến cơ sở dữ liệu SQLite hoặc tạo bảng bằng RSQLite. Bên cạnh đó, tôi sẽ trình bày cách sử dụng truy vấn tham số hóa và các thao tác như INSERT hoặc DELETE vốn không trả về kết quả dạng bảng.

Tạo Cơ sở dữ liệu và Bảng

Bước đầu tiên, như bạn có thể đoán, là tạo một cơ sở dữ liệu. RSQLite có thể tạo các cơ sở dữ liệu SQLite thoáng qua trong bộ nhớ, tương tự khi bạn mở dòng lệnh SQLite. Tuy nhiên, đây thường không phải điều bạn muốn, nên hãy tạo một cơ sở dữ liệu đúng nghĩa cho bộ dữ liệu mtcars bằng hàm dbConnect(), hàm này nhận các đối số sau:

• drv: Trình điều khiển cơ sở dữ liệu
• path: Đường dẫn đến cơ sở dữ liệu SQLite. Nếu bạn tạo cơ sở dữ liệu mới, chỉ cần đặt một tên tùy chọn như tôi làm bên dưới. Nếu bạn muốn làm việc với cơ sở dữ liệu thoáng qua trong bộ nhớ, bạn có thể bỏ qua đối số path hoặc gõ ":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")
Được hỗ trợ bởi

Khi đã tạo cơ sở dữ liệu và chuẩn hóa dữ liệu, bạn có thể tạo một bảng bên trong cơ sở dữ liệu bằng hàm dbWriteTable(). Hàm này có thể nhận nhiều đối số, nhưng trước mắt, hãy tập trung vào các đối số sau:

• conn: Kết nối tới cơ sở dữ liệu SQLite của bạn
• name: Tên bạn muốn dùng cho bảng
• value: Dữ liệu bạn muốn chèn. Nên là một data frame trong R hoặc đối tượng có thể ép kiểu thành data frame R.

Sau đó, bạn có thể dùng hàm dbListTables() với đối số là kết nối tới cơ sở dữ liệu SQLite để kiểm tra xem bảng đã được tạo thành công hay chưa.

# 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)
Được hỗ trợ bởi

'cars_data'

Một tính năng cực kỳ hữu ích khi tạo bảng bằng RSQLite là bạn có thể nối thêm dữ liệu vào một bảng hiện có bằng vòng lặp trong trường hợp có nhiều data frame, bằng cách đặt đối số tùy chọn append = TRUE trong dbWriteTable(). Ví dụ, hãy tạo một bảng thử với một số mẫu xe và hãng sản xuất bằng cách nối hai data frame khác nhau:

# 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)
Được hỗ trợ bởi

1. 'Cars_and_Makes'
2. 'cars_data'

Giờ hãy đảm bảo rằng mọi dữ liệu đều đã có trong bảng mới:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")
Được hỗ trợ bởi

Thực thi Truy vấn SQL

Như bạn thấy ở trên, có thể thực thi các truy vấn SQL hợp lệ qua RSQLite bằng hàm dbGetQuery(), hàm này có các đối số sau:

• conn: Kết nối tới cơ sở dữ liệu SQLite
• query: Truy vấn SQL bạn muốn thực thi dưới dạng chuỗi

Để minh họa thêm khả năng thực thi truy vấn SQL với RSQLite, hãy cùng xem thêm một số ví dụ truy vấn trên bảng cars_data:

Lưu ý: Cần nhớ rằng qua RSQLite, bạn có thể thực thi bất kỳ truy vấn nào hợp lệ đối với cơ sở dữ liệu SQLite, từ các câu lệnh SELECT đơn giản đến JOINS (ngoại trừ RIGHT OUTER JOIN và FULL OUTER JOIN, vốn không được hỗ trợ trong SQLite).

# Gather the first 10 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
Được hỗ trợ bởi

# 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")
Được hỗ trợ bởi

# 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)")
Được hỗ trợ bởi

# 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")
Được hỗ trợ bởi

Để lưu kết quả truy vấn nhằm tiếp tục thao tác trong R dưới dạng data frame, bạn chỉ cần gán kết quả truy vấn cho một biến.

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)
Được hỗ trợ bởi

'data.frame'

Chèn biến vào truy vấn (Truy vấn tham số hóa)

Một trong những lợi thế lớn nhất khi thao tác với cơ sở dữ liệu SQLite từ R là khả năng dùng truy vấn tham số hóa. Tức là, bạn có thể lấy các biến có trong không gian làm việc R của mình và dùng chúng để truy vấn cơ sở dữ liệu SQLite. Hãy xem ví dụ về cách dùng một biến trong truy vấn 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
Được hỗ trợ bởi

Như bạn thấy, điểm khác biệt duy nhất giữa việc gửi một truy vấn bình thường và truy vấn tham số hóa nằm ở giá trị giữ chỗ trong truy vấn (>= ?) và đối số params của dbGetQuery(), vốn nhận một danh sách hoặc vector chứa các giá trị bạn muốn gán cho các vị trí giữ chỗ (trong trường hợp này là vector chứa biến mpg và cyl).

Vậy điều gì xảy ra khi bạn muốn thực thi một truy vấn khác? Trong ví dụ trên, như bạn thấy, tôi gần như đã “đo ni đóng giày” truy vấn. Nó chỉ cho phép nhập các giá trị mpg và cyl, và truy vấn sẽ chỉ truy xuất các xe có mpg và cyl lớn hơn hoặc bằng. Tuy nhiên, có thể có tình huống bạn muốn linh hoạt hơn. Nếu người dùng muốn xem các xe có mã lực và trọng lượng lớn hơn hoặc bằng một giá trị nhất định thì sao? Với truy vấn trên, bạn sẽ phải quay lại viết lại nó, nhưng bạn có thể viết một hàm để không cần bước đó nữa. Hãy xem một ví dụ:

# 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
Được hỗ trợ bởi

[1] "SELECT car_names, mpg, hp, wt FROM cars_data  WHERE  mpg  >= ?  AND  hp  >= ? AND  wt  >= ?"
Được hỗ trợ bởi

Hàm trên có thể đơn giản, nhưng nó minh họa cách có thể viết mã R để tạo truy vấn SQL có thể thực thi trên cơ sở dữ liệu SQLite. Nếu bạn quan tâm đến trường hợp sử dụng này, tôi khuyến khích bạn tiếp tục khám phá. Hàm trên có thể được cải thiện theo nhiều cách, bắt đầu từ việc loại bỏ giả định rằng người dùng cung cấp tham số sẽ muốn xem các giá trị lớn hơn hoặc bằng những gì họ nhập.

Các câu lệnh không trả về kết quả dạng bảng

Đôi khi, bạn có thể muốn thực thi các truy vấn SQL không nhất thiết trả về dữ liệu dạng bảng. Ví dụ cho các thao tác này là chèn, cập nhật hoặc xóa bản ghi. Để làm điều đó, ta có thể dùng hàm dbExecute(), hàm này nhận đối số là kết nối cơ sở dữ liệu SQLite và một truy vấn SQL. Một vài ví dụ như bên dưới:

# 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")
Được hỗ trợ bởi

1

Bây giờ hãy thử chèn lại dữ liệu của 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")
Được hỗ trợ bởi

1

Như bạn thấy, dòng mã cuối đã thêm Mazda RX4 vào cuối bảng. Khi hoàn tất thao tác với cơ sở dữ liệu SQLite trong R, điều quan trọng là gọi hàm dbDisconnect(). Điều này đảm bảo giải phóng mọi tài nguyên mà kết nối cơ sở dữ liệu đang dùng, vốn luôn là một thói quen tốt.

# Close the database connection to CarsDB
dbDisconnect(conn)
Được hỗ trợ bởi

Kết luận

Trong hướng dẫn này, chúng ta đã bao quát các hàm thiết yếu để thao tác cơ sở dữ liệu SQLite trong R bằng RSQLite. Nếu được sử dụng đúng cách, các cơ sở dữ liệu SQLite là công cụ cực kỳ hữu ích khi kết hợp trong các script R của bạn. Một ví dụ cho sức mạnh của giao điểm giữa R và SQLite là truy vấn tham số hóa, có thể dùng khi bạn cần truy vấn cơ sở dữ liệu để hiển thị thông tin dựa trên đầu vào người dùng trong ứng dụng R Shiny. Một ví dụ khác cho truy vấn tham số hóa có thể là trợ lý ảo hoặc chatbot. Nếu bạn muốn tìm hiểu thêm, tôi khuyến khích bạn xem khóa học Xây dựng Chatbot bằng Python của DataCamp.

Ghi bảng SQLite bằng cách nối thêm các data frame cũng có thể rất mạnh mẽ. Như tôi đã nêu trong hướng dẫn Hướng dẫn cho người mới bắt đầu với SQLite, khả năng này giúp tôi hoàn thành một dự án phân tích mạng xã hội bằng cách nối thêm người theo dõi của một số người dùng Twitter quan tâm vào một bảng khi tôi thu thập chúng. Tôi đã dùng một cơ sở dữ liệu SQLite cho việc này để tiết kiệm thời gian bằng cách tránh phải bắt đầu lại từ đầu trong trường hợp mất điện hoặc cập nhật Windows khiến máy tính bị tắt đột ngột. Nếu điều đó xảy ra, tất cả những gì tôi cần làm là khởi động lại việc thu thập người theo dõi của người dùng kế tiếp ngay sau người dùng cuối cùng đã có trong cơ sở dữ liệu. Để hình dung, thời gian tính toán để thu thập người theo dõi của tất cả người dùng Twitter tôi quan tâm mất gần 4 tuần do giới hạn tốc độ của Twitter API. Vì vậy, phải bắt đầu lại từ số 0 do mất điện hoặc cập nhật Windows sẽ là điều rất không mong muốn.

Như thường lệ, tôi khuyến khích bạn tiếp tục học về cơ sở dữ liệu SQLite và cách tương tác với chúng thông qua R. Tôi đặc biệt khuyến khích bạn đào sâu và xem các hướng dẫn trước đó về cách sử dụng với Python và dòng lệnh, những nội dung có thể giúp bạn trở thành “phù thủy” SQLite thực thụ. Học nữa nhé; bầu trời là giới hạn!