Creating SQL "Loops"

Question

I am working with the R programming language.

I have the following tables stored on a server : "my_table1", "my_table2", "my_table3", "my_table4", "my_table5"

I am trying to write a LOOP that performs SQL statements on these tables. As an example:

library(odbc)
library(DBI)

names = c("my_table1", "my_table2", "my_table3", "my_table4", "my_table5")
limits = abs(as.integer(rnorm(5,100,100)))

mycon = dbConnect(...)

results = list()

for (i in 1:length(names))

{

file_i = dbGetQuery(mycon, "select * from names[i] limit limits[i]")
results[[i]] = file_i

}

final_results <- do.call(rbind.data.frame, results)

But I don't think the dbgetquery() statement is able to recognize the SQL in this format.

Can someone please show me how to fix this?

Thanks!

Check out safe string interpolation: dbi.r-dbi.org/reference/sqlinterpolate or glue.tidyverse.org/reference/glue_sql.html — MrFlick
– MrFlick, Commented Dec 7, 2022 at 20:26

celimi · Accepted Answer · 2022-12-07 20:29:39Z

1

to create the sql statement you have to use

paste("select * from ",names[i]," limit ",limits[i])

instead of

"select * from names[i] limit limits[i]"

answered Dec 7, 2022 at 20:29

celimi

261 bronze badge

Sign up to request clarification or add additional context in comments.

8 Comments

stats_noob Over a year ago

@ celimi: thank you so much! Can you please show me how I would use this?

stats_noob Over a year ago

file_i = dbGetQuery(mycon, paste("select * from ",names[i]," limit ",limits[i]) )

stats_noob Over a year ago

is this correct?

celimi Over a year ago

Yes, this should work.

MrFlick Over a year ago

Though this method is very prone to SQL injection problems and any names that may have characters that could interfere with the SQL syntax. It's much safer to use something like sqlInterpolate to correctly build SQL statements.

|

r2evans · Accepted Answer · 2022-12-07 22:40:49Z

1

No need for an explicit for loop, we can do it with

results <-
  Map(function(nm, lim) dbGetQuery(mycon, paste("select * from", nm, "limit", lim)),
      dbQuoteIdentifier(mycon, names), limits)

Alternatively, generating the queries up front then lapplying over them:

queries <- paste("select * from", dbQuoteIdentifier(mycon, names), "limit", limits)
results <- lapply(queries, dbGetQuery, conn = mycon)

edited Dec 7, 2022 at 22:40

answered Dec 7, 2022 at 21:14

r2evans

167k8 gold badges92 silver badges176 bronze badges

Collectives™ on Stack Overflow

Creating SQL "Loops"

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related