Database connections (DBI, RSQLite) in R Programming - Step-by-Step Execution

Concept Flow - Database connections (DBI, RSQLite)

Load DBI and RSQLite libraries

↓

Create connection object to SQLite DB

↓

Use connection to send queries

↓

Fetch results or write data

↓

Close connection to release resources

This flow shows how to load libraries, connect to a SQLite database, run queries, and then close the connection.

Execution Sample

R Programming

library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cars", mtcars)
res <- dbReadTable(con, "cars")
dbDisconnect(con)

This code connects to an in-memory SQLite database, writes the mtcars dataset as a table, reads it back, and then disconnects.

Execution Table

Step	Action	Evaluation	Result
1	Load DBI and RSQLite libraries	library(DBI), library(RSQLite)	Libraries loaded
2	Connect to SQLite in-memory DB	dbConnect(RSQLite::SQLite(), ":memory:")	Connection object created
3	Write mtcars data to 'cars' table	dbWriteTable(con, "cars", mtcars)	Table 'cars' created with mtcars data
4	Read 'cars' table data	dbReadTable(con, "cars")	Data frame with mtcars data returned
5	Disconnect from DB	dbDisconnect(con)	Connection closed

💡 Connection closed, no further DB operations possible

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
con	NULL	Connection object	Connection object	Connection object	NULL
res	NULL	NULL	NULL	Data frame with mtcars	Data frame with mtcars

Key Moments - 3 Insights

Why do we need to disconnect the database connection?

Disconnecting frees resources and avoids locking the database. See Step 5 in execution_table where dbDisconnect closes the connection.

What does ':memory:' mean in the connection?

It means the database is created in RAM and is temporary. This is shown in Step 2 where the connection is to an in-memory SQLite database.

Why do we use dbWriteTable before reading data?

Because the database starts empty, we must write data first. Step 3 writes mtcars to the 'cars' table so Step 4 can read it.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'con' after Step 2?

A NULL

B Data frame

C Connection object

D Disconnected

💡 Hint

Check variable_tracker column 'After Step 2' for 'con'

At which step is the mtcars data written into the database?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at execution_table action descriptions for writing data

If we skip dbDisconnect, what happens to 'con' variable at the end?

A It becomes NULL

B It remains a connection object

C It becomes a data frame

D It throws an error

💡 Hint

See variable_tracker 'con' final value after Step 5

Concept Snapshot

library(DBI) and RSQLite allow connecting to SQLite databases.
Use dbConnect() to open a connection.
Use dbWriteTable() to save data, dbReadTable() to read.
Always close with dbDisconnect() to free resources.
':memory:' creates a temporary in-RAM database.

Full Transcript

This example shows how to connect to a SQLite database in R using DBI and RSQLite. First, we load the libraries. Then, we create a connection to an in-memory database. Next, we write the built-in mtcars dataset into a table named 'cars'. After that, we read the data back from the 'cars' table into a variable. Finally, we disconnect from the database to release resources. Variables 'con' and 'res' track the connection and data respectively. Disconnecting is important to avoid resource leaks. The ':memory:' database is temporary and disappears after disconnecting.

Practice

1. What is the main purpose of dbConnect() in R when working with databases?

easy

A. To run a SQL query and get results

B. To close an open database connection

C. To open a connection to a database

D. To create a new table in the database

Solution

1. Step 1: Understand the role of dbConnect()

   dbConnect() is used to start a connection between R and a database so they can communicate.

2. Step 2: Differentiate from other functions

   dbDisconnect() closes connections, dbGetQuery() runs queries, and dbExecute() changes data. Only dbConnect() opens the connection.

3. Final Answer:

   To open a connection to a database -> Option C

4. Quick Check:

   dbConnect() = open connection [OK]

Hint: Remember: Connect opens, Disconnect closes [OK]

Common Mistakes:

• Confusing dbConnect() with dbDisconnect()
• Thinking dbConnect() runs queries
• Assuming dbConnect() creates tables

2. Which of the following is the correct syntax to connect to an SQLite database file named mydata.sqlite using DBI in R?

easy

A. dbConnect(SQLite::RSQLite(), file = "mydata.sqlite")

B. dbConnect(SQLite, "mydata.sqlite")

C. dbConnect(RSQLite, dbname = "mydata.sqlite")

D. dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite")

Solution

