如何在R语言中将CSV文件指定列关联至已有SQL表？

需求说明 #

已通过R将多个CSV文件导入PostgreSQL的SQL_table表，现需将外部CSV文件CSV_1中的Latitude、Longitude、Altitude、Start_Date、End_Date字段，通过SQL_table.Location_ID与CSV_1.City的关联关系，追加到SQL_table中，最终得到包含这些地理与日期字段的完整表。

修改后的完整R代码 #

library(RPostgres)
library(DBI)
library(dplyr) # 用于数据关联操作

# Step 1: 读取并筛选CSV_1的目标列
csv_1 <- read.csv("CSV_1.csv") %>%
  select(City, Latitude, Longitude, Altitude, Start_Date, End_Date)

# Step 2: 读取原CSV文件集（若无需重新导入原数据可跳过此步骤）
file_names1 <- dir("D:/Data/", full.names = TRUE, recursive = TRUE)
my_data_frame <- do.call(rbind, lapply(file_names1, read.csv))

# Step 3: 关联两个数据集
merged_data <- my_data_frame %>%
  left_join(csv_1, by = c("Location_ID" = "City"))

# Step 4: 建立PostgreSQL连接
dsn_database = "你的数据库名"
dsn_hostname = "localhost"
dsn_port = "你的端口号"
dsn_uid = "你的用户名"
dsn_pwd = "你的密码"

tryCatch({
  drv <- dbDriver("Postgres")
  print("Connecting to Database…")
  connec <- dbConnect(drv, 
                      dbname = dsn_database,
                      host = dsn_hostname, 
                      port = dsn_port,
                      user = dsn_uid, 
                      password = dsn_pwd)
  print("Database Connected!")
},
error=function(cond) {
  print("Unable to connect to Database.")
  stop(cond)
})

# Step 5: 更新数据库表（两种方案二选一）
## 方案1：测试环境用 - 直接覆盖原表（适合重新导入完整数据）
dbWriteTable(connec, "SQL_table", merged_data, overwrite = TRUE, row.names = FALSE)

## 方案2：生产环境用 - 先加字段再关联更新（不丢失原表已有数据）
# dbExecute(connec, "ALTER TABLE SQL_table ADD COLUMN Latitude numeric, ADD COLUMN Longitude numeric, ADD COLUMN Altitude numeric, ADD COLUMN Start_Date date, ADD COLUMN End_Date date;")
# dbWriteTable(connec, "temp_csv1", csv_1, temporary = TRUE, row.names = FALSE)
# dbExecute(connec, "UPDATE SQL_table t SET Latitude = c.Latitude, Longitude = c.Longitude, Altitude = c.Altitude, Start_Date = c.Start_Date, End_Date = c.End_Date FROM temp_csv1 c WHERE t.Location_ID = c.City;")

# 验证更新结果
updated_table <- dbReadTable(connec, "SQL_table")
print(head(updated_table))

# 关闭数据库连接
dbDisconnect(connec)

关键说明 #

• CSV数据筛选：用dplyr::select精准提取需要的字段，避免冗余数据干扰后续操作。
• 数据关联逻辑：left_join确保原数据集的所有行都被保留，匹配不到关联键的字段会填充NA。
• 数据库更新选择：
  • 方案1操作简单，适合测试场景，直接用关联后的完整数据覆盖原表。
  • 方案2更安全，适合已有生产数据的场景，通过添加字段+关联更新的方式，避免丢失原表数据。
• 连接稳定性：添加stop(cond)确保连接失败时终止程序，最后主动关闭连接避免资源占用。

内容的提问来源于stack exchange，提问作者Alexia k Boston