Database Interface and 'MySQL' Driver for R

A 'DBI' interface to 'MySQL' / 'MariaDB'. The 'RMySQL' package contains an old implementation based on legacy code from S-PLUS which being phased out. A modern 'MySQL' client based on 'Rcpp' is available from the 'RMariaDB' package on 'Github': < https://github.com/rstats-db/RMariaDB>.


RMySQL is a database interface and MySQL driver for R. This version complies with the database interface definition as implemented in the package DBI 0.2-2.

library(DBI)
# Connect to my-db as defined in ~/.my.cnf
con <- dbConnect(RMySQL::MySQL(), group = "my-db")
 
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
 
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")
 
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)
 
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
# Clear the result
dbClearResult(res)
 
# Disconnect from the database
dbDisconnect(con)

Binary packages for OS-X or Windows can be installed directly from CRAN:

install.packages("RMySQL")

The development version from github:

# install.packages("devtools")
devtools::install_github("rstats-db/DBI")
devtools::install_github("rstats-db/RMySQL")

Installation from source on Linux or OSX requires MariaDB Connector/C. On some older platforms you can also link against Oracle's libmysqlclient driver but the mariadb implementation is much better.

On recent Debian or Ubuntu install libmariadb-client-lgpl-dev. In Ubuntu 14.04 this was called libmariadbclient-dev.

sudo apt-get install -y libmariadb-client-lgpl-dev

On Fedora, CentOS or RHEL we need mariadb-devel:

sudo yum install mariadb-devel

On OS-X use mariadb-connector-c from Homebrew:

brew install mariadb-connector-c

Instead of specifying a username and password in calls to dbConnect(), it's better to set up a MySQL configuration file that names the databases that you connect to most commonly. This file should live in ~/.my.cnf and look like:

[database_name]
option1=value1
option2=value2

If you want to run the examples, you'll need to set the proper options in the [rs-dbi] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For a default single user install of MySQL, the following code should work:

[rs-dbi]
database=test
user=root
password=

Many thanks to Christoph M. Friedrich, John Heuer, Kurt Hornik, Torsten Hothorn, Saikat Debroy, Matthew Kelly, Brian D. Ripley, Mikhail Kondrin, Jake Luciani, Jens Nieschulze, Deepayan Sarkar, Louis Springer, Duncan Temple Lang, Luis Torgo, Arend P. van der Veen, Felix Weninger, J. T. Lindgren, Crespin Miller, and Michal Okonlewski, Seth Falcon and Paul Gilbert for comments, suggestions, bug reports, and patches.

News

Version 0.10.9

  • OSX binary packages for OSX now use mariadb-connector-c instead of mysql-connector-c

  • CMD check fix for DBI 0.4

Version 0.10.8

  • Replace #include S.h with R.h as required by BDR

Version 0.10.6

  • Fix configure script for OSX

Version 0.10.5

  • Refactor configure script

  • Fix for setInternet2 in R-devel on Windows

Version 0.10.4

  • Fix dbWriteTable bug with MySQL-based Infobright database

  • Use mariadb_config if available

  • Properly import used utils functions

Version 0.10.3

  • Fix the RPostgreSQL conflict

  • Remove the workaround for the bug in TSMySQL

  • Update URL to MariaDB client in DESCRIPTION

Version 0.10.2

  • Backport fixes from Rcpp branch.

  • Configure script now uses mysql_config if available.

  • MariaDB windows libraries have been updated to 2.1.0

  • Add SSL support to MariaDB windows libraries.

Version 0.10.1

  • Fix configure script for OSX 10.6 Snow Leopard

  • Issue in dbWriteTable() with temporary files on Windows fixed.

Version 0.10

  • New maintainer: Jeroen Ooms

  • Internal changes to support static linking on Windows; this means that windows a binary is now available on CRAN.

  • The following internal functions are no longer exported: please use the corresponding DBI generic instead:

    mysqlInitDriver, mysqlCloseDriver, mysqlDescribeDriver, mysqlDriverInfo, mysqlNewConnection, mysqlCloneConnection, mysqlDescribeConnection, mysqlConnectionInfo, mysqlCloseConnection, mysqlExecStatement, mysqlQuickSQL, mysqlDBApply, mysqlFetch, mysqlResultInfo, mysqlDescribeResult, mysqlDescribeFields, mysqlCloseResult, mysqlImportFile, mysqlReadTable, mysqlWriteTable, mysqlEscapeStrings, mysqlDataType, safe.write.

  • RMySQL gains transaction support with dbBegin(), dbCommit(), and dbRollback(), but note that MySQL does not allow data definition language statements to be rolled back.

  • The MySQLObject base class has been removed - there is no real shared behaviour for MySQLDriver, MySQLConnection and MySQLResult so this simplifies the code

  • Implemented methods for dbIsValid(); please use these instead of isIdCurrent().

  • Implement dbFetch() method; please use this in preference to fetch(). dbFetch() now returns a 0-row data frame (instead of an 0-col data frame) if there are no results.

  • Methods no longer automatically close open result sets. This was implemented inconsistently in a handful of places.

  • dbBuildTableDefinition() has been renamed to mysqlBuildTableDefinition().

  • dbWriteTable() has been rewritten:

    • It quotes field names using dbQuoteIdentifier(), rather than use a flawed black-list based approach with name munging.

    • It now throws errors on failure, rather than returning FALSE.

    • It will automatically add row names only if they are character, not integer.

    • When loading a file from disk, dbWriteTable() will no longer attempt to guess the correct values for row.names and header - instead supply them explicitly if the defaults are incorrect.

    • When given a zero-row data frame it will just creates the table definition.

  • Assorted fixes accumulated since last release 3 years ago.

  • MySQL() no longer has force.reload argument - it's not obvious that this ever worked.

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("RMySQL")

0.10.13 by Jeroen Ooms, 3 months ago


https://downloads.mariadb.org/connector-c/ (upstream)


Report a bug at https://github.com/rstats-db/rmysql/issues


Browse source code at https://github.com/cran/RMySQL


Authors: Jeroen Ooms [aut, cre], David James [aut], Saikat DebRoy [aut], Hadley Wickham [aut], Jeffrey Horner [aut], RStudio [cph]


Documentation:   PDF Manual  


GPL-2 license


Imports methods

Depends on DBI

Suggests testthat

System requirements: libmariadb-client-dev | libmariadb-client-lgpl-dev | libmysqlclient-dev (deb), mariadb-devel (rpm), mariadb | mysql-connector-c (brew), mysql56_dev (csw)


Imported by BETS, Causata, MetaIntegrator, TSMySQL, mdsr, nowcasting, taxizedb, tcpl, wordbankr.

Depended on by compendiumdb, dbConnect, gmDatabase.

Suggested by ProjectTemplate, TSdata, TSsql, WhopGenome, dbplyr, dplyr, etl, pool, quantmod, sqldf, toxboot, twitteR, valr.

Enhanced by sqlutils.


See at CRAN