Creating Multiple SQL Tables based on the factors from a column

Question

I am trying to create a SQLite table for each factor that I have in a R dataframe. I have created a for loop in an attempt to accomplish this, but when I include the dWriteTable() function it gives me an error. I believe it might have something to do with the "argument" but I can't say for certain.

Here is the code I currently have with the for loop:

# Connects to the database##
mydb <- dbConnect(RSQLite::SQLite(), "../Output/all_data.sqlite")

#Reads the selected table in database
mu_ut <- dbReadTable(mydb, "mu_ut")

for(i in unique(mu_ut$AnimalID)){
  AnID <- paste("AnID", i, sep = ".")
  dfname <- assign(AnID, mu_ut[mu_ut$AnimalID == i,])
  dbWriteTable(conn = mydb, name = dfname, value = dat_csv, 
               field.types = c(DateAndTime = "DATETIME", 
                               AnimalID = "CHAR",
                               Species = "CHAR",
                               Sex = "CHAR",
                               CurrentCohort = "CHAR",
                               BirthYear = "DATE",
                               CaptureUnit = "CHAR",
                               CaptureSubunit = "CHAR",
                               CaptureArea = "CHAR"))
}

I get the error message when I run it:

Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"SQLiteConnection", "data.frame", "data.frame"’

Any help would be appreciated!

Thank you!

Answer 1

When using dbWriteTable :

• name is a character string giving the name of the table.
• value is a dataframe with the values to write into the table

Try:

dbWriteTable(conn = mydb, name = AnID, value = mu_ut[mu_ut$AnimalID == i,],
               field.types = c(DateAndTime = "DATETIME", 
                               AnimalID = "CHAR",
                               Species = "CHAR",
                               Sex = "CHAR",
                               CurrentCohort = "CHAR",
                               BirthYear = "DATE",
                               CaptureUnit = "CHAR",
                               CaptureSubunit = "CHAR",
                               CaptureArea = "CHAR"))