Database Interface and 'MariaDB' Driver

Implements a 'DBI'-compliant interface to 'MariaDB' (< https://mariadb.org/>) and 'MySQL' (< https://www.mysql.com/>) databases.


Build Status AppVeyor Build Status Coverage Status CRAN_Status_Badge CRAN RStudio mirror downloads

RMariaDB is a database interface and MariaDB driver for R. This version is aimed at full compliance with the DBI specification, as a replacement for the old RMySQL package.

library(DBI)
# Connect to my-db as defined in ~/.my.cnf
con <- dbConnect(RMariaDB::MariaDB(), 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)

Installation

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

install.packages("RMariaDB")

The development version from github:

# install.packages("devtools")
devtools::install_github("r-dbi/DBI")
devtools::install_github("r-dbi/RMariaDB")

Installation from source on Linux or OS X currently requires Oracle's libmysqlclient or the more modern MariaDB Connector/C. The latter works best in version 2.3.4/3.0.3 or later, with older versions character and blob columns do not work reliably.

MySQL client library

On recent Debian or Ubuntu install libmysqlclient-dev.

sudo apt-get install -y libmysqlclient-dev

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

sudo yum install mysql-devel

Follow instructions to enable the MySQL yum repository if the above command attempts to install MariaDB files.

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

brew install mysql-connector-c

Connector/C

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

MariaDB configuration file

Instead of specifying a username and password in calls to dbConnect(), it's better to set up a MariaDB 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 MariaDB option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For a default single user install of MariaDB, the following code should work:

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

Acknowledgements

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 to the original RMySQL package, and to all contributors (of code and discussions) to this package.

News

RMariaDB 1.0.6 (2018-05-05)

  • Add support for bigint argument to dbConnect(), supported values are "integer64", "integer", "numeric" and "character". Large integers are returned as values of that type (r-dbi/DBItest#133).
  • Data frames resulting from a query always have unique non-empty column names (r-dbi/DBItest#137).
  • New arguments temporary and fail_if_missing (default: TRUE) to dbRemoveTable() (r-dbi/DBI#141, r-dbi/DBI#197).
  • Using dbCreateTable() and dbAppendTable() internally (r-dbi/DBI#74).
  • Implement format() method for MariaDBConnection (r-dbi/DBI#163).
  • Reexporting Id(), DBI::dbIsReadOnly() and DBI::dbCanConnect().
  • Now imports DBI 1.0.0.

RMariaDB 1.0.5 (2018-04-02)

  • dbGetException() is no longer reexported from DBI.
  • NaN and Inf are converted to NULL when writing to the database (#77).
  • Values of class "integer64" are now supported for dbWriteTable() and dbBind() (#87).
  • Schema support, as specified by DBI: dbListObjects(), dbUnquoteIdentifier() and Id().
  • Names in the x argument to dbQuoteIdentifier() are preserved in the output (r-lib/DBI#173).
  • Replace non-portable timegm() with private implementation (#78).
  • Update libmariadbclient to version 2.3.5 on Windows (#92, @jeroen).

RMariaDB 1.0-4 (2017-12-11)

  • Attempt to fix Solaris builds by redefining timegm symbol if the macro sun is defined.
  • Fix examples running on CRAN by using only temporary tables and overwrite = TRUE.
  • Refactor connection and result handling to be more similar to other backends.
  • Add support for R 3.1, but DBI >= 0.5 is required (#68).
  • Queries that bypass the prepared statement framework (like SHOW PLUGINS) return data (#70, @nbenn).
  • A temporary table can be created via dbWriteTable() if a table by the same name exists. If a temporary table of the same name exists, the error will be raised by the database itself, because this condition cannot be checked beforehand.

RMariaDB 1.0-2 (2017-10-01)

Initial release, compliant to the DBI specification.

  • Test almost all test cases of the DBI specification.
  • Fully support parametrized queries (#22, #27, #39).
  • Queries not supported by the prepared statement protocol still can be run via dbExecute() or dbSendStatement(), the function mariadbExecQuery() has been removed (#28).
  • Spec-compliant transactions (#38, #49).
  • 64-bit integers are now supported through the bit64 package. Unfortunately, this also means that numeric literals (as in SELECT 1) are returned as 64-bit integers (#12).
  • Correct handling of DATETIME and TIME columns (#52, @noahwilliamsson). Support timestamp values with sub-second precision on output, and with year beyond 2038 (#56).
  • The connection now uses the "utf8mb4" charset by default (#7).
  • New default row.names = FALSE.
  • New SSL-related arguments to dbConnect(): ssl.key, ssl.cert, ssl.ca, ssl.capath, ssl.cipher (#131, #148, @adamchainz).
  • CI for Linux, Windows, and OS X (#25).
  • Support for Connector/C from both MariaDB and MySQL.

RMariaDB 0.11-1 (2016-03-24)

  • RMariaDB fully supports DATE and DATETIME columns. On output, DATE columns will be converted to vectors of Dates and DATETIME will be converted to POSIXct. To faciliate correct computation of time zone, RMariaDB always sets the session timezone to UTC.

  • RMariaDB has been rewritten (essentially from scratch) in C++ with Rcpp. This has considerably reduced the amount of code, and allow us to take advantage of the more sophisticated memory management tools available in Rcpp. This rewrite should yield some minor performance improvements, but most importantly protect against memory leaks and crashes. It also provides a better base for future development.

  • Support for prepared queries: create prepared query with dbSendQuery() and bind values with dbBind(). dbSendQuery() and dbGetQuery() also support inline parameterised queries, like dbGetQuery(mariadbDefault(), "SELECT * FROM mtcars WHERE cyl = :cyl", params = list(cyl = 4)). This has no performance benefits but protects you from SQL injection attacks.

  • dbListFields() has been removed. Please use dbColumnInfo() instead.

  • dbGetInfo() has been removed. Please use the individual metadata functions.

  • Information formerly contain in summary() methods has now been integrated into show() methods.

  • make.db.names() has been deprecated. Use dbQuoteIdentifier() instead.

  • isIdCurrent() has been deprecated. Use dbIsValid() instead.

  • dbApply(), dbMoreResults() and dbNextResults() have been removed. These were always flagged as experimental, and now the experiment is over.

  • dbEscapeStrings() has been deprecated. Please use dbQuoteStrings() instead.

  • dbObjectId compatibility shim removed

  • Add SSL support on Windows.

  • Fix repetition of strings in subsequent rows (@peternowee, #125).

  • Always set connection character set to utf-8

  • Backport build system improvements from stable branch

  • Reenable Travis-CI, switch to R Travis, collect coverage

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("RMariaDB")

1.0.6 by Kirill Müller, a year ago


https://github.com/r-dbi/RMariaDB, https://downloads.mariadb.org/connector-c/


Report a bug at https://github.com/r-dbi/RMariaDB/issues


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


Authors: Kirill Müller [aut, cre] , Jeroen Ooms [aut] , David James [aut] , Saikat DebRoy [aut] , Hadley Wickham [aut] , Jeffrey Horner [aut] , R Consortium [fnd] , RStudio [cph]


Documentation:   PDF Manual  


Task views: Databases with R


GPL-2 license


Imports bit64, DBI, hms, methods, Rcpp

Suggests DBItest, rprojroot, testthat

Linking to BH, plogr, Rcpp

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


Imported by AdhereR.

Suggested by RMySQL, dbplyr, dbx, lgr.


See at CRAN