R>> DBI>> 返回
项目作者: r-dbi

项目描述 :
A database interface (DBI) definition for communication between R and RDBMSs
高级语言: R
项目地址: git://github.com/r-dbi/DBI.git
创建时间: 2013-10-16T05:17:38Z
项目社区:https://github.com/r-dbi/DBI

开源协议:GNU Lesser General Public License v2.1

下载


DBI

Lifecycle:
stable
rcc
Coverage
Status
CRAN_Status_Badge
CII Best
Practices

The DBI package helps connecting R to database management systems
(DBMS). DBI separates the connectivity to the DBMS into a “front-end”
and a “back-end”. The package defines an interface that is implemented
by DBI backends such as:

and many more, see the list of
backends
. R scripts and
packages use DBI to access various databases through their DBI backends.

The interface defines a small set of classes and methods similar in
spirit to Perl’s DBI, Java’s JDBC, Python’s
DB-API, and Microsoft’s
ODBC. It supports the following
operations:

  • connect/disconnect to the DBMS
  • create and execute statements in the DBMS
  • extract results/output from statements
  • error/exception handling
  • information (meta-data) from database objects
  • transaction management (optional)

Installation

Most users who want to access a database do not need to install DBI
directly. It will be installed automatically when you install one of the
database backends:

You can install the released version of DBI from
CRAN with:

  1. install.packages("DBI")

And the development version from GitHub with:

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

Example

The following example illustrates some of the DBI capabilities:

  1. library(DBI)
  2. # Create an ephemeral in-memory RSQLite database
  3. con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
  4. dbListTables(con)
  5. #> character(0)
  6. dbWriteTable(con, "mtcars", mtcars)
  7. dbListTables(con)
  8. #> [1] "mtcars"
  9. dbListFields(con, "mtcars")
  10. #> [1] "mpg" "cyl" "disp" "hp" "drat" "wt" "qsec" "vs" "am" "gear"
  11. #> [11] "carb"
  12. dbReadTable(con, "mtcars")
  13. #> mpg cyl disp hp drat wt qsec vs am gear carb
  14. #> 1 21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4
  15. #> 2 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
  16. #> 3 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
  17. #> 4 21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1
  18. #> 5 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
  19. #> 6 18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1
  20. #> 7 14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4
  21. #> 8 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
  22. #> 9 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
  23. #> [ reached 'max' / getOption("max.print") -- omitted 23 rows ]
  24. # You can fetch all results:
  25. res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
  26. dbFetch(res)
  27. #> mpg cyl disp hp drat wt qsec vs am gear carb
  28. #> 1 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
  29. #> 2 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
  30. #> 3 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
  31. #> 4 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
  32. #> 5 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
  33. #> 6 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
  34. #> 7 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
  35. #> 8 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
  36. #> 9 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
  37. #> [ reached 'max' / getOption("max.print") -- omitted 2 rows ]
  38. dbClearResult(res)
  39. # Or a chunk at a time
  40. res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
  41. while (!dbHasCompleted(res)) {
  42. chunk <- dbFetch(res, n = 5)
  43. print(nrow(chunk))
  44. }
  45. #> [1] 5
  46. #> [1] 5
  47. #> [1] 1
  48. dbClearResult(res)
  49. dbDisconnect(con)

Class structure

There are four main DBI classes. Three which are each extended by
individual database backends:

  • DBIObject: a common base class for all DBI.

  • DBIDriver: a base class representing overall DBMS properties.
    Typically generator functions instantiate the driver objects like
    RSQLite(), RPostgreSQL(), RMySQL() etc.

  • DBIConnection: represents a connection to a specific database

  • DBIResult: the result of a DBMS query or statement.

All classes are virtual: they cannot be instantiated directly and
instead must be subclassed.

Further Reading

  • Databases using R describes the tools and
    best practices in this ecosystem.

  • The DBI project site hosts a blog where
    recent developments are presented.

  • A history of DBI by
    David James, the driving force behind the development of DBI, and many
    of the packages that implement it.


Please note that the DBI project is released with a Contributor Code
of Conduct
. By contributing
to this project, you agree to abide by its terms.