RMySQL: query – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

query

Execute a SQL statement on a database connection.

Description

To retrieve results a chunk at a time, use dbSendQuery, dbFetch, then dbClearResult. Alternatively, if you want all the results (and they'll fit in memory) use dbGetQuery 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 `MySQLResult` object.
`n`	maximum number of records to retrieve per fetch. Use `-1` to retrieve all pending records; use `0` for to fetch the default number of rows as defined in `MySQL`
`...`	Unused. Needed for compatibility with generic.
`conn`	an `MySQLConnection` object.
`statement`	a character vector of length one specifying the SQL statement that should be executed. Only a single SQL statment should be provided.
`what`	optional
`name`	Table name.

Details

fetch() will be deprecated in the near future; please use dbFetch() instead.

Examples

if (mysqlHasDefault()) {
con <- dbConnect(RMySQL::MySQL(), dbname = "test")
dbWriteTable(con, "arrests", datasets::USArrests, overwrite = TRUE)

# Run query to get results as dataframe
dbGetQuery(con, "SELECT * FROM arrests limit 3")

# Send query to pull requests in batches
res <- dbSendQuery(con, "SELECT * FROM arrests")
data <- dbFetch(res, n = 2)
data
dbHasCompleted(res)

dbListResults(con)
dbClearResult(res)
dbRemoveTable(con, "arrests")
dbDisconnect(con)
}

RMySQL

Database Interface and 'MySQL' Driver for R

v0.10.21

GPL-2

Authors

Jeroen Ooms [aut, cre] (<https://orcid.org/0000-0002-4035-0289>), David James [aut], Saikat DebRoy [aut], Hadley Wickham [aut], Jeffrey Horner [aut], RStudio [cph]

Initial release