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.thecoatlessprofe...
lahman2016.sqlite: https://coatless.github.io/raw-data/l...
demo-table-creation.sql: https://demo.stat447.thecoatlessprofe...
demo-table-insert.sql: https://demo.stat447.thecoatlessprofe...
test-sql-connection.sql: https://demo.stat447.thecoatlessprofe...
----
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.
Watch video Working with SQLite3 in R using RStudio's Connection Panel on Windows online without registration, duration hours minute second in high quality. This video was added by user TheCoatlessProfessor 07 February 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 78 once and liked it people.