[R] Data Carpentry - Creating a New SQLite Database

[Bert Gunter]

Please note that tidyverse packages have their own support resources at
RStudio, whence they came; e.g. here:
https://education.rstudio.com/learn/beginner/
You may also do better asking about issues that concern them at their
support site:  https://support.rstudio.com/hc/en-us
 though, as you already found out, there are folks here who may help also.

Bert Gunter

"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Fri, Jan 10, 2020 at 10:32 AM Phillip Heinrich <herd_dog using cox.net> wrote:

> Working my way through a tutorial named Data Carpentry (
> https://datacarpentry.org/R-ecology-lesson/).  for the most part it is
> excellent but I’m stuck on the very last section (
> https://datacarpentry.org/R-ecology-lesson/05-r-and-databases.html).
>
> First, below are the packages I have loaded:
> [1] "forcats"   "stringr"   "purrr"     "readr"     "tidyr"     "tibble"
>   "ggplot2"   "tidyverse" "dbplyr"    "RMySQL"    "DBI"
> [12] "dplyr"     "RSQLite"   "stats"     "graphics"  "grDevices" "utils"
>    "datasets"  "methods"   "base"
>
>
>             >
>
>
> Second,
> Second, is the text of the last section of the last chapter titled
> “Creating a New SQLite Database”.
> Second, below is the text from the tutorial.  The black type is from the
> tutorial.  The green and blue is the suggested R code.  My comments are in
> red.
> Creating a new SQLite database
> So far, we have used a previously prepared SQLite database. But we can
> also use R to create a new database, e.g. from existing csv files. Let’s
> recreate the mammals database that we’ve been working with, in R. First
> let’s download and read in the csv files. We’ll import tidyverse to gain
> access to the read_csv() function.
>
> download.file("https://ndownloader.figshare.com/files/3299483",
>               "data_raw/species.csv")
> download.file("https://ndownloader.figshare.com/files/10717177",
>               "data_raw/surveys.csv")
> download.file("https://ndownloader.figshare.com/files/3299474",
>               "data_raw/plots.csv")
> library(tidyverse)
> species <- read_csv("data_raw/species.csv")No problem here.  I’m pulling
> three databases from the Web and saving them to a folder on my hard drive.
> (...data_raw/species.csv) etc.surveys <- read_csv("data_raw/surveys.csv")
> plots <- read_csv("data_raw/plots.csv")Again no problem.  I’m just creating
> an R data files.  But here is where I loose it.  I’m creating something
> named my_db_file from another file named portal-database-output with an
> sqlite extension and then creating my_db from the My_db_file.  Not sure
> where the sqlite extension file came from. Creating a new SQLite database
> with dplyr is easy. You can re-use the same command we used above to open
> an existing .sqlite file. The create = TRUE argument instructs R to create
> a new, empty database instead.
>
> Caution: When create = TRUE is added, any existing database at the same
> location is overwritten without warning.
>
> my_db_file <- "data/portal-database-output.sqlite"
> my_db <- src_sqlite(my_db_file, create = TRUE)Currently, our new database
> is empty, it doesn’t contain any tables:
>
> my_db#> src:  sqlite 3.29.0 [data/portal-database-output.sqlite]
> #> tbls:To add tables, we copy the existing data.frames into the database
> one by one:
>
> copy_to(my_db, surveys)
> copy_to(my_db, plots)
> my_dbI can follow the directions to fill in my_db but I have no idea how
> to access the tables.  The text from the tutorial below says to check the
> location of our database.  Huh!  Can someone give me some direction.
> Thanks.
>
>
>
>
>
> If you check the location of our database you’ll see that data is
> automatically being written to disk. R and dplyr not only provide easy ways
> to query existing databases, they also allows you to easily create your own
> databases from flat files!
>
>
>
> Here is where I loose it.
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

	[[alternative HTML version deleted]]