Access SQL-Database via R

RMySQL is a handy package to import data from a sql database in R.

One caveat: if the location is on localhost, the host needs to be host = "127.0.0.1", instead of host = "localhost"

library(RMySQL)

# if database on localhost
db = dbConnect(MySQL(), user='root', password='root', host = "127.0.0.1", port=8888, dbname="table_name")

# look at tables in database
dbListTables(db)

# variables in a table
dbListFields(db, 'table_name')

# sql query to get all data in a table
query = dbSendQuery(db, "SELECT * FROM table_name")

# save as dataframe
df = fetch(query, n=-1)

head(df)