Convert RMySQL queries into dataframes/tibbles for visualization in ggplot2

[Oleksiy]

I wrote a ton of queries in RMarkdown and trying to visualize results, but not sure how to convert my queries to dataframes.

# Libraries
library(RMySQL)
library(ggplot2)
library(dplyr)
library(DBI)

# Establish connection with the database
connection <- dbConnect(MySQL(), user = "user", password = "password", 
dbname = "db", host = "localhost")
connection

 # Sample query
 test_query <- "SELECT first_name, last_name 
           FROM actor
           LIMIT 5;"
 dbGetQuery(connection, test_query)

My output works

Appreciate any tips

[mfherman]

You are so close!! All you need to do here is to assign the result of dbGetQuery() to a data frame. Something like:

test_query <- "SELECT first_name, last_name 
           FROM actor
           LIMIT 5;"

test_df <- dbGetQuery(connection, test_query)

Then you can manipulate test_df as you would any regular R data frame (because it is one!).

[Oleksiy]

@mfherman, Matt, this is the most ridiculous question I have ever asked ahahaha.
I just put class() and realized that I won the "dumb and dumber" award today. Grad school definitely makes me like this - the more I learn the less I know. And I started to overthink ANY question now.
Apologize for this question :). You can delete, if necessary.

[mfherman]

Don't feel dumb! Managing to connect to a database using R is much more difficult that assigning an object, and you were able to make that work. Also, I'm sure other people will have similar questions in the future, so it's definitely useful to leave this up for the next person.

[Oleksiy]

@mfherman, true, I will have it as a badge of my stupidity. Seriously.

I just have re-read Are You Smart Enough to Work at Google and I am in this trap too now. I wish I was in Google at least ahahahahahhaha.