RMySQL-package {RMySQL} | R Documentation |
The functions in this package allow you interact with one or more MySQL databases from R.
A typical usage of the R-MySQL interface is:
con <- dbConnect(MySQL(), group = "lasers") con2 <- dbConnect(MySQL(), user="opto", password="pure-light", dbname="lasers", host="merced")
dbListTables(con) dbListFields(con, "table\_name")
d <- dbReadTable(con, "WL") dbWriteTable(con, "WL2", a.data.frame) ## table from a data.frame dbWriteTable(con, "test2", "~/data/test2.csv") ## table from a file
dbGetQuery(con, "select count(*) from a\_table") dbGetQuery(con, "select * from a\_table")
rs <- dbSendQuery(con, "select * from WL where width\_nm between 0.5 and 1") d1 <- fetch(rs, n = 10000) d2 <- fetch(rs, n = -1
client.flag
value in the dbConnect
call):
con <- dbConnection(MySQL(), dbname = "rs-dbi", client.flag = CLIENT\_MULTI\_STATEMENTS) script <- paste("select * from WL where width\_nm between 0.5 and 1" "select * from lasers\_id where id LIKE 'AL100 sep = ";") rs1 <- dbSendQuery(con, script) d1 <- fetch(rs1, n = -1) if(dbMoreResults(con)){ rs2 <- dbNextResult(con) d2 <- fetch(rs2, n=-1) }
summary(MySQL(), verbose = TRUE) summary(con, verbose = TRUE) summary(rs, verbose = TRUE) dbListConnections(MySQL()) dbListResultSets(con) dbHasCompleted(rs)
dbDisconnect(con) dbDisconnect(con2)
MySQL tables are read into R as data.frames, but without coercing character or logical data into factors. Similarly while exporting data.frames, factors are exported as character vectors.
Integer columns are usually imported as R integer vectors, except
for cases such as BIGINT
or UNSIGNED INTEGER
which are coerced to R's double
precision vectors to avoid
truncation (currently R's integers are signed 32-bit quantities).
Time variables are imported/exported as character data, so you need to convert these to your favorite date/time representation.
Currently there are no facilities to import/export BLOBs
.
Tables in a relational database are only superficially similar to R's data.frames (e.g., tables as unordered sets of rows compared to data.frames as ordered sets, tables having referential constraints, indexes, and so on.)
Although you can specify user authentication parameters
(user, password, database, and host) in
the call to dbConnect
, the preferred method to pass
these parameters to the server is through a MySQL
default.file
, e.g., ‘$HOME/.my.cnf’ (or ‘c:/my.cnf’
under Windows).
The MySQL dbConnect
method parses the
default.file=\$HOME/.my.cnf
to initialize connections to
MySQL databases.
This file consists of zero or more named sections
each starting with a line of the form [section-name]
;
each section includes zero or more MySQL variable declaration per line,
such as, user=
, password=
, host=
, etc.
For instance,
$ cat $HOME/.my.cnf # this is a comment ; this is also a comment [client] user = dj host = localhost [rs-dbi] database = s-data [lasers] user = opto database = opto password = pure-light host = merced ... [iptraffic] host = data database = iptraffic
This file should be readable only by you. RMySQL
always initializes connection values from the [client]
and
[rs-dbi]
sections, but you may define you own project-specific
sections (as in the example above) to tailor its environment;
if the same parameter appears in multiple sections (e.g., in client
and rs-dbi
), the last (closer to the bottom) occurrence is used.
If you define a section, for instance, [iptraffic]
,
then instead of including all these parameters in the
call to dbConnect
, you simply supply the
name of the group
,
e.g., dbConnect(MySQL(), group = "iptraffic")
.
In addition to user
, password
, host
, and
dbname
, you may specify any other connection parameters,
e.g., port
, socket
. See the MySQL documentation
for details.
Lastly, you may specify an alternate default.file
, e.g.,
dbConnect(MySQL(), group="iptraffic", default.file="router\_shield")
.
See stat.bell-labs.com/RS-DBI for more details on the R/S-Plus database interface.
See the documentation at the MySQL Web site http://www.mysql.com for details.
David A. James <dj@bell-labs.com> Saikat DebRoy <saikat@stat.wisc.edu>
On database managers:
On connections, SQL statements and resultSets:
dbConnect
dbDisconnect
dbSendQuery
dbGetQuery
fetch
dbClearResult
On transaction management:
On meta-data:
summary
dbGetInfo
dbGetDBIVersion
dbListTables
dbListConnections
dbListResults
dbColumnInfo
dbGetException
dbGetStatement
dbHasCompleted
dbGetRowCount
dbGetAffectedRows
## Not run: # create a MySQL instance and create one connection. > m <- dbDriver("MySQL") ## or MySQL() <MySQLDriver:(4378)> # open the connection using user, passsword, etc., as # specified in the "[iptraffic]" section of the # configuration file \file{\$HOME/.my.cnf} > con <- dbConnect(m, group = "iptraffic") > rs <- dbSendQuery(con, "select * from HTTP_ACCESS where IP_ADDRESS = '127.0.0.1'") > df <- fetch(rs, n = 50) > dbHasCompleted(rs) [1] FALSE > df2 <- fetch(rs, n = -1) > dbHasCompleted(rs) [1] TRUE > dbClearResult(rs) > dim(dbGetQuery(con, "show tables")) [1] 74 1 > dbListTables(con) ## End(Not run)