R में SQLite

मेरे पिछले ट्यूटोरियल Beginners Guide to SQLite के अंत में बताया गया था कि R और Python के साथ उपयोग करने पर SQLite डेटाबेस सबसे अधिक शक्तिशाली होते हैं। अब तक DataCamp में, आपने Python से SQLite डेटाबेस के साथ काम करना देखा है (Python में sqlite3 पैकेज के माध्यम से SQLite डेटाबेस को कैसे मैनीप्युलेट करें, यह सीखने के लिए Sayak Paul का SQLite in Python ट्यूटोरियल देखें)। हालांकि, इस ट्यूटोरियल में हम R में RSQLite पैकेज का उपयोग करके SQLite डेटाबेस का उपयोग कैसे करें, इस पर ध्यान देंगे।

हम इस बात की बुनियाद समझेंगे कि RSQLite का उपयोग करके आवश्यक कार्य जैसे कि SQLite डेटाबेस को क्वेरी भेजना या टेबल बनाना कैसे किया जाता है। इसके अलावा, मैं बताऊंगा कि पैरामीटराइज़्ड क्वेरीज़ और INSERT या DELETE जैसे ऑपरेशन, जो टैबुलर परिणाम नहीं लौटाते, कैसे उपयोग करें।

डेटाबेस और टेबल बनाना

पहला कदम, जैसा कि आप समझ ही गए होंगे, डेटाबेस बनाना है। RSQLite, SQLite कमांड लाइन की तरह, अस्थायी इन-मेमाेरी (in-memory) ट्रांज़िएंट SQLite डेटाबेस बना सकता है। हालांकि, आमतौर पर आपको यही नहीं चाहिए होता, इसलिए आइए mtcars डेटासेट के लिए एक उचित डेटाबेस बनाते हैं, इसके लिए हम dbConnect() फ़ंक्शन का उपयोग करेंगे, जो निम्न तर्क लेता है:

• drv: एक डेटाबेस ड्राइवर
• path: SQLite डेटाबेस का पथ। यदि आप नया बना रहे हैं, तो नीचे दिए अनुसार बस अपनी पसंद का नाम दें। यदि आप एक ट्रांज़िएंट इन-मेमाेरी डेटाबेस के साथ काम करना चाहते हैं, तो आप path आर्गुमेंट छोड़ सकते हैं या ":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")
संचालित द्वारा

एक बार जब आपने डेटाबेस बना लिया और आपका डेटा सही रूप में है, तो आप dbWriteTable() फ़ंक्शन का उपयोग करके डेटाबेस के अंदर एक टेबल बना सकते हैं। यह फ़ंक्शन कई आर्गुमेंट ले सकता है, लेकिन अभी के लिए, निम्न पर ध्यान दें:

• conn: आपके SQLite डेटाबेस का कनेक्शन
• name: वह नाम जिसे आप अपनी टेबल के लिए उपयोग करना चाहते हैं
• value: वह डेटा जिसे आप इनसर्ट करना चाहते हैं। यह एक R डेटा फ्रेम होना चाहिए या ऐसा ऑब्जेक्ट जिसे R डेटा फ्रेम में बदला जा सके।

इसके बाद, आप dbListTables() फ़ंक्शन को SQLite डेटाबेस कनेक्शन के साथ आर्गुमेंट के रूप में उपयोग कर यह जाँच सकते हैं कि टेबल सफलतापूर्वक बन गई है।

# 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 से टेबल बनाते समय एक अत्यंत उपयोगी सुविधा यह है कि यदि आपके पास कई डेटा फ्रेम हैं, तो आप एक लूप का उपयोग करके किसी मौजूदा टेबल में और डेटा जोड़ सकते हैं; बस dbWriteTable() के अंदर वैकल्पिक आर्गुमेंट append = TRUE सेट करें। उदाहरण के तौर पर, आइए कुछ कारों और उनके निर्माता नामों के साथ दो अलग-अलग डेटा फ्रेम जोड़कर एक नया टॉय टेबल बनाते हैं:

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

अब आइए सुनिश्चित करें कि सारा डेटा नई टेबल में है:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")
संचालित द्वारा

SQL क्वेरी चलाना

जैसा कि आपने ऊपर देखा, dbGetQuery() फ़ंक्शन का उपयोग करके RSQLite के माध्यम से वैध SQL क्वेरी चलाना संभव है, जिसके निम्न तर्क हैं:

