episode 5 - sql databases and r

[DanielleQuinn]

Creating a New SQLite Database

• to add a table to the database on disk rather than in the R environment, using copy_to(...) requires the argument temporary=FALSE

• to actually make those changes to the database on disk, you need to disconnect using DBI::dbDisconnect(...)

I ran into problems recently that would require this information, but don't have enough experience using SQLite in conjunction with R to confidently say that it is a normal thing to have happened.

[aurielfournier]

Hello @DanielleQuinn thanks for opening an issue on this.

Are you saying this is an issue you've found with SQLite in general? or with a part of our lessons specifically?

[12345678yirgu]

Hello @DanielleQuinn Do you mean we have to treat SQL and R together?

[DanielleQuinn]

@aurielfournier perhaps both? It was my first time trying to connect to an SQL database using R and do things like update / add tables. Working through the lesson, I wasn't able to make permanent changes to the database using copy_to(...) without also adding the temporary argument and then disconnecting. It seemed, from my Google searches, that temporary = FALSE and disconnecting are common solutions to something that is a common problem. Thus, I wondered if it should be included in the lesson, but also can't be sure that, being a total novice at this process, I didn't simply miss something. I thought I'd ask those with more experience to take a look. Thanks! :)

[aurielfournier]

@DanielleQuinn thanks for the clarification.

So you are encountering an error when you run this chunk of code?

my_db_file <- "portal-database.sqlite"    
my_db <- src_sqlite(my_db_file, create = TRUE)  
copy_to(my_db, surveys)  
copy_to(my_db, plots)

[fmichonneau]

in theory, when using dplyr/dbplyr, the user shouldn't have to call DBI::dbDisconnect(...) directly.

I agree it's a good idea to spell out that temporary=TRUE needs to be specified if you want to keep the changes across your R session.

[orchid00]

Hi @DanielleQuinn I would like to add this resource for you https://db.rstudio.com just in case you need it in the future for more in depth options with R and SQL

[DanielleQuinn]

Thanks @orchid00! I will definitely take a look!

@aurielfournier The code will run without producing an error but it didn't accomplish my goal of making changes to the database on disk. Instead, it only made those changes within the R environment. I needed to use:

src_sqlite("data/all_data.sqlite", create=TRUE)
my_db<-dbConnect(RSQLite::SQLite(), "data/all_data.sqlite")
copy_to(my_db, ISCATCHES, temporary=FALSE)
DBI::dbDisconnect(my_db)

Could it have something to do with using my_db<-dbConnect(RSQLite::SQLite(), "data/all_data.sqlite") ? Here, it seems that I'm connecting using something other than dplyr/dbplyr, which could be the entire problem! I believe, though, that I initially tried following the lesson exactly and when I realized things weren't saving to disk, did some exploring and that line of code was added later.

Thanks for the discussion! : ) In the end, it works, but understanding why something has or hasn't worked is important, too!

[aurielfournier]

I agree @DanielleQuinn understanding why is important. Would you be willing to submit a pull request making that why more clear in our lessons?

[gabrielodom]

I just went through this lesson as well. Can confirm: my_db <- dbConnect(RSQLite::SQLite(), "data/portal-database.sqlite") and adding the temporary = FALSE argument to copy_to() are required to write to the disk.

[elliewix]

Hi! I'm writing a homework on this, and getting some odd behavior. I'm running off the code from the code handout, just to try and have a clean environment.

• setting the temporary = F arg helped getting it to disk
• the disconnect commands listed in the lesson fail, but from this discussion it appears that is not needed?
• when I open the file and look at the tables from any of the new dbs I've made, I've been seeing two extra tables being added: sqlite_stat1, sqlite_stat4
• DBI::dbDisconnect(db) works when I load the db as db <- DBI::dbConnect(RSQLite::SQLite(), "hw6-podcast-review-database.sqlite") but not with anything created by this function listed in the lessonmy_db <- src_sqlite(out, create = T)

Likely not all this is relevant to this issue, but I don't use these sqlite packages much and this is a new lesson for me. I'm writing a homework assignment based on this lesson and this issue has been holding me up. (don't rush on my account to fix it, I'm going to redirect to something else due to my local timeline).

[Teebusch]

Hi, I think this section needs an overhaul. Please see #790 for a reproducible example of creating a permanent database on disk, using the recommended, not deprecated, syntax (as far as I understand it)