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 = "", instead of host = "localhost"


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

# look at tables in database

# 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)