Working with SQLite3 in R using RStudio's Connection Panel on Windows

Video Timestamps:

00:00 Video Bumper
00:06 Introduction
00:19 Pre-requisites: R, RStudio, and an ODBC Driver for SQLite Installed
00:33 Open RStudio
00:39 Install R packages odbc, DBI, and RSQLite
01:33 Download the lahman2016.sqlite database
02:12 Identify the working directory
02:29 Open Windows explorer and move lahman2016.sqlite from Downloads to Documents
03:03 Create a new connection using RStudio's Connections panel in the upper-right
03:14 Select the SQLite3 ODBC Driver connection
03:29 Review R code generated and run by the wizard in R Console to create the `con` object
03:44 View `con` object in the Environment Panel
04:10 Create a new SQL document from the Connections Panel
04:29 Description of the !preview conn=con syntax to re-use the R object
04:52 Save SQL file prior to "Preview" being available
05:15 Run SQL code using the "Preview" button in RStudio's code editor
05:46 Create a Table in SQLite DB Using RStudio
06:10 Refresh Connections Data in the Connections Panel to see the new table
06:18 View Table Schema by Pressing the "Play" button on the left of the table name
06:57 View Data in the Table by Pressing the "Data" icon on the far right side of the table name
07:28 Insert values into the Table
07:28 Verify data was added by previewing the "data" on the right-hand side
08:48 Disconnect from the SQLite database
08:56 Discussions on SQLite database saved in memory being empty on re-connect
09:11 Connect via the RStudio Connections Panel back into the SQLite memory-based database.
09:46 Creating a file-backed SQLite3 database using RStudio's Connection Panel Wizard
09:52 Specify the location of the file-backed database using the parameter option of "Database=location-of-db.sqlite"
10:19 Viewing the path of the file-backed database in the RStudio Connection Panel
10:32 Populating the file-backed SQLite database by re-running the scripts
11:17 Verifying the data was added into the SQLite database using the interactive data viewer
11:33 Disconnecting from the file-backed database
11:42 Re-connect to the file-backed database and verify data is intact
12:34 Viewing a list of all connections by pressing the Blue back arrow
13:01 Creating a connection into the already existing file-backed Lahman 2016 SQLite database
13:13 Specifying the file-backed database location using the parameter of "Database=lahman2016.sqlite"
13:25 Exploring the lahman2016 database from the Connections Panel.
13:40 View table schema for AllStarsFull
13:44 View table data for AllStarsFull
14:04 View all connections loaded
14:27 Out roll

----

Links

- install-sqlite-packages-and-db.R: https://demo.stat447.thecoatlessprofessor.com/sql/sqlite3-rstudio-connections/install-sqlite-packages-and-db.R
- lahman2016.sqlite: https://coatless.github.io/raw-data/lahman2016.sqlite
- demo-table-creation.sql: https://demo.stat447.thecoatlessprofessor.com/sql/sqlite3-rstudio-connections/demo-table-creation.sql
- demo-table-insert.sql: https://demo.stat447.thecoatlessprofessor.com/sql/sqlite3-rstudio-connections/demo-table-insert.sql
- test-sql-connection.sql: https://demo.stat447.thecoatlessprofessor.com/sql/sqlite3-rstudio-connections/test-sql-connection.sql

----

Summary

We explored the Connections Panel in the upper right-hand side of RStudio.

We created a connection to SQLite using the ODBC SQLite Driver option.

Without supplying a file-backed database using `Database=location-of-database`, we used a memory-only database that we lost when we disconnected.