R: اتصالات قاعدة البيانات R

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

library(DBI)
library(RMySQL)

# 1. Connect to the Database
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 user = "root",
                 password = "secret")

# 2. Run straight ahead SQL
result <- dbGetQuery(con, "
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 100
")

# 3. or use dplyr Translation SQL
library(dplyr)
result2 <- tbl(con, "orders") |>
  filter(order_date >= "2024-01-01") |>
  group_by(customer_id) |>
  summarise(total = sum(amount)) |>
  arrange(desc(total)) |>
  collect()  # Forget it R Memory

# 4. Disconnect
dbDisconnect(con)

# 1. Core Interfaces(Required)
install.packages("DBI")

# 2. Local Database(No server required,Recommended for beginners)
install.packages("RSQLite")

# 3. Production Database(On Demand)
install.packages("RMySQL")        # MySQL
install.packages("RMariaDB")      # MariaDB(Recommended Alternatives RMySQL)
install.packages("RPostgreSQL")   # PostgreSQL
install.packages("odbc")          # General ODBC

library(DBI)
library(RSQLite)

# 1. Create/Connect(If the file does not exist, it will be created automatically.)
con <- dbConnect(SQLite(), "my_database.db")

# 2. Disconnect(Important!Disconnect after use)
dbDisconnect(con)

# 3. Temporary Database(In memory,Restart Failed)
con <- dbConnect(SQLite(), ":memory:")

# Prepare the data
sales <- data.frame(
  id = 1:5,
  product = c("A", "B", "A", "C", "B"),
  amount = c(100, 200, 150, 300, 250)
)

con <- dbConnect(SQLite(), "shop.db")

# Write to Table(Coverage:overwrite / Add:append)
dbWriteTable(con, "sales", sales, overwrite = TRUE)

# Verification
dbListTables(con)
# [1] "sales"

# Method 1:Read the entire table
df <- dbReadTable(con, "sales")
print(df)

# Method 2:Execute SQL
result <- dbGetQuery(con, "SELECT * FROM sales WHERE amount > 150")
print(result)

# Method 3:dplyr(Lazy Query,Finally collect)
library(dplyr)
result2 <- tbl(con, "sales") |>
  filter(amount > 150) |>
  collect()

# 1. List all tables
dbListTables(con)
# [1] "sales" "products" "customers"

# 2. Does the table exist?
dbExistsTable(con, "sales")
# [1] TRUE

# 3. Delete Table
dbRemoveTable(con, "sales")

# 4. View Table Fields
dbListFields(con, "sales")
# [1] "id" "product" "amount"

# 5. Commit Transaction
dbCommit(con)
dbRollback(con)

# MySQL
library(RMySQL)
con <- dbConnect(MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

# MariaDB(Recommendations,Open Source)
library(RMariaDB)
con <- dbConnect(MariaDB(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

library(RPostgreSQL)
con <- dbConnect(PostgreSQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 5432,
                 user = "postgres",
                 password = "your_password")

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "localhost",
                 Database = "shop",
                 UID = "sa",
                 PWD = "your_password")

library(dplyr)
library(dbplyr)  # Load Translator

# 1. Create a "lazy" table(Doesn't actually query the database)
orders <- tbl(con, "orders")

# 2. Write dplyr Code(Will not be executed immediately)
query <- orders |>
  filter(order_date >= "2024-01-01", amount > 100) |>
  group_by(customer_id) |>
  summarise(
    total = sum(amount),
    n_orders = n()
  ) |>
  arrange(desc(total)) |>
  head(100)

# 3. View the translation SQL
query |> show_query()
# SELECT `customer_id`, SUM(`amount`) AS `total`, COUNT(*) AS `n_orders`
# FROM `orders`
# WHERE (`order_date` >= '2024-01-01') AND (`amount` > 100.0)
# GROUP BY `customer_id`
# ORDER BY `total` DESC
# LIMIT 100

# 4. Forget it R Memory
result <- query |> collect()

# 1. Create/Overview Table
dbWriteTable(con, "sales_summary", summary_df, overwrite = TRUE)

# 2. Append to the existing table
dbWriteTable(con, "sales_log", new_data, append = TRUE)

# 3. Temporary Table(Automatically delete at the end of the session)
dbWriteTable(con, "temp_data", df, temporary = TRUE)

# 4. Line Name Processing
dbWriteTable(con, "df", df, row.names = FALSE)  # Do not include the line number

# Read R Data → Cleaning → Write back to the database
library(dplyr)
library(readr)

# 1. Read CSV
sales <- read_csv("sales.csv")

# 2. Cleaning
clean_sales <- sales |>
  filter(!is.na(amount)) |>
  mutate(date = as.Date(date)) |>
  group_by(region, product) |>
  summarise(total = sum(amount))

# 3. Write back to the database
con <- dbConnect(SQLite(), "shop.db")
dbWriteTable(con, "sales_by_region_product", clean_sales, overwrite = TRUE)

# 4. Verification
result <- dbGetQuery(con, "SELECT * FROM sales_by_region_product LIMIT 5")
print(result)

# 1. Use .Renviron Save Password(Don't put it in the code)
# Add to ~/.Renviron:
# DB_PASSWORD=your_password
password <- Sys.getenv("DB_PASSWORD")

# 2. Connection Configuration Encapsulation
db_connect <- function() {
  dbConnect(RMariaDB::MariaDB(),
            dbname = "shop",
            host = Sys.getenv("DB_HOST", "localhost"),
            port = as.integer(Sys.getenv("DB_PORT", 3306)),
            user = Sys.getenv("DB_USER", "root"),
            password = Sys.getenv("DB_PASSWORD"))
}

# 3. Use withConnection Pattern
con <- db_connect()
on.exit(dbDisconnect(con))  # Automatically disconnect when the function ends

# 1. Simple tryCatch
result <- tryCatch(
  {
    con <- dbConnect(SQLite(), "shop.db")
    dbGetQuery(con, "SELECT * FROM sales LIMIT 10")
  },
  error = function(e) {
    message("Database Error:", e$message)
    NULL
  },
  finally = {
    if (exists("con") && !is.null(con)) dbDisconnect(con)
  }
)

# 2. Check the connection
if (dbIsValid(con)) {
  cat("Connection is normal\n")
} else {
  cat("Connection Lost\n")
}

# 1. Bulk Insert(Transactions)
dbBegin(con)
for (chunk in split(data, ceiling(seq_len(nrow(data)) / 1000))) {
  dbWriteTable(con, "big_table", chunk, append = TRUE)
}
dbCommit(con)

# 2. Progress Bar
library(progress)
pb <- progress_bar$new(total = nrow(data))
for (i in seq_len(nrow(data))) {
  dbExecute(con, "INSERT INTO log VALUES (?, ?)", params = list(data$id[i], data$msg[i]))
  pb$tick()
}

# ============================================
# Comprehensive Analysis of the Local Sales Database
# Features:Use RSQLite Database Creation,Look up data,Analysis
# ============================================

library(DBI)
library(RSQLite)
library(dplyr)

# 1. Create a Database + Write Initial Data
con <- dbConnect(SQLite(), "shop_demo.db")
dbWriteTable(con, "customers", data.frame(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  city = c("Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"),
  register_date = as.Date("2023-01-01") + c(0, 30, 60, 90, 120)
), overwrite = TRUE)

dbWriteTable(con, "orders", data.frame(
  id = 1:20,
  customer_id = sample(1:5, 20, replace = TRUE),
  order_date = as.Date("2024-01-01") + sample(0:90, 20),
  amount = sample(100:2000, 20)
), overwrite = TRUE)

cat("=== Table Structure ===\n")
print(dbListTables(con))

# 2. SQL Search:Total Order Amount for Each Customer
cat("\n=== SQL Search(Total Customer Orders)===\n")
result_sql <- dbGetQuery(con, "
  SELECT c.name, c.city, COUNT(o.id) AS n_orders, SUM(o.amount) AS total
  FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id
  ORDER BY total DESC
")
print(result_sql)

# 3. The same query using dplyr(dbplyr Translation SQL)
cat("\n=== dplyr Search(Automatic Translation SQL)===\n")
customers_tbl <- tbl(con, "customers")
orders_tbl <- tbl(con, "orders")

result_dplyr <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(
    n_orders = n(),
    total = sum(amount)
  ) |>
  arrange(desc(total)) |>
  collect()

print(result_dplyr)

# 4. Complex Analysis:Monthly Sales Trends
cat("\n=== Monthly Sales Trends ===\n")
monthly_sales <- orders_tbl |>
  mutate(month = format(order_date, "%Y-%m")) |>
  group_by(month) |>
  summarise(
    orders = n(),
    revenue = sum(amount),
    avg_amount = round(mean(amount), 2)
  ) |>
  collect()
print(monthly_sales)

# 5. Identify High-Value Customers
cat("\n=== High-value customers(Order Value > 2000)===\n")
vip_customers <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  filter(total > 2000) |>
  arrange(desc(total)) |>
  collect()
print(vip_customers)

# 6. Write the analysis results back to the database
dbWriteTable(con, "vip_customers", vip_customers, overwrite = TRUE)
dbWriteTable(con, "monthly_sales", monthly_sales, overwrite = TRUE)
cat("\n=== The report has been saved to the database. ===\n")
print(dbListTables(con))

# 7. Verify the table to which data is written back
cat("\n=== Verification vip_customers ===\n")
vip_reloaded <- dbReadTable(con, "vip_customers")
print(vip_reloaded)

# 8. Disconnect
dbDisconnect(con)
cat("\n=== The database connection has been lost ===\n")

# 9. Clean Up Files
file.remove("shop_demo.db")

=== SQL Search(Total Customer Orders)===
     name city n_orders total
1    Eve   Shenzhen        5  5180
2  Diana   Beijing        5  4643
3 Charlie Guangzhou        4  4156
4    Bob   Shanghai        3  3461
5  Alice   Beijing        3  3033

=== Monthly Sales Trends ===
    month orders revenue avg_amount
1  2024-01      4     5239    1309.75
2  2024-02      5     6491    1298.20
...