# air_time, distance, hour, minute, time_hour
# carrier, flight, tailnum, origin, dest , # … with 336,766 more rows, and 11 more variables: arr_delay , # year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time I’m then going to create a simple function that takes each dataframe in the package and copies it to a table in the database. So below, I first need to modify my connection to connect to the database we just created. Great, okay my plan is to insert each of the five dataframes of nycflights13 into the db as tables. Let’s create a database of the nycflights13 data, and query it! dbGetQuery(con, "CREATE DATABASE nyc_flights ") #create the database The first argument is just the name of your connection (mine is con), and the second is your sql statement. The dbGetQuery function is the easiest way to read/write to the database. Note: If you restart your machine, you will have to re-run the bash command above…Not quite sure why that is, but you can probably figure out a more permanent solution.
#Mamp update mysql password
con <- DBI::dbConnect(RMariaDB::MariaDB(), user = Sys.getenv("mysql_user"), password = Sys.getenv("mysql_pass"), host = "localhost")ĭbGetQuery(conn = con, statement = "show databases ")Īwesome! We are connected to our MySQL instance that we installed with MAMP. System("ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock") So what we can do is write a bash command that creates a hard link from the MAMP location to where the RMariaDB package expects it: #either open terminal and run what's within quotes, or run it write from r with the system() function.
#Mamp update mysql install
Mine isn’t there because I used MAMP to install MySQL MAMP places the socket file here: /Applications/MAMP/tmp/mysql/mysql.sock. According to MySQL’s documentation, Unix systems use this socket file for connecting to MySQL databases. library(tidyverse)Ĭon <- DBI::dbConnect(RMariaDB::MariaDB(), user = Sys.getenv("mysql_user"), password = Sys.getenv("mysql_pass"), host = "localhost")Įrror: Failed to connect: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
#Mamp update mysql how to
You can learn more on how to do that on RStudio’s Databases using R page. Since I don’t want to embed my username and password, I am using environmental variables. Okay, let’s first see if we can connect to the database.
So if you haven’t done so already, install the following packages (along with nycflights13 which we’ll be using for a demo): install.packages("tidyverse") I tend to always trust the folks at RStudio for great package development, and this is a part of the DBI interface that in my experience gives the best performance for database work in R. So instead of attempting to set up an ODBC connection to MySQL, I am simply going to use the RMariaDB package coupled with DBI (I’m skipping the odbc package altogether). Setting up drivers, ODBC connections, and DSN files is difficult enough in Windows, let alone on a Mac. RStudio has a great website that walks through their enterprise packages for working with databases, but I’ve often found this site confusing. There are lots of different ways to connect to a db in R. This tutorial assumes you have already installed MAMP and have it currently running on your local machine in a macOS environment.
0 kommentar(er)
