Search code examples
rlapplyrmysql

R use values in vector to become name of dataframe

I am getting the names of tables from a database and I want to select all the data from each table and create a dataframe automatically. The code

nm = dbListTables(connection)

creates a vector like:

[1] "Account"                          "Credit__Amortization_Payment__c" 
[3] "Credit__Amortization_Schedule__c" "Credit__Loan_Payment__c"         
[5] "Credit__Loan__c"

As an example,

 nm = c("Account", "Credit__Amortization_Payment__c", "Credit__Amortization_Schedule__c", "Credit__Loan_Payment__c", "Credit__Loan__c")

I create a list of a query for each value in a loop: 

qry <- list()
for (i in 1:length(nm)) {
  qry[i] <- paste0("select * from ", nm[i])
  }

Then I can't figure out how to save the name of the data frame as the values from the vector, so I have to manually type it out like this, but I would like to just loop through it saving a data frame with exactly the same name and structure from the list nm.

q = paste0(qry[1])
rs <- dbSendQuery(con, q)
Account <- dbFetch(rs, n=-1)

I repeat it here for each value in the vector manually but I would like to do this automatically based on the values in nm = dbListTables(connection)

q = paste0(qry[3])
rs <- dbSendQuery(con, q)
Credit__Amortization_Schedule__c <- dbFetch(rs, n=-1)
Solution

  • I think assign is what you are looking for, e.g. 

    > assign("x", 5)
> x
[1] 5

    In your case e.g. for query 1

    q <- paste0(qry[1])
rs <- dbSendQuery(con, q)
assign(nm[1], dbFetch(rs, n=-1))

    should work. Also have a look at the R FAQ.