R’de SQLite

Önceki eğitimimin sonunda belirttiğim gibi, SQLite’a Başlangıç Kılavuzunda, SQLite veritabanları R ve Python ile birlikte kullanıldığında en güçlü halini alır. DataCamp’te şimdiye kadar Python’dan SQLite veritabanlarıyla nasıl çalışılacağına maruz kaldınız (Python’da sqlite3 paketi aracılığıyla SQLite veritabanlarını nasıl yöneteceğinizi öğrenmek için Sayak Paul’un Python’da SQLite eğitimine bakın). Ancak bu eğitimde, R’de RSQLite paketi kullanarak SQLite veritabanlarının nasıl kullanılacağına odaklanacağız.

RSQLite ile bir SQLite veritabanına sorgu göndermek veya tablolar oluşturmak gibi temel görevlerin nasıl yapılacağına dair temel konuların üzerinden geçeceğiz. Ayrıca, parametreli sorguların ve tablo sonuçları döndürmeyen INSERT veya DELETE gibi işlemlerin nasıl kullanılacağını da ele alacağım.

Veritabanları ve Tablolar Oluşturma

Tahmin edeceğiniz üzere ilk adım bir veritabanı oluşturmaktır. RSQLite, tıpkı SQLite komut satırını açtığınızda olduğu gibi, geçici bellek içi (in-memory) SQLite veritabanları oluşturabilir. Ancak bu genellikle istediğiniz şey değildir; bu nedenle mtcars veri kümesi için dbConnect() işlevini kullanarak düzgün bir veritabanı oluşturalım. Bu işlev şu argümanları alır:

• drv: Bir veritabanı sürücüsü
• path: Bir SQLite veritabanının yolu. Yeni bir tane oluşturuyorsanız, aşağıda yaptığım gibi ona seçtiğiniz bir adı verebilirsiniz. Geçici bellek içi bir veritabanıyla çalışmak istiyorsanız ise path argümanını atlayabilir veya ":memory:" yazabilirsiniz).

# 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")

Veritabanını oluşturup verinizi uygun hale getirdikten sonra, dbWriteTable() işlevini kullanarak veritabanı içinde bir tablo oluşturabilirsiniz. Bu işlev birden çok argüman alabilir, ancak şimdilik şunlara odaklanalım:

• conn: SQLite veritabanına bağlantı
• name: Tablonuz için kullanmak istediğiniz ad
• value: Eklemek istediğiniz veri. R veri çerçevesi olmalı veya bir R veri çerçevesine dönüştürülebilir bir nesne olmalıdır.

Ardından, tablonun başarıyla oluşturulduğunu kontrol etmek için argüman olarak SQLite veritabanı bağlantısını vererek dbListTables() işlevini kullanabilirsiniz.

# 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)

'cars_data'

RSQLite kullanarak tablo oluştururken son derece kullanışlı bir özellik, birden çok veri çerçeveniz varsa bir döngüyle mevcut bir tabloya daha fazla veri ekleyebilmenizdir; bunun için dbWriteTable() içinde isteğe bağlı argüman olan append = TRUE’yu ayarlamanız yeterlidir. Örneğin, iki farklı veri çerçevesini ekleyerek bazı arabalar ve üreticilerinden oluşan küçük bir oyuncak tablo oluşturalım:

# 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)

1. 'Cars_and_Makes'
2. 'cars_data'

Şimdi tüm verilerin yeni tabloda olduğundan emin olalım:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

SQL Sorgularını Çalıştırma

Yukarıda gördüğünüz gibi, dbGetQuery() işlevini kullanarak RSQLite üzerinden geçerli SQL sorguları çalıştırmak mümkündür; bu işlev şu argümanlara sahiptir:

• conn: SQLite veritabanına bağlantı
• query: Dize olarak çalıştırmak istediğiniz SQL sorgusu

RSQLite ile SQL sorguları çalıştırma yeteneğini daha iyi göstermek için, cars_data tablosu üzerinde birkaç sorgu örneğine daha bakalım:

Not: RSQLite aracılığıyla, basit SELECT ifadelerinden JOIN’lere kadar bir SQLite veritabanı için geçerli olan herhangi bir sorguyu çalıştırabilirsiniz (SQLite’ta izin verilmediği için RIGHT OUTER JOIN ve FULL OUTER JOIN hariç).

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

# 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")

# 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)")

# 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")

Sorgularınızın sonuçlarını, ileride R’de veri çerçevesi olarak daha fazla işlem yapmak üzere saklamak için, sorgunuzun çıktısını bir değişkene atamanız yeterlidir.

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)

'data.frame'

Sorgulara Değişken Ekleme (Parametreli Sorgular)

R’den SQLite veritabanlarıyla çalışmanın en büyük avantajlarından biri parametreli sorguları kullanabilmektir. Yani, R çalışma alanınızda bulunan değişkenleri alıp bunları SQLite veritabanınıza sorgu yapmak için kullanabilme becerisi. Bir değişkenin bir SQLite sorgusunda nasıl kullanılacağına dair bir örnek göstereyim:

# 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

Gördüğünüz gibi normal bir sorgu göndermekle parametreli bir sorgu göndermek arasındaki tek fark, sorgudaki yer tutucu değerlerde (>= ?) ve dbGetQuery()’nin params argümanındadır; bu argüman, yer tutuculara atamak istediğiniz değerleri içeren bir liste veya vektör alır (bu örnekte mpg ve cyl değişkenlerini içeren bir vektörümüz var).

