RMySQL-package {RMySQL}

Overview

A typical usage of the R-MySQL interface is:

1. Connect and authenticate to one or more MySQL databases:

    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    

    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)    

Data mappings between MySQL and R

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.

RDBMS tables, data.frames, and data types

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.)

User authentication

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").

References

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.