1. Step 1: Identify correct function usage

   The correct way is to call dbConnect() with the driver RSQLite::SQLite() and specify the database file with dbname.

2. Step 2: Check each option

   dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") uses the correct driver and argument name. Options B, C, and D misuse the driver or argument names.

3. Final Answer:

   dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") -> Option D

4. Quick Check:

   Correct driver and dbname argument = dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") [OK]

Hint: Use RSQLite::SQLite() and dbname for file [OK]

Common Mistakes:

• Using wrong driver syntax
• Using 'file' instead of 'dbname'
• Omitting the namespace RSQLite::

3. What will be the output of the following R code?

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE test (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob')")
res <- dbGetQuery(con, "SELECT name FROM test WHERE id = 2")
print(res)
dbDisconnect(con)

medium

A. name
1 Bob

B. id name
1 2 Bob

C. Error: no such table: test

D. NULL

Solution

1. Step 1: Understand the code flow

   The code creates an in-memory SQLite database, makes a table 'test', inserts two rows, then queries the name where id=2.

2. Step 2: Predict the query result

   The query selects the 'name' column for id=2, which is 'Bob'. The printed result is a data frame with one row and one column 'name' containing 'Bob'.

3. Final Answer:

   name 1 Bob -> Option A

4. Quick Check:

   Query returns name 'Bob' for id=2 [OK]

Hint: SELECT returns matching rows as data frame [OK]

Common Mistakes:

• Expecting full row instead of selected column
• Confusing dbExecute with dbGetQuery output
• Forgetting to disconnect connection

4. Identify the error in the following R code snippet that tries to connect to an SQLite database and run a query:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), "mydb.sqlite")
result <- dbGetQuery(con, "SELECT * FROM users")
dbDisconnect(con)

medium

A. dbDisconnect() should be called before dbGetQuery()

B. The database file name should be passed as dbname argument, not directly

C. dbGetQuery() cannot be used with SQLite

D. The library DBI is not needed for SQLite connections

Solution

1. Step 1: Check dbConnect() usage

   The SQLite file name must be passed as dbname = "mydb.sqlite", not as a positional argument.

2. Step 2: Verify other function calls

   Calling dbGetQuery() before dbDisconnect() is correct. dbGetQuery() works with SQLite. The DBI library is required.

3. Final Answer:

   The database file name should be passed as dbname argument, not directly -> Option B

4. Quick Check:

   Use dbname= for file in dbConnect() [OK]

Hint: Always name dbname argument in dbConnect() [OK]

Common Mistakes:

• Passing file name without dbname=
• Disconnecting before query
• Thinking DBI is unnecessary

5. You want to create a new SQLite database in R, add a table named employees with columns id (integer) and name (text), insert three employees, and then retrieve all names. Which sequence of commands correctly achieves this?

hard

A. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT name FROM employees")
dbDisconnect(con)
print(names)

B. con <- dbConnect(RSQLite::SQLite(), "company.sqlite")
dbExecute(con, "CREATE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT name FROM employees")
dbDisconnect(con)
print(names)

C. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id TEXT, name INTEGER)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT id FROM employees")
dbDisconnect(con)
print(names)

D. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna')")
dbExecute(con, "INSERT INTO employees VALUES (2, 'Ben')")
dbExecute(con, "INSERT INTO employees VALUES (3, 'Cara')")
names <- dbGetQuery(con, "SELECT id FROM employees")
dbDisconnect(con)
print(names)

Solution

1. Step 1: Identify correct sequence

   Use dbConnect(RSQLite::SQLite(), dbname = "company.sqlite"), CREATE TABLE employees (id INTEGER, name TEXT), multi-row INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara'), SELECT name FROM employees.

2. Step 2: Eliminate distractors

   B: No dbname=, missing TABLE. C: Wrong types (name INTEGER fails insert), selects id. D: Selects id instead of name.

3. Final Answer:

   dbConnect(RSQLite::SQLite(), dbname = "company.sqlite"), correct CREATE TABLE, multi-row INSERT, SELECT name -> Option A

4. Quick Check:

   dbname=, id INTEGER name TEXT, INSERT 3 rows, SELECT name [OK]

Hint: Insert multiple rows in one dbExecute for efficiency [OK]

Common Mistakes:

• Passing file name without dbname=
• Wrong SQL syntax for CREATE TABLE
• Mixing column types (id TEXT instead of INTEGER)
• Selecting id instead of name
• Forgetting to disconnect