循环在R中迭代SQL查询

Question

我想在我的df中的17行上迭代这个SQL查询。我的df和代码如下。我想我可能需要在dat$ptt_id周围使用单引号，因为我在"；IN"；作用有什么想法如何正确地写这篇文章吗？

df看起来像：

ptt_id
1  181787
2  181788
3  184073
4  184098
5  197601
6  197602
7  197603
8  197604
9  197605
10 197606
11 197607
12 197608
13 197609
14 200853
15 200854
16 200851
17 200852

#加载数据----

dat <- read.csv("ptts.csv")
dat2<-list(dat)

#发送到数据库----

for(i in 1:nrow(dat)){
q <- paste("SELECT orgnl_pit, t_name, cap_date, species, sex, mass, cap_lat, cap_lon, sat_appld_id
FROM main.capev JOIN uid.turtles USING (orgnl_pit)
WHERE sat_appld_id IN", dat$ptt_id[i],";")

#Get query----
tags <- dbGetQuery(conn, q)
}
Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "181787"
LINE 3:         WHERE sat_appld_id IN 181787 ;
^

感谢您的帮助。

Answer 1

两个选项：

1. 参数绑定。

   qmarks <- paste0("(", paste(rep("?", nrow(df)), collapse = ","), ")")
   qmarks
   # [1] "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
   qry <- paste(
   "SELECT orgnl_pit, t_name, cap_date, species, sex, mass, cap_lat, cap_lon, sat_appld_id
   FROM main.capev JOIN uid.turtles USING (orgnl_pit)
   WHERE sat_appld_id IN", qmarks)
   tags <- dbGetQuery(conn, qry, params = df[,1])
   

2. 临时表格。当您有大量的id要使用时，这可能更有用。(如果你从数据库中获取id，并且可以在这个子查询中使用该查询，那么在没有临时表的情况下，这也可以工作。(

   dbWriteTable(conn, df, "mytemp")
   qry <- "SELECT orgnl_pit, t_name, cap_date, species, sex, mass, cap_lat, cap_lon, sat_appld_id
   FROM main.capev JOIN uid.turtles USING (orgnl_pit)
   WHERE sat_appld_id IN (select id from mytemp)"
   tags <- dbGetQuery(conn, qry)
   dbExecute(conn, "drop table mytemp")
   

   (仔细命名临时表。DBMS通常有一个命名法，以确保在断开连接时自动清理/删除表，通常类似于"#mytemp"。请与DBMS或DBA核实。(

Answer 2

IN运算符需要一个列表。您可以将其视为多个OR条件。

例如，应该是WHERE sat_appld_id IN (181787)而不是WHERE sat_appld_id IN 181787

在这一点上，您可以从dat$ptt_id列中为一个sql查询(如WHERE sat_appld_id IN (181787, 181788, 184073, ...)(创建一个列表，而不是循环，并在R代码中进行任何额外的处理，而不是进行多个数据库查询。