• conn: SQLite डेटाबेस का कनेक्शन
• query: वह SQL क्वेरी जिसे आप स्ट्रिंग के रूप में चलाना चाहते हैं

RSQLite के साथ SQL क्वेरी चलाने की क्षमता को और दिखाने के लिए, आइए cars_data टेबल पर कुछ और क्वेरी उदाहरण देखें:

ध्यान दें: यह ध्यान में रखना महत्वपूर्ण है कि RSQLite के माध्यम से आप किसी भी क्वेरी को चला सकते हैं जो SQLite डेटाबेस के लिए वैध है, साधारण SELECT स्टेटमेंट से लेकर JOINS तक (RIGHT OUTER JOIN और FULL OUTER JOIN को छोड़कर, जिन्हें SQLite में अनुमति नहीं है)।

# 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")
संचालित द्वारा

अपनी क्वेरी के परिणामों को आगे R में डेटा फ्रेम के रूप में उपयोग करने के लिए सहेजना उतना ही सरल है जितना कि क्वेरी के परिणाम को किसी वैरिएबल को असाइन कर देना।

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'

क्वेरी में वैरिएबल सम्मिलित करें (पैरामीटराइज़्ड क्वेरीज़)

R से SQLite डेटाबेस के साथ काम करने का सबसे बड़ा लाभ है पैरामीटराइज़्ड क्वेरीज़ का उपयोग करने की क्षमता। यानी, अपने R वर्कस्पेस में उपलब्ध वैरिएबल्स को लेकर उन्हें अपने SQLite डेटाबेस पर क्वेरी करने में उपयोग करना। आइए देखें कि किसी 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
संचालित द्वारा

जैसा कि आप देख सकते हैं, सामान्य क्वेरी भेजने और पैरामीटराइज़्ड क्वेरी भेजने के बीच एकमात्र अंतर क्वेरी में प्लेसहोल्डर मान (>= ?) और dbGetQuery() के params आर्गुमेंट में है, जो उन मानों वाली एक लिस्ट या वेक्टर लेता है जिन्हें आप प्लेसहोल्डर को असाइन करना चाहते हैं (इस मामले में हमारे पास mpg और cyl वैरिएबल वाला एक वेक्टर है)।

अब, क्या होगा जब आप कोई अलग क्वेरी चलाना चाहें? ऊपर दिए उदाहरण में, जैसा कि आप देख रहे हैं, मैंने क्वेरी को हाथ से तैयार किया है। यह केवल mpg और cyl मानों के इनपुट की अनुमति देता है, और क्वेरी सिर्फ उन कारों को लौटाएगी जिनके mpg और cyl उससे अधिक या बराबर हैं। हालांकि, कई स्थितियाँ हो सकती हैं जहाँ आपको अधिक लचीलापन चाहिए। यदि कोई उपयोगकर्ता यह देखना चाहता है कि उन कारों का horsepower और weight भी किसी मान से अधिक या बराबर हो? ऊपर वाली क्वेरी के साथ आपको वापस जाकर उसे फिर से लिखना होगा, लेकिन आप एक फ़ंक्शन लिख सकते हैं जो उस कदम को अनावश्यक बना दे। आइए एक उदाहरण देखें:

# 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  >= ?"
संचालित द्वारा

ऊपर दिया गया फ़ंक्शन बुनियादी हो सकता है, लेकिन यह दर्शाता है कि ऐसा R कोड लिखना संभव है जो SQL क्वेरी जेनरेट करे और उन्हें SQLite डेटाबेस पर चलाया जा सके। यदि आप इस उपयोग-केस में रुचि रखते हैं, तो आगे खोजबीन करने के लिए मैं प्रोत्साहित करता हूँ। ऊपर के फ़ंक्शन में कई तरह से सुधार किया जा सकता है, शुरुआत उसी धारणा को हटाने से कि पैरामीटर देने वाला उपयोगकर्ता केवल उन मानों को देखना चाहेगा जो इनपुट से अधिक या बराबर हों।

वो स्टेटमेंट जो टैबुलर परिणाम नहीं लौटाते

