r dbWriteTable in loop

Question

I have many table that I am saving to a MariaDb in AWS RDS. I can manually save the tables. However I want to create a loop to do and I can't figure out the syntax on the dbWriteTable command. library(RMySQL)

dbWriteTable(con, "Account" , Account, overwrite = T)
dbWriteTable(con, "Campaign",  Campaign, overwrite = T)
dbWriteTable(con, "Contact" , Contact, overwrite = T)
dbWriteTable(con, "User", User, overwrite =T)

Instead I would like to do it in a loop.

nm = c("Account", "Campaign", "Contact",  "User")

for (i in 1:length(nm)) {

  dbWriteTable(con,  nm[i], paste(nm[i]), overwrite = TRUE)
 }

I get an Error in eval(substitute(expr), envir, enclos) : external pointer is not valid. I have found that the dbWriteTable is finicky — Spruce Island, Oct 28 '16 at 21:58
for some reason get0() will work but get() does not work. Thanks for the tip — Spruce Island, Oct 28 '16 at 22:51
Interesting @Spruce Island, glad I could help. Mind if I post as an answer? — Zach, Oct 28 '16 at 22:53

score 3 · Accepted Answer · answered Oct 28 '16 at 23:09

3

Per comments above, using get0 instead of paste like so will work:

nm = c("Account", "Campaign", "Contact",  "User")

for (i in 1:length(nm)) {
    dbWriteTable(con,  nm[i], get0(nm[i]), overwrite = TRUE)
}

answered Oct 28 '16 at 23:09

Zach

1,103
8
11

score 0 · Answer 2 · answered Oct 28 '16 at 22:17

0

Try this instead:

for (i in nm){
  dbWriteTable(con,  i, paste(i), overwrite = TRUE)
}

You do not need to extract the i from the original list with brackets, because i represents directly that object in the list. Essentially you are saying for each instance in nm, write to the db table named i (which will be one of the words in your list) and paste the value of i as the name.

answered Oct 28 '16 at 22:17

sconfluentus

4,693
1
21
40

I get the error. Error in file(file, "rt") : cannot open the connection In addition: Warning message: In file(file, "rt") : cannot open file 'Account': No such file or directory. Its like the data.frame can't be found.. I have tried so many different things... – Spruce Island Oct 28 '16 at 22:28
Sorry it was of no help. Because I did not have the files to over-write I could not test. I ran the loop with prints and returns. But that sounds like a working directory directory issue...maybe `setwd(" your/directory/here")` – sconfluentus Oct 29 '16 at 02:15

r dbWriteTable in loop

2 Answers2