| Version: | 0.11.1 |
| Title: | Database Interface and 'MySQL' Driver for R |
| Description: | Legacy 'DBI' interface to 'MySQL' / 'MariaDB' based on old code ported from S-PLUS. A modern 'MySQL' client written in 'C++' is available from the 'RMariaDB' package. |
| Depends: | R (≥ 2.8.0), DBI (≥ 0.4) |
| Imports: | methods |
| License: | GPL-2 |
| URL: | https://r-dbi.r-universe.dev/RMySQLhttps://downloads.mariadb.org/connector-c/ |
| BugReports: | https://github.com/r-dbi/rmysql/issues |
| SystemRequirements: | libmariadb-client-dev | libmariadb-client-lgpl-dev| libmysqlclient-dev (deb), mariadb-devel (rpm), mariadb |mysql-connector-c (brew), mysql56_dev (csw) |
| NeedsCompilation: | yes |
| Collate: | 'mysql.R' 'driver.R' 'connection.R' 'data-type.R' 'default.R''escaping.R' 'result.R' 'extension.R' 'is-valid.R' 'table.R''transaction.R' |
| Suggests: | testthat, curl |
| RoxygenNote: | 7.3.2.9000 |
| Packaged: | 2025-04-01 13:02:47 UTC; jeroen |
| Author: | Jeroen Ooms [aut, cre], David James [aut], Saikat DebRoy [aut], Hadley Wickham [aut], Jeffrey Horner [aut], RStudio [cph] |
| Maintainer: | Jeroen Ooms <jeroenooms@gmail.com> |
| Repository: | CRAN |
| Date/Publication: | 2025-04-01 13:40:02 UTC |
Class MySQLDriver with constructor MySQL.
Description
An MySQL driver implementing the R database (DBI) API.This class should always be initialized with theMySQL() function.It returns a singleton that allows you to connect to MySQL.
Usage
MySQL(max.con = 16, fetch.default.rec = 500)Arguments
max.con | maximum number of connections that can be openat one time. There's no intrinic limit, since strictly speaking this limitapplies to MySQLservers, but clients can have (at least in theory)more than this. Typically there are at most a handful of open connections,thus the internal |
fetch.default.rec | number of records to fetch at one time from thedatabase. (The |
Examples
if (mysqlHasDefault()) {# connect to a database and load some datacon <- dbConnect(RMySQL::MySQL(), dbname = "test")dbWriteTable(con, "USArrests", datasets::USArrests, overwrite = TRUE)# queryrs <- dbSendQuery(con, "SELECT * FROM USArrests")d1 <- dbFetch(rs, n = 10) # extract data in chunks of 10 rowsdbHasCompleted(rs)d2 <- dbFetch(rs, n = -1) # extract all remaining datadbHasCompleted(rs)dbClearResult(rs)dbListTables(con)# clean updbRemoveTable(con, "USArrests")dbDisconnect(con)}Class MySQLConnection.
Description
MySQLConnection. objects are usually created bydbConnect
Class MySQLResult
Description
MySQL's query results class. This classes encapsulates the result of an SQLstatement (eitherselect or not).
Constants
Description
Constants
Constants
.MySQLPkgName (currently"RMySQL"),.MySQLPkgVersion (the R package version),.MySQLPkgRCS (theRCS revision),.MySQLSQLKeywords (a lot!)
Database interface meta-data
Description
Database interface meta-data
Usage
## S4 method for signature 'MySQLConnection'dbGetInfo(dbObj, what = "", ...)## S4 method for signature 'MySQLConnection'dbListResults(conn, ...)## S4 method for signature 'MySQLConnection'summary(object, verbose = FALSE, ...)## S4 method for signature 'MySQLConnection'dbGetException(conn, ...)## S4 method for signature 'MySQLConnection'show(object)Arguments
what | optional |
... | Other arguments for compatibility with generic. |
conn,dbObj,object | MySQLConnection object. |
verbose | If |
Examples
if (mysqlHasDefault()) { con <- dbConnect(RMySQL::MySQL(), dbname = "test") summary(con) dbGetInfo(con) dbListResults(con) dbListTables(con) dbDisconnect(con)}Apply R/S-Plus functions to remote groups of DBMS rows (experimental)
Description
Applies R/S-Plus functions to groups of remote DBMS rows without bringing anentire result set all at once. The result set is expected to be sorted bythe grouping field.
Usage
dbApply(res, ...)## S4 method for signature 'MySQLResult'dbApply( res, INDEX, FUN = stop("must specify FUN"), begin = NULL, group.begin = NULL, new.record = NULL, end = NULL, batchSize = 100, maxBatch = 1e+06, ..., simplify = TRUE)Arguments
res | a result set (see |
... | any additional arguments to be passed to |
INDEX | a character or integer specifying the field name or fieldnumber that defines the various groups. |
FUN | a function to be invoked upon identifying the last row from everygroup. This function will be passed a data frame holding the records of thecurrent group, a character string with the group label, plus any otherarguments passed to |
begin | a function of no arguments to be invoked just prior to retrievethe first row from the result set. |
group.begin | a function of one argument (the group label) to beinvoked upon identifying a row from a new group |
new.record | a function to be invoked as each individual record isfetched. The first argument to this function is a one-row data.frameholding the new record. |
end | a function of no arguments to be invoked just after retrievingthe last row from the result set. |
batchSize | the default number of rows to bring from the remote resultset. If needed, this is automatically extended to hold groups bigger than |
maxBatch | the absolute maximum of rows per group that may be extractedfrom the result set. |
simplify | Not yet implemented |
Details
This function is meant to handle somewhat gracefully(?) largeamounts of data from the DBMS by bringing into R manageable chunks (aboutbatchSize records at a time, but not more thanmaxBatch); theidea is that the data from individual groups can be handled by R, but notall the groups at the same time.
Value
A list with as many elements as there were groups in the result set.
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)res <- dbSendQuery(con, "SELECT * FROM mtcars ORDER BY cyl")dbApply(res, "cyl", function(x, grp) quantile(x$mpg, names=FALSE))dbClearResult(res)dbRemoveTable(con, "mtcars")dbDisconnect(con)}Experimental dbColumnInfo method for a connection
Description
Experimental dbColumnInfo method for a connection
Usage
## S4 method for signature 'MySQLConnection'dbColumnInfo(res, name, ...)Connect/disconnect to a MySQL DBMS
Description
These methods are straight-forward implementations of the correspondinggeneric functions.
Usage
## S4 method for signature 'MySQLDriver'dbConnect( drv, dbname = NULL, username = NULL, password = NULL, host = NULL, unix.socket = NULL, port = 0, client.flag = 0, groups = "rs-dbi", default.file = NULL, ...)## S4 method for signature 'MySQLConnection'dbConnect(drv, ...)## S4 method for signature 'MySQLConnection'dbDisconnect(conn, ...)Arguments
drv | an object of class |
dbname | string with the database name or NULL. If not NULL, theconnection sets the default daabase to this value. |
username,password | Username and password. If username omitted,defaults to the current user. If password is ommitted, only userswithout a password can log in. |
host | string identifying the host machine running the MySQL server orNULL. If NULL or the string |
unix.socket | (optional) string of the unix socket or named pipe. |
port | (optional) integer of the TCP/IP default port. |
client.flag | (optional) integer setting various MySQL client flags. Seethe MySQL manual for details. |
groups | string identifying a section in the |
default.file | string of the filename with MySQL client options.Defaults to |
... | Unused, needed for compatibility with generic. |
conn | an |
Examples
## Not run: # Connect to a MySQL database running locallycon <- dbConnect(RMySQL::MySQL(), dbname = "mydb")# Connect to a remote database with username and passwordcon <- dbConnect(RMySQL::MySQL(), host = "mydb.mycompany.com", user = "abc", password = "def")# But instead of supplying the username and password in code, it's usually# better to set up a group in your .my.cnf (usually located in your homedirectory). Then it's less likely you'll inadvertently share them.con <- dbConnect(RMySQL::MySQL(), group = "test")# Always cleanup by disconnecting the databasedbDisconnect(con)## End(Not run)# All examples use the rs-dbi group by default.if (mysqlHasDefault()) { con <- dbConnect(RMySQL::MySQL(), dbname = "test") summary(con) dbDisconnect(con)}Determine the SQL Data Type of an S object
Description
This method is a straight-forward implementation of the correspondinggeneric function.
Usage
## S4 method for signature 'MySQLDriver'dbDataType(dbObj, obj)## S4 method for signature 'MySQLConnection'dbDataType(dbObj, obj)Arguments
dbObj | A |
obj | R/S-Plus object whose SQL type we want to determine. |
Examples
dbDataType(RMySQL::MySQL(), "a")dbDataType(RMySQL::MySQL(), 1:3)dbDataType(RMySQL::MySQL(), 2.5)Escape SQL-special characters in strings.
Description
Escape SQL-special characters in strings.
Usage
dbEscapeStrings(con, strings, ...)## S4 method for signature 'MySQLConnection,character'dbEscapeStrings(con, strings)## S4 method for signature 'MySQLResult,character'dbEscapeStrings(con, strings, ...)Arguments
con | a connection object (see |
strings | a character vector. |
... | any additional arguments to be passed to the dispatched method. |
Value
A character vector with SQL special characters properly escaped.
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")tmp <- sprintf("SELECT * FROM emp WHERE lname = %s", "O'Reilly")dbEscapeStrings(con, tmp)dbDisconnect(con)}Execute a SQL statement on a database connection.
Description
To retrieve results a chunk at a time, usedbSendQuery,dbFetch, thendbClearResult. Alternatively, if you want all theresults (and they'll fit in memory) usedbGetQuery which sends,fetches and clears for you.
Usage
## S4 method for signature 'MySQLResult,numeric'dbFetch(res, n = -1, ...)## S4 method for signature 'MySQLResult,numeric'fetch(res, n = -1, ...)## S4 method for signature 'MySQLResult,missing'dbFetch(res, n = -1, ...)## S4 method for signature 'MySQLResult,missing'fetch(res, n = -1, ...)## S4 method for signature 'MySQLConnection,character'dbSendQuery(conn, statement, ...)## S4 method for signature 'MySQLResult'dbClearResult(res, ...)## S4 method for signature 'MySQLResult'dbGetInfo(dbObj, what = "", ...)## S4 method for signature 'MySQLResult'dbGetStatement(res, ...)## S4 method for signature 'MySQLResult,missing'dbListFields(conn, name, ...)Arguments
res,dbObj | A |
n | maximum number of records to retrieve per fetch. Use |
... | Unused. Needed for compatibility with generic. |
conn | an |
statement | a character vector of length one specifying the SQLstatement that should be executed. Only a single SQL statment should beprovided. |
what | optional |
name | Table name. |
Details
fetch() will be deprecated in the near future; please usedbFetch() instead.
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")dbWriteTable(con, "arrests", datasets::USArrests, overwrite = TRUE)# Run query to get results as dataframedbGetQuery(con, "SELECT * FROM arrests limit 3")# Send query to pull requests in batchesres <- dbSendQuery(con, "SELECT * FROM arrests")data <- dbFetch(res, n = 2)datadbHasCompleted(res)dbListResults(con)dbClearResult(res)dbRemoveTable(con, "arrests")dbDisconnect(con)}Get information about a MySQL driver.
Description
Get information about a MySQL driver.
Usage
## S4 method for signature 'MySQLDriver'dbGetInfo(dbObj, what = "", ...)## S4 method for signature 'MySQLDriver'dbListConnections(drv, ...)## S4 method for signature 'MySQLDriver'summary(object, verbose = FALSE, ...)## S4 method for signature 'MySQLDriver'show(object)Arguments
dbObj,object,drv | Object created by |
what | Optional |
... | Ignored. Needed for compatibility with generic. |
verbose | If |
Examples
db <- RMySQL::MySQL()dbdbGetInfo(db)dbListConnections(db)summary(db)Fetch next result set from an SQL script or stored procedure (experimental)
Description
SQL scripts (i.e., multiple SQL statements separated by ';') and storedprocedures oftentimes generate multiple result sets. These genericfunctions provide a means to process them sequentially.dbNextResultfetches the next result from the sequence of pending results sets;dbMoreResults returns a logical to indicate whether there areadditional results to process.
Usage
dbNextResult(con, ...)## S4 method for signature 'MySQLConnection'dbNextResult(con, ...)dbMoreResults(con, ...)## S4 method for signature 'MySQLConnection'dbMoreResults(con, ...)Arguments
con | a connection object (see |
... | any additional arguments to be passed to the dispatched method |
Value
dbNextResult returns a result set orNULL.
dbMoreResults returns a logical specifying whether or not there areadditional result sets to process in the connection.
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test", client.flag = CLIENT_MULTI_STATEMENTS)dbWriteTable(con, "mtcars", datasets::mtcars, overwrite = TRUE)sql <- "SELECT cyl FROM mtcars LIMIT 5; SELECT vs FROM mtcars LIMIT 5"rs1 <- dbSendQuery(con, sql)dbFetch(rs1, n = -1)if (dbMoreResults(con)) { rs2 <- dbNextResult(con) dbFetch(rs2, n = -1)}dbClearResult(rs1)dbClearResult(rs2)dbRemoveTable(con, "mtcars")dbDisconnect(con)}Quote method for MySQL identifiers
Description
In MySQL, identifiers are enclosed in backticks, e.g.`x`.
Usage
## S4 method for signature 'MySQLConnection,character'dbQuoteIdentifier(conn, x, ...)Convenience functions for importing/exporting DBMS tables
Description
These functions mimic their R/S-Plus counterpartget,assign,exists,remove, andobjects, except that they generatecode that gets remotely executed in a database engine.
Usage
## S4 method for signature 'MySQLConnection,character'dbReadTable(conn, name, row.names, check.names = TRUE, ...)## S4 method for signature 'MySQLConnection'dbListTables(conn, ...)## S4 method for signature 'MySQLConnection,character'dbExistsTable(conn, name, ...)## S4 method for signature 'MySQLConnection,character'dbRemoveTable(conn, name, ...)## S4 method for signature 'MySQLConnection,character'dbListFields(conn, name, ...)Arguments
conn | a |
name | a character string specifying a table name. |
row.names | A string or an index specifying the column in the DBMS tableto use as |
check.names | If |
... | Unused, needed for compatiblity with generic. |
Value
A data.frame in the case ofdbReadTable; otherwise a logicalindicating whether the operation was successful.
Note
Note that the data.frame returned bydbReadTable only hasprimitive data, e.g., it does not coerce character data to factors.
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")# By default, row names are written in a column to row_names, and# automatically read back into the row.names()dbWriteTable(con, "mtcars", mtcars[1:5, ], overwrite = TRUE)dbReadTable(con, "mtcars")dbReadTable(con, "mtcars", row.names = NULL)}Unload MySQL driver.
Description
Unload MySQL driver.
Usage
## S4 method for signature 'MySQLDriver'dbUnloadDriver(drv, ...)Arguments
drv | Object created by |
... | Ignored. Needed for compatibility with generic. |
Value
A logical indicating whether the operation succeeded or not.
Write a local data frame or file to the database.
Description
Write a local data frame or file to the database.
Usage
## S4 method for signature 'MySQLConnection,character,data.frame'dbWriteTable( conn, name, value, field.types = NULL, row.names = TRUE, overwrite = FALSE, append = FALSE, ..., allow.keywords = FALSE)## S4 method for signature 'MySQLConnection,character,character'dbWriteTable( conn, name, value, field.types = NULL, overwrite = FALSE, append = FALSE, header = TRUE, row.names = FALSE, nrows = 50, sep = ",", eol = "\n", skip = 0, quote = "\"", ...)Arguments
conn | a |
name | a character string specifying a table name. |
value | a data.frame (or coercible to data.frame) object or afile name (character). In the first case, the data.frame iswritten to a temporary file and then imported to SQLite; when |
field.types | character vector of named SQL field types wherethe names are the names of new table's columns. If missing, types inferredwith |
row.names | A logical specifying whether the |
overwrite | a logical specifying whether to overwrite an existing tableor not. Its default is |
append | a logical specifying whether to append to an existing tablein the DBMS. Its default is |
... | Unused, needs for compatibility with generic. |
allow.keywords | logical indicating whether column names that happen tobe MySQL keywords be used as column names in the resulting relation (table)being written. Defaults to FALSE, forcing mysqlWriteTable to modify columnnames to make them legal MySQL identifiers. |
header | logical, does the input file have a header line? Default is thesame heuristic used by |
nrows | number of lines to rows to import using |
sep | field separator character |
eol | End-of-line separator |
skip | number of lines to skip before reading data in the input file. |
quote | the quote character used in the input file (defaults to |
Check if a database object is valid.
Description
Support function that verifies that an object holding a reference to aforeign object is still valid for communicating with the RDBMS.isIdCurrent will be deprecated in the near future; please usethedbIsValid() generic instead.
Usage
isIdCurrent(obj)## S4 method for signature 'MySQLDriver'dbIsValid(dbObj)## S4 method for signature 'MySQLConnection'dbIsValid(dbObj)## S4 method for signature 'MySQLResult'dbIsValid(dbObj)Arguments
dbObj,obj | A |
Details
dbObjects are R/S-Plus remote references to foreign objects. Thisintroduces differences to the object's semantics such as persistence (e.g.,connections may be closed unexpectedly), thus this function provides aminimal verification to ensure that the foreign object being referenced canbe contacted.
Value
a logical scalar.
Examples
dbIsValid(MySQL())Make R/S-Plus identifiers into legal SQL identifiers
Description
These methods are straight-forward implementations of the correspondinggeneric functions.
Usage
## S4 method for signature 'MySQLConnection,character'make.db.names( dbObj, snames, keywords = .SQL92Keywords, unique = TRUE, allow.keywords = TRUE, ...)## S4 method for signature 'MySQLConnection'SQLKeywords(dbObj, ...)## S4 method for signature 'MySQLConnection,character'isSQLKeyword( dbObj, name, keywords = .MySQLKeywords, case = c("lower", "upper", "any")[3], ...)Arguments
dbObj | any MySQL object (e.g., |
snames | a character vector of R/S-Plusidentifiers (symbols) from which we need to make SQL identifiers. |
keywords | a character vector with SQL keywords, by default it is |
unique | logical describing whether the resulting set of SQL namesshould be unique. Its default is |
allow.keywords | logical describing whether SQL keywords should beallowed in the resulting set of SQL names. Its default is |
... | Unused, needed for compatibility with generic. |
name | a character vector of SQL identifiers we want to check againstkeywords from the DBMS. |
case | a character string specifying whether to make thecomparison as lower case, upper case, or any of the two. it defaults to |
Build the SQL CREATE TABLE definition as a string
Description
The output SQL statement is a simpleCREATE TABLE with suitable fordbGetQuery
Usage
mysqlBuildTableDefinition( dbObj, name, obj, field.types = NULL, row.names = TRUE, ...)Arguments
dbObj | any DBI object (used only to dispatch according to the engine(e.g., MySQL, Oracle, PostgreSQL, SQLite) |
name | name of the new SQL table |
obj | an R object coerceable to data.frame for which we want to createa table |
field.types | optional named list of the types for each field in |
row.names | logical, should row.name of |
... | reserved for future use |
Value
An SQL string
MySQL Check for Compiled Versus Loaded Client Library Versions
Description
This function prints out the compiled and loaded client library versions.
Usage
mysqlClientLibraryVersions()Value
A named integer vector of length two, the first element representingthe compiled library version and the second element representint the loadedclient library version.
Examples
mysqlClientLibraryVersions()Check if default database is available.
Description
RMySQL examples and tests connect to a database defined by thers-dbi group in~/.my.cnf. This function checks if thatdatabase is available, and if not, displays an informative message.
Usage
mysqlHasDefault()Examples
if (mysqlHasDefault()) { db <- dbConnect(RMySQL::MySQL(), dbname = "test") dbListTables(db) dbDisconnect(db)}Database interface meta-data.
Description
See documentation of generics for more details.
Usage
## S4 method for signature 'MySQLResult'dbColumnInfo(res, ...)## S4 method for signature 'MySQLResult'dbGetRowsAffected(res, ...)## S4 method for signature 'MySQLResult'dbGetRowCount(res, ...)## S4 method for signature 'MySQLResult'dbHasCompleted(res, ...)## S4 method for signature 'MySQLResult'dbGetException(conn, ...)## S4 method for signature 'MySQLResult'summary(object, verbose = FALSE, ...)## S4 method for signature 'MySQLResult'show(object)Arguments
res,conn,object | An object of class |
... | Ignored. Needed for compatibility with generic |
verbose | If |
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")dbWriteTable(con, "t1", datasets::USArrests, overwrite = TRUE)rs <- dbSendQuery(con, "SELECT * FROM t1 WHERE UrbanPop >= 80")dbGetStatement(rs)dbHasCompleted(rs)dbGetInfo(rs)dbColumnInfo(rs)dbClearResult(rs)dbRemoveTable(con, "t1")dbDisconnect(con)}DBMS Transaction Management
Description
Commits or roll backs the current transaction in an MySQL connection.Note that in MySQL DDL statements (e.g.CREATE TABLE) can notbe rolled back.
Usage
## S4 method for signature 'MySQLConnection'dbCommit(conn, ...)## S4 method for signature 'MySQLConnection'dbBegin(conn, ...)## S4 method for signature 'MySQLConnection'dbRollback(conn, ...)Arguments
conn | a |
... | Unused. |
Examples
if (mysqlHasDefault()) {con <- dbConnect(RMySQL::MySQL(), dbname = "test")df <- data.frame(id = 1:5)dbWriteTable(con, "df", df)dbBegin(con)dbGetQuery(con, "UPDATE df SET id = id * 10")dbGetQuery(con, "SELECT id FROM df")dbRollback(con)dbGetQuery(con, "SELECT id FROM df")dbRemoveTable(con, "df")dbDisconnect(con)}