ในบทความนี้ เราจะมาดู 4 ขั้นตอนในการเชื่อมต่อและทำงานกับ database ในภาษา R ด้วย DBI package กัน:
- Get started
- Explore the database
- Query the database
- Close the connection
ถ้าพร้อมแล้ว ไปเริ่มกันเลย
- 💻 Step 1. Get Started
- 👀 Step 2. Explore the Database
- 🔍 Step 3. Query the Database
- 🤚 Step 4. Close the Connection
- 💪 Summary
- 😺 GitHub
- 📃 References
- ✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
💻 Step 1. Get Started
📦 DBI Package
DBI (Database Interface) เป็น package สำหรับเชื่อมต่อกับ database ซึ่งทำให้เราทำงานกับ database ในภาษา R ได้โดยตรง
ในบทความนี้ เรามาลองดูการใช้งาน DBI ผ่านการทำงานกับ Chinook SQLite database กัน (เราสามารถโหลด Chinook เพื่อลองทำตามได้จาก GitHub)
.
⬇️ Install & Connect
ในขั้นแรกของการใช้งาน เราจะติดตั้งและโหลด DBI พร้อมกับ package สำหรับ database ที่เราจะทำงานด้วย
อย่างในกรณีนี้ เราจะติดตั้งและโหลด RSQLite package เพราะเราจะทำงานกับ SQLite database
Note: ถ้าเราทำงานกับ database อื่น เราจะต้องติดตั้งและโหลด package อื่น เช่น:
- MySQL →
RMySQL - PostgresSQL →
RPostgresSQL - Oracle →
ROracle
ติดตั้ง packages:
# Install
install.packages("DBI")
install.packages("RSQLite")
โหลด packages:
# Load library(DBI) library(RSQLite)
หลังติดตั้งและโหลด packages แล้ว เราจะเชื่อมต่อกับ database ด้วย dbConnect() แบบนี้:
# Connect to database
con <- dbConnect(RSQLite::SQLite(),
"chinook.sqlite")
Note: ในกรณีที่ database ไม่ได้อยู่ใน working directory เราจะต้องใช้ absolute file path แทนชื่อไฟล์ เช่น:
# Connect to database
con <- dbConnect(RSQLite::SQLite(),
"C:/Users/YourUser/Documents/R_Projects/my_data/chinook.sqlite")
เท่านี้ เราก็พร้อมที่จะทำงานกับ database กันแล้ว
👀 Step 2. Explore the Database
เริ่มแรก เราจะสำรวจ database เพื่อทำความเข้าใจโครงสร้างข้อมูลกันก่อน
เรามี 2 functions ที่ช่วยเราได้ ได้แก่:
dbListTables(): ดูรายชื่อ tables ทั้งหมดใน databasedbGetQuety(): ดู columns ใน table ที่ต้องการ
.
1️⃣ dbListTable()
ตัวอย่าง:
# List tables in the database dbListTables(con)
ผลลัพธ์:
[1] "Album" "Artist" "Customer" "Employee" [5] "Genre" "Invoice" "InvoiceLine" "MediaType" [9] "Playlist" "PlaylistTrack" "Track"
.
2️⃣ dbGetQuery()
ตัวอย่าง:
# List columns in a table
dbGetQuery(con,
"PRAGMA table_info(Artist)")
ผลลัพธ์:
cid name type notnull dflt_value pk 1 0 ArtistId INTEGER 1 NA 1 2 1 Name NVARCHAR(120) 0 NA 0
ในกรณีที่เราต้องการดู columns ในทุก table เราสามารถใช้ for loop ช่วยได้แบบนี้:
# Get the table list
tables <- dbListTables(con)
# Get all columns
for (table_name in tables) {
# Print the table name
message(paste0("\\n👉 Table: ", table_name))
# Get the columns
column_info <- dbGetQuery(con,
paste0("PRAGMA table_info(",
table_name,
")"))
# Print the columns
print(column_info)
}
ผลลัพธ์:
👉 Table: Album cid name type notnull dflt_value pk 1 0 AlbumId INTEGER 1 NA 1 2 1 Title NVARCHAR(160) 1 NA 0 3 2 ArtistId INTEGER 1 NA 0 👉 Table: Artist cid name type notnull dflt_value pk 1 0 ArtistId INTEGER 1 NA 1 2 1 Name NVARCHAR(120) 0 NA 0 👉 Table: Customer cid name type notnull dflt_value pk 1 0 CustomerId INTEGER 1 NA 1 2 1 FirstName NVARCHAR(40) 1 NA 0 3 2 LastName NVARCHAR(20) 1 NA 0 4 3 Company NVARCHAR(80) 0 NA 0 5 4 Address NVARCHAR(70) 0 NA 0 6 5 City NVARCHAR(40) 0 NA 0 7 6 State NVARCHAR(40) 0 NA 0 8 7 Country NVARCHAR(40) 0 NA 0 9 8 PostalCode NVARCHAR(10) 0 NA 0 10 9 Phone NVARCHAR(24) 0 NA 0 11 10 Fax NVARCHAR(24) 0 NA 0 12 11 Email NVARCHAR(60) 1 NA 0 13 12 SupportRepId INTEGER 0 NA 0 👉 Table: Employee cid name type notnull dflt_value pk 1 0 EmployeeId INTEGER 1 NA 1 2 1 LastName NVARCHAR(20) 1 NA 0 3 2 FirstName NVARCHAR(20) 1 NA 0 4 3 Title NVARCHAR(30) 0 NA 0 5 4 ReportsTo INTEGER 0 NA 0 6 5 BirthDate DATETIME 0 NA 0 7 6 HireDate DATETIME 0 NA 0 8 7 Address NVARCHAR(70) 0 NA 0 9 8 City NVARCHAR(40) 0 NA 0 10 9 State NVARCHAR(40) 0 NA 0 11 10 Country NVARCHAR(40) 0 NA 0 12 11 PostalCode NVARCHAR(10) 0 NA 0 13 12 Phone NVARCHAR(24) 0 NA 0 14 13 Fax NVARCHAR(24) 0 NA 0 15 14 Email NVARCHAR(60) 0 NA 0 👉 Table: Genre cid name type notnull dflt_value pk 1 0 GenreId INTEGER 1 NA 1 2 1 Name NVARCHAR(120) 0 NA 0 👉 Table: Invoice cid name type notnull dflt_value pk 1 0 InvoiceId INTEGER 1 NA 1 2 1 CustomerId INTEGER 1 NA 0 3 2 InvoiceDate DATETIME 1 NA 0 4 3 BillingAddress NVARCHAR(70) 0 NA 0 5 4 BillingCity NVARCHAR(40) 0 NA 0 6 5 BillingState NVARCHAR(40) 0 NA 0 7 6 BillingCountry NVARCHAR(40) 0 NA 0 8 7 BillingPostalCode NVARCHAR(10) 0 NA 0 9 8 Total NUMERIC(10,2) 1 NA 0 👉 Table: InvoiceLine cid name type notnull dflt_value pk 1 0 InvoiceLineId INTEGER 1 NA 1 2 1 InvoiceId INTEGER 1 NA 0 3 2 TrackId INTEGER 1 NA 0 4 3 UnitPrice NUMERIC(10,2) 1 NA 0 5 4 Quantity INTEGER 1 NA 0 👉 Table: MediaType cid name type notnull dflt_value pk 1 0 MediaTypeId INTEGER 1 NA 1 2 1 Name NVARCHAR(120) 0 NA 0 👉 Table: Playlist cid name type notnull dflt_value pk 1 0 PlaylistId INTEGER 1 NA 1 2 1 Name NVARCHAR(120) 0 NA 0 👉 Table: PlaylistTrack cid name type notnull dflt_value pk 1 0 PlaylistId INTEGER 1 NA 1 2 1 TrackId INTEGER 1 NA 2 👉 Table: Track cid name type notnull dflt_value pk 1 0 TrackId INTEGER 1 NA 1 2 1 Name NVARCHAR(200) 1 NA 0 3 2 AlbumId INTEGER 0 NA 0 4 3 MediaTypeId INTEGER 1 NA 0 5 4 GenreId INTEGER 0 NA 0 6 5 Composer NVARCHAR(220) 0 NA 0 7 6 Milliseconds INTEGER 1 NA 0 8 7 Bytes INTEGER 0 NA 0 9 8 UnitPrice NUMERIC(10,2) 1 NA 0
🔍 Step 3. Query the Database
หลังสำรวจ database แล้ว เราสามารถ query ข้อมูลได้ด้วย 3 functions ได้แก่:
dbReadTable()dbGetQuery()dbSendQuery()
.
1️⃣ dbReadTable()
เราจะใช้ dbReadTable() เมื่อต้องการดึงข้อมูลทั้งหมดมาจาก table ที่ต้องการ
ตัวอย่างเช่น ดูข้อมูลทั้งหมดใน Genre:
# Query with dbReadTable()
dbReadTable(con,
"Genre")
ผลลัพธ์:
GenreId Name 1 1 Rock 2 2 Jazz 3 3 Metal 4 4 Alternative & Punk 5 5 Rock And Roll 6 6 Blues 7 7 Latin 8 8 Reggae 9 9 Pop 10 10 Soundtrack 11 11 Bossa Nova 12 12 Easy Listening 13 13 Heavy Metal 14 14 R&B/Soul 15 15 Electronica/Dance 16 16 World 17 17 Hip Hop/Rap 18 18 Science Fiction 19 19 TV Shows 20 20 Sci Fi & Fantasy 21 21 Drama 22 22 Comedy 23 23 Alternative 24 24 Classical 25 25 Opera
.
2️⃣ dbGetQuery()
ในกรณีที่เราต้องการดูข้อมูลแบบเจาะจง เราจะใช้ dbGetQuery() ซึ่งต้องการ 2 arguments:
- Connection ที่เชื่อมต่อกับ database
- SQL query ที่กำหนดข้อมูลที่ต้องการจาก database
ตัวอย่างการใช้งาน #1 – ดึงข้อมูลลูกค้าที่มาจากบราซิล:
# Query with dbGetQuery() - example 1
dbGetQuery(con,
"SELECT
CustomerId,
FirstName,
LastName, Email
FROM
Customer
WHERE
country = 'Brazil';")
ผลลัพธ์:
CustomerId FirstName LastName Email 1 1 Luís Gonçalves luisg@embraer.com.br 2 10 Eduardo Martins eduardo@woodstock.com.br 3 11 Alexandre Rocha alero@uol.com.br 4 12 Roberto Almeida roberto.almeida@riotur.gov.br 5 13 Fernanda Ramos fernadaramos4@uol.com.br
ตัวอย่าง #2 – คำนวณยอดขายรวมของแต่ละประเทศ โดยเรียงจากมากไปน้อย:
# Query with dbGetQuery() - example 2
dbGetQuery(con,
"SELECT
BillingCountry,
SUM(Total) AS TotalSales
FROM
Invoice
GROUP BY
BillingCountry
ORDER BY
TotalSales DESC;")
ผลลัพธ์:
BillingCountry TotalSales 1 USA 523.06 2 Canada 303.96 3 France 195.10 4 Brazil 190.10 5 Germany 156.48 6 United Kingdom 112.86 7 Czech Republic 90.24 8 Portugal 77.24 9 India 75.26 10 Chile 46.62 11 Ireland 45.62 12 Hungary 45.62 13 Austria 42.62 14 Finland 41.62 15 Netherlands 40.62 16 Norway 39.62 17 Sweden 38.62 18 Spain 37.62 19 Poland 37.62 20 Italy 37.62 21 Denmark 37.62 22 Belgium 37.62 23 Australia 37.62 24 Argentina 37.62
ตัวอย่าง #3 – ดึงชื่อเพลงและชื่ออัลบัม 10 อันดับแรก:
# Query with dbGetQuery() - example 3
dbGetQuery(con,
"SELECT
T.Name AS TrackName,
A.Title AS AlbumTitle
FROM
Track AS T
JOIN
Album AS A ON T.AlbumID = A.AlbumID
LIMIT 10;")
ผลลัพธ์:
TrackName AlbumTitle 1 For Those About To Rock (We Salute You) For Those About To Rock We Salute You 2 Balls to the Wall Balls to the Wall 3 Fast As a Shark Restless and Wild 4 Restless and Wild Restless and Wild 5 Princess of the Dawn Restless and Wild 6 Put The Finger On You For Those About To Rock We Salute You 7 Let's Get It Up For Those About To Rock We Salute You 8 Inject The Venom For Those About To Rock We Salute You 9 Snowballed For Those About To Rock We Salute You 10 Evil Walks For Those About To Rock We Salute You
.
3️⃣ dbSendQuery()
dbSendQuery() ทำงานเหมือนกับ dbGetQuery() แต่ต่างกันที่ dbGetQuery() จะยังส่งข้อมูลใด ๆ กลับมาจนกว่าเราจะเรียกดูด้วย dbFetch()
ยกตัวอย่างเช่น ดูข้อมูลลูกค้า 10 รายชื่อแรกเมื่อเรียงตามนามสกุล:
# Send query
res <- dbSendQuery(con,
"SELECT
CustomerId,
LastName,
FirstName,
Email
FROM
Customer
ORDER BY
LastName
LIMIT 10;")
# Fetch results
dbFetch(res)
ผลลัพธ์:
CustomerId LastName FirstName Email 1 12 Almeida Roberto roberto.almeida@riotur.gov.br 2 28 Barnett Julia jubarnett@gmail.com 3 39 Bernard Camille camille.bernard@yahoo.fr 4 18 Brooks Michelle michelleb@aol.com 5 29 Brown Robert robbrown@shaw.ca 6 21 Chase Kathy kachase@hotmail.com 7 26 Cunningham Richard ricunningham@hotmail.com 8 41 Dubois Marc marc.dubois@hotmail.com 9 34 Fernandes João jfernandes@yahoo.pt 10 30 Francis Edward edfrancis@yachoo.ca
เมื่อเราเรียกดูข้อมูลทั้งหมดแล้ว เราจะไม่สามารถเรียกดูซ้ำได้:
# Fetch results dbFetch(res)
ผลลัพธ์:
> dbFetch(res) [1] CustomerId LastName FirstName Email <0 rows> (or 0-length row.names)
ทั้งนี้ เราสามารถกำหนดจำนวนข้อมูลที่จะเรียกดูในแต่ละครั้งได้ เช่น:
# Send query
res <- dbSendQuery(con,
"SELECT
CustomerId,
LastName,
FirstName,
Email
FROM
Customer
ORDER BY
LastName
LIMIT 10;")
# Fetch five, twice
dbFetch(res, n = 5)
dbFetch(res, n = 5)
dbFetch(res, n = 5)
ผลลัพธ์:
> dbFetch(res, n = 5) CustomerId LastName FirstName Email 1 12 Almeida Roberto roberto.almeida@riotur.gov.br 2 28 Barnett Julia jubarnett@gmail.com 3 39 Bernard Camille camille.bernard@yahoo.fr 4 18 Brooks Michelle michelleb@aol.com 5 29 Brown Robert robbrown@shaw.ca > dbFetch(res, n = 5) CustomerId LastName FirstName Email 1 21 Chase Kathy kachase@hotmail.com 2 26 Cunningham Richard ricunningham@hotmail.com 3 41 Dubois Marc marc.dubois@hotmail.com 4 34 Fernandes João jfernandes@yahoo.pt 5 30 Francis Edward edfrancis@yachoo.ca > dbFetch(res, n = 5) [1] CustomerId LastName FirstName Email <0 rows> (or 0-length row.names)
เราสามารถใช้ dbSendQuery() และ dbFetch() เพื่อดูข้อมูลเป็นชุด ๆ แทนที่จะดูข้อมูลทั้งหมดในครั้งเดียวแบบ dbGetQuery()
Note: ในกรณีที่เราต้องการลบ query ที่เราส่งไป database ด้วย dbSendQuery() ให้เราใช้ dbClearResult():
# Clear results dbClearResult(res)
🤚 Step 4. Close the Connection
สุดท้าย เมื่อเราทำงานกับ database เสร็จแล้ว เราต้องสิ้นสุดการเชื่อมต่อกับ database ด้วย dbDisconnect():
# Close the connection dbDisconnect(con)
เป็นอันจบการทำงานกับ database ด้วย DBI
💪 Summary
ในบทความนี้ เราได้ไปดูวิธีการใช้งาน DBI เพื่อทำงานกับ database กัน:
เชื่อมต่อ database:
dbConnect()
สำรวจ database:
dbListTables()dbGetQuery()
Query ข้อมูล:
dbReadTable()dbGetQuery()dbSendQuery(),dbFetch(), และdbClearResult()
ปิดการเชื่อมต่อ:
dbDisconnect()
😺 GitHub
ดู code และ database ในบทความนี้ได้ที่ GitHub
📃 References
✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
📕 ขอฝากหนังสือเล่มแรกในชีวิตด้วยนะครับ 😆
🙋 ใครที่กำลังเรียนจิตวิทยาหรือทำงานสายจิตวิทยา และเบื่อที่ต้องใช้ software ราคาแพงอย่าง SPSS และ Excel เพื่อทำข้อมูล
💪 ผมขอแนะนำ R Book for Psychologists หนังสือสอนใช้ภาษา R เพื่อการวิเคราะห์ข้อมูลทางจิตวิทยา ที่เขียนมาเพื่อนักจิตวิทยาที่ไม่เคยมีประสบการณ์เขียน code มาก่อน
ในหนังสือ เราจะปูพื้นฐานภาษา R และพาไปดูวิธีวิเคราะห์สถิติที่ใช้บ่อยกัน เช่น:
- Correlation
- t-tests
- ANOVA
- Reliability
- Factor analysis
🚀 เมื่ออ่านและทำตามตัวอย่างใน R Book for Psychologists ทุกคนจะไม่ต้องพึง SPSS และ Excel ในการทำงานอีกต่อไป และสามารถวิเคราะห์ข้อมูลด้วยตัวเองได้ด้วยความมั่นใจ
แล้วทุกคนจะแปลกใจว่า ทำไมภาษา R ง่ายขนาดนี้ 🙂↕️
👉 สนใจดูรายละเอียดหนังสือได้ที่ meb:
Leave a Reply