कभी-कभी आप ऐसी SQL क्वेरी चलाना चाहेंगे जो अनिवार्य रूप से टैबुलर डेटा न लौटाए। इन ऑपरेशनों के उदाहरण हैं टेबल रिकॉर्ड्स को इनसर्ट करना, अपडेट करना या डिलीट करना। इसके लिए हम dbExecute() फ़ंक्शन का उपयोग कर सकते हैं, जो आर्गुमेंट्स के रूप में SQLite डेटाबेस कनेक्शन और एक SQL क्वेरी लेता है। नीचे कुछ उदाहरण दिए गए हैं:

# 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

अब आइए 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")
संचालित द्वारा

1

जैसा कि आप देख सकते हैं, कोड की आखिरी पंक्ति ने Mazda RX4 को टेबल के अंत में जोड़ दिया। जब आप R के भीतर अपने SQLite डेटाबेस पर काम कर लें, तो dbDisconnect() फ़ंक्शन को कॉल करना महत्वपूर्ण है। यह सुनिश्चित करता है कि हम डेटाबेस कनेक्शन द्वारा उपयोग किए गए संसाधनों को रिलीज़ कर दें, जो हमेशा एक अच्छी प्रैक्टिस है।

# Close the database connection to CarsDB
dbDisconnect(conn)
संचालित द्वारा

निष्कर्ष

इस ट्यूटोरियल में, हमने RSQLite का उपयोग करके R में SQLite डेटाबेस को मैनीप्युलेट करने के लिए आवश्यक फ़ंक्शनों को कवर किया। यदि सही तरीके से उपयोग किया जाए, तो R स्क्रिप्ट्स के भीतर SQLite डेटाबेस अविश्वसनीय रूप से उपयोगी टूल हैं। R और SQLite के संगम की शक्ति का एक उदाहरण पैरामीटराइज़्ड क्वेरीज़ हैं, जिन्हें आप R Shiny ऐप में उपयोगकर्ता इनपुट के आधार पर जानकारी दिखाने के लिए डेटाबेस को क्वेरी करने में उपयोग कर सकते हैं। पैरामीटराइज़्ड क्वेरीज़ का एक और उपयोग-केस वर्चुअल असिस्टेंट्स या चैटबॉट्स हो सकते हैं। यदि आप इसके बारे में और सीखना चाहते हैं, तो मैं आपको DataCamp के Building Chatbots with Python कोर्स को देखने के लिए प्रोत्साहित करता हूँ।

डेटा फ्रेम्स को जोड़कर SQLite टेबल लिखना भी बहुत शक्तिशाली हो सकता है। जैसा कि मैंने Beginners Guide to SQLite ट्यूटोरियल में नोट किया था, इस क्षमता ने मुझे कई रुचिकर Twitter उपयोगकर्ताओं के फॉलोअर्स को इकट्ठा करते समय उन्हें एक टेबल में जोड़ते जाने के जरिए एक सोशल नेटवर्क विश्लेषण प्रोजेक्ट पूरा करने में मदद की। मैंने इसके लिए SQLite डेटाबेस का उपयोग किया, ताकि समय बचे और पावर कट या Windows अपडेट की स्थिति में, जो मेरे कंप्यूटर को जबरन बंद कर सकता था, शून्य से शुरू करने की आवश्यकता न पड़े। यदि ऐसा कुछ होता, तो मुझे बस उसी उपयोगकर्ता के फॉलोअर्स इकट्ठा करना फिर से शुरू करना होता जो डेटाबेस में अंतिम के ठीक बाद था। संदर्भ के लिए, मेरे सभी Twitter उपयोगकर्ताओं के फॉलोअर्स जुटाने में, Twitter API की रेट लिमिट के कारण, लगभग 4 सप्ताह का समय लगा। इसलिए पावर कट या Windows अपडेट की वजह से शून्य से शुरू करना बेहद अवांछनीय होता।

हमेशा की तरह, मैं आपको SQLite डेटाबेस के बारे में सीखते रहने और R के माध्यम से उनके साथ इंटरैक्ट करने के तरीकों की खोज करने के लिए प्रोत्साहित करता हूँ। मैं आपको दृढ़ता से सलाह देता हूँ कि आप Python और कमांड लाइन के साथ इनके उपयोग को कवर करने वाले पिछले ट्यूटोरियल्स में और गहराई से जाएँ, जो आपको एक पूर्ण SQLite विशेषज्ञ बनने में मदद कर सकते हैं। सीखते रहिए; संभावनाएँ अनंत हैं!