error while looping through dbexecute command in R

Question

I am trying to loop through dbexceute command in r while processing my mode file to remove element by element and store them in a separate file for each element. But while running the loop I am getting an error in R.

 library("RSQLite")
 sqlite <- dbDriver("SQLite")
 dbname <- "models_signif.db" 
 db = dbConnect(sqlite,dbname)
 brain_n <- dbGetQuery(db,"SELECT * FROM weights")
 x <- which(brain_n$gene %in% "ENSG00000130943.6")
 rsid_list <- brain_n$rsid[x]
 ##now loop for generating the files
 for( i in 1:128){
   #copy original file db to another
   #use that for modifications: 
   library("RSQLite")
   sqlite <- dbDriver("SQLite")
   dbname <- "models_signif.db" 
   db = dbConnect(sqlite,dbname)
   #brain_cortex_gtex_n <- dbGetQuery(db,"SELECT * FROM weights")
   require("RSQLite")
   sqlite <- dbDriver("SQLite")
   myfile <- paste0("modelsn","_",i,".db")
   dbname1 <- myfile
   db1 = dbConnect(sqlite,dbname1)
   sqliteCopyDatabase(db, db1)
   dbDisconnect(db)
   #gtex_n_1 <- dbGetQuery(db1,"SELECT * FROM weights") ##dim 9453
   ##now filter row1098 its beta is 0.002 and rsid is rs4560235
   #dbGetQuery(db,"SELECT * FROM weights") ##dim 5415rows
   rsid <- rsid_list[i]
   dbExecute(db1,"DELETE FROM weights WHERE rsid=rsid_list[i] ") 
   dbDisconnect(db1)
 }

Error: 
Error: near "[i]": syntax error

Answer 1

rdis_list[i] is R code, not SQL code. The SQLite engine (or any DBMS engine) is not going to interpret it the same. And since it is in a string in R, the R parser is not going to try to infer that it should be replaced with something.

The quick/hasty (and not best-practices) approach would be to paste it into your query. Again, not the recommended approach.

   dbExecute(db1,paste("DELETE FROM weights WHERE rsid=",
                       dbQuoteLiteral(db1, rsid_list[i])))

The better approach is to use "parameter binding", for several reasons. Use ? each place you are putting in "data"-like things in your query, and add params=list(..) to the query/statement.

    dbExecute(db1,"DELETE FROM weights WHERE rsid=?",
              params = list(rsid_list[i]))

See parameterized queries for more details about using bound-parameters like this.

Some side notes:

1. You define sqlite, dbname, and db outside the loop, it is wasteful, unnecessary, and helping not-at-all to recreate those variables each pass in the loop. The variables are accessible from within for, use them.

2. Don't use require there, for two reasons. First, you've already loaded library("RSQLite"), so the package functions are already available in your environment, so doing require is doing absolutely nothing but wasting (micro)time. Second, only use require(.) (for any use) if you believe that the package may not be available, you capture its return value, and react differently on its results. See https://stackoverflow.com/a/51263513/3358272, https://yihui.org/en/2014/07/library-vs-require/, and for details about the related functions, https://r-pkgs.org/namespace.html#search-path.