Peki farklı bir sorgu çalıştırmak istediğinizde ne olur? Yukarıdaki örnekte görüldüğü gibi sorguyu büyük ölçüde el yapımı olarak oluşturdum. Yalnızca mpg ve cyl değerlerinin girişine izin veriyor ve sorgu yalnızca daha yüksek veya eşit mpg ve cyl değerlerine sahip arabaları getiriyor. Ancak, daha esnek olmak isteyebileceğiniz durumlar olabilir. Ya bir kullanıcı, belirli bir değerden daha yüksek veya eşit beygir gücü ve ağırlığa sahip arabaları da görmek isterse? Yukarıdaki sorgu ile geri dönüp yeniden yazmanız gerekir; fakat bu adımı gereksiz kılacak bir işlev yazabilirsiniz. Bir tanesine bakalım:

# 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

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

Yukarıdaki işlev basit olabilir ancak, bir SQLite veritabanında çalıştırılabilecek SQL sorguları üreten R kodunun nasıl yazılabileceğini gösterir. Bu kullanım alanı ilginizi çekiyorsa keşfetmeye devam etmenizi öneririm. Yukarıdaki işlev, kullanıcıdan gelen parametrelerin her zaman girilen değerlerden büyük veya onlara eşit olan değerleri görmek isteyeceği varsayımını kaldırmaktan başlayarak çeşitli şekillerde geliştirilebilir.

Tablo Benzeri Sonuç Döndürmeyen İfadeler

Bazen, mutlaka tablo verisi döndürmeyen SQL sorguları çalıştırmak isteyebilirsiniz. Bu işlemlere örnek olarak tablo kayıtlarını ekleme, güncelleme veya silme verilebilir. Bunun için, argüman olarak bir SQLite veritabanı bağlantısı ve bir SQL sorgusu alan dbExecute() işlevini kullanabiliriz. Aşağıda birkaç örnek görebilirsiniz:

# 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")

1

Şimdi Mazda RX4’ün verilerini yeniden eklemeyi deneyelim:

# 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")

1

Gördüğünüz gibi, son kod satırı Mazda RX4’ü tablonun sonuna ekledi. R içinde SQLite veritabanınızla işiniz bittiğinde, dbDisconnect() işlevini çağırmak önemlidir. Bu, veritabanı bağlantısının kullandığı tüm kaynakları serbest bıraktığımızdan emin olmamızı sağlar ki bu her zaman iyi bir pratiktir.

# Close the database connection to CarsDB
dbDisconnect(conn)

Sonuç

Bu eğitimde, RSQLite kullanarak R’de SQLite veritabanlarını yönetmek için gereken temel işlevleri ele aldık. Uygun şekilde kullanıldıklarında, SQLite veritabanları R betikleriniz içinde bir araya getirildiğinde son derece yararlı araçlardır. R ile SQLite’ın kesişiminin gücüne bir örnek, parametreli sorgulardır; örneğin bir R Shiny uygulamasında kullanıcı girdisine göre bilgi görüntülemek için bir veritabanını sorgulamanız gerektiğinde kullanılabilirler. Parametreli sorguların bir başka kullanım örneği de sanal asistanlar veya sohbet botları olabilir. Bunun hakkında daha fazla bilgi edinmek isterseniz, DataCamp’in Python ile Sohbet Botları Geliştirme kursuna göz atmanızı öneririm.

Veri çerçevelerini ekleyerek SQLite tabloları yazmak da oldukça güçlü olabilir. SQLite’a Başlangıç Kılavuzu eğitiminde belirttiğim gibi, bu yetenek, ilgilendiğim birkaç Twitter kullanıcısının takipçilerini topladıkça bir tabloya ekleyerek bir sosyal ağ analizi projesini tamamlamama olanak tanıdı. Bunun için, olası bir elektrik kesintisi veya bilgisayarımı zorla kapatabilecek bir Windows güncellemesi durumunda sıfırdan başlamaya gerek kalmaması için zaman kazandıracak şekilde bir SQLite veritabanı kullandım. Böyle bir şey olduğunda yapmam gereken tek şey, veritabanımda kayıtlı son kullanıcının bir sonrasından itibaren takipçileri toplamaya yeniden başlamaktı. Perspektif kazanmak adına, ilgi duyduğum tüm Twitter kullanıcılarının takipçilerini toplama hesaplama süresi, Twitter API’sinin hız sınırlamaları nedeniyle neredeyse 4 hafta sürdü. Dolayısıyla, bir elektrik kesintisi veya Windows güncellemesi nedeniyle sıfırdan başlamak hiç de arzu edilir olmazdı.

Her zaman olduğu gibi, SQLite veritabanları ve onlarla R aracılığıyla nasıl etkileşim kurulacağı hakkında öğrenmeye devam etmenizi öneririm. Python ve komut satırıyla kullanımlarını ele alan önceki eğitimlere daha derinlemesine dalmanızı özellikle tavsiye ederim; bu, tam bir SQLite ustası olmanıza daha fazla yardımcı olabilir. Öğrenmeye devam edin; sınır gökyüzü!