Code

library(odbc)
library(DBI)
library(tidyverse)
library(gapminder)

Show the code

library(odbc)
library(DBI)
library(tidyverse)
library(gapminder)

gapminder_data=gapminder 

mtcars=mtcars

mtcars$names <- rownames(mtcars)

#Create database
#con <- dbConnect(drv = RSQLite::SQLite(),
#                 dbname = ":memory:")


con <- dbConnect(drv = RSQLite::SQLite(),
                 dbname = "test_db.sqlite")

#store sampledatabase
dbWriteTable(conn = con, 
             name = "gapminder_data_table",
             value = gapminder_data,
             overwrite = TRUE)


dbWriteTable(conn = con, 
             name = "mtcars_table",
             value = mtcars,
             overwrite = TRUE)

Code

dbListTables(con)

[1] "gapminder_data_table" "mtcars_table"

Code

dbExistsTable(con, 'gapminder_data_table')

[1] TRUE

Code

SELECT * from mtcars_table

Displaying records 1 - 10
mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb	names
21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4	Mazda RX4
21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	Mazda RX4 Wag
22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	Hornet 4 Drive
18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	Hornet Sportabout
18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	Valiant
14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	Duster 360
24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	Merc 240D
22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	Merc 230
19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	Merc 280

Code

dbDisconnect(con)

Reference

https://solutions.rstudio.com/db/getting-started/database-queries/