Jeroen Ooms and I are very pleased to announce a new version of RMySQL, the R package that allows you to talk to MySQL (and MariaDB) databases. We have taken over maintenance from Jeffrey Horner, who has done a great job of maintaining the package of the last few years, but no longer has time to look after it. Thanks for all your hard work Jeff!
library(DBI) # Connect to a public database that I'm running on Google's # cloud SQL service. It contains a copy of the data in the # datasets package. con <- dbConnect(RMySQL::MySQL(), username = "public", password = "F60RUsyiG579PeKdCH", host = "18.104.22.168", port = 3306, dbname = "datasets" ) # Run a query dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 4 AND mpg < 23") #> row_names mpg cyl disp hp drat wt qsec vs am gear carb #> 1 Datsun 710 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1 #> 2 Merc 230 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2 #> 3 Toyota Corona 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1 #> 4 Volvo 142E 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2 # It's polite to let the database know when you're done dbDisconnect(con) #>  TRUE
It’s generally a bad idea to put passwords in your code, so instead of typing them directly, you can create a file called
~/.my.cnf that contains
[cloudSQL] username=public password=F60RUsyiG579PeKdCH host=22.214.171.124 port=3306 database=datasets
Then you can connect with:
con <- dbConnect(RMySQL::MySQL(), group = "cloudSQL")
RMySQL 0.10.0 is mostly a cleanup release. RMySQL is one of the oldest packages on CRAN, and according to the timestamps, it is older than many recommended packages, and only slightly younger than MASS! That explains why a facelift was well overdue.
The most important change is an improvement to the build process so that CRAN binaries are now available for Windows and OS X Mavericks. This should make your life much easier if you’re on one of these platforms. We’d love your feedback on the new build scripts. There have been many problems in the past, so we’d like to know that this client works well across platforms and versions of MySQL server.
Otherwise, the changes update RMySQL for DBI 0.3 compatibility:
mysql*() functions are no longer exported. Please use the corresponding DBI generics instead.
RMySQL gains transaction support with
dbRollback(). (But note that MySQL does not allow data definition language statements to be rolled back.)
Added method for
dbFetch(). Please use this instead of
dbFetch() now returns a 0-row data frame (instead of an 0-col data frame) if there are no results.
Added methods for
dbIsValid(). Please use these instead of
dbWriteTable() has been rewritten. It uses a better quoting strategy, throws errors on failure, and only automatically adds row names only if they’re strings. (NB:
dbWriteTable() also has a method that allows you load files directly from disk - this is likely to be faster if your file is one of the formats supported.)
For a complete list of changes, please see the full release notes.
Austin Chia, Founder of Any Instructor, describes his transition from studying biology to becoming a healthcare data analyst.
In this series, we walk through lesser-known tips and tricks to help you work more effectively and efficiently in R Markdown. The final post focuses on making your document look and work better.