Rmysql - My querys return a list

Question

Hi, I’m working with a database that I usually access with Workbench. To start importing directly into R, I am using the Rmysql library. I can make the connection and find my tables, but when it comes to importing with tbl it returns me a list. How can I make it return me a df (Tabela1 object in the example)?

Example:

con <- src_mysql(dbname = "dbname", 
                          host = "localhost", 
                          port = 3306, 
                          user = "user",
                          password = "my password")

tabela1 <- tbl(con, "tabela1")

I also tried the command dbReadTable , but it returns me an error message:

*Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbReadTable’ for signature ‘"src_dbi", "character"’*

Thanks for your help!

Answer 1

I never used this package. But the DBI package works for me:

con <- DBI::dbConnect(dbname = "dbname", 
                          host = "localhost", 
                          port = 3306, 
                          user = "user",
                          password = "my password")

tabela1 <- tbl(con, "tabela1")

Answer 2

I managed to solve my problem!

library(RMySQL)
library(dbplyr)


conexao <- dbConnect(RMySQL::MySQL(),
                     dbname = "name",
                     host = "localhost",
                     port = 3306,
                     user = "user",
                     password = "my password")


# Encontrar uma tabela em duas etapas:

rs <- dbSendQuery(conexao, "SELECT * FROM tabela1")
d1 <- dbFetch(rs)