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!

what is dat_csv?

Waldi
– Waldi

2021-04-07 18:09:45 +00:00
Commented Apr 7, 2021 at 18:09 — Waldi
– Waldi, Commented Apr 7, 2021 at 18:09

Waldi · Accepted Answer · 2021-04-07 18:12:10Z

2

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"))

answered Apr 7, 2021 at 18:12

Waldi

41.6k6 gold badges38 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

Creating Multiple SQL Tables based on the factors from a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related