How do I connect to mysql database with dbplyr

Question

I have an employee table in a local mysql database. I'm trying to use dbplyr and DBI to connect to the database/table and query data into a dataframe.

Heres is my code:

library(dbplyr)
library(DBI)
library(RMySQL)
my_db <-  DBI::dbConnect(RMySQL::MySQL()(), 
                        host = "127.0.0.1",
                        port = 3306,
                        user = "username",
                        password = "password"
  )

employee <- tbl(my_db, "employee")

view(employee)

When I run the script, I get the following error:

Error in DBI::dbConnect((RMySQL::MySQL())(), host = "127.0.0.1", user = "root",  : 
  attempt to apply non-function

I've been googling the error message, but have not been able to find a solution.

I using a mysql 8.0CE database and have been able to connect to it via shiny using the RMySQL library.

Answer 1

The documentation for dbConnect says that the first argument is: drv - an object that inherits from DBIDriver, or an existing DBIConnection object (in order to clone an existing connection).

When I connect to MS SQL server I use the command:

db_connection = dbConnect(odbc::odbc(), .connection_string = connection_string)

Where a connection string is an alternative way of capturing host, port, etc.

The RMySQL documentation gives the following example:

con <- dbConnect(RMySQL::MySQL(), dbname = "test")

So this issue is probably the extra pair of brackets:

# current code causing error
my_db <-  DBI::dbConnect(RMySQL::MySQL()(), 
# try this instead
my_db <-  DBI::dbConnect(RMySQL::MySQL(),