-
Notifications
You must be signed in to change notification settings - Fork 74
/
Copy pathREADME.Rmd
134 lines (97 loc) · 4.66 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "man/figures/README-",
out.width = "100%"
)
```
# DBI
<!-- badges: start -->
[](https://lifecycle.r-lib.org/articles/stages.html#stable)
[](https://github.com/r-dbi/DBI/actions)
[](https://app.codecov.io/github/r-dbi/DBI?branch=main)
[](https://cran.r-project.org/package=DBI)
[](https://bestpractices.coreinfrastructure.org/projects/1882)
<!-- badges: end -->
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:
- [RPostgres](https://rpostgres.r-dbi.org),
- [RMariaDB](https://rmariadb.r-dbi.org),
- [RSQLite](https://rsqlite.r-dbi.org),
- [odbc](https://github.com/r-dbi/odbc),
- [bigrquery](https://github.com/r-dbi/bigrquery),
and many more, see the [list of backends](https://github.com/r-dbi/backends#readme).
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](https://dbi.perl.org/), Java's JDBC, Python's [DB-API](https://www.python.org/dev/peps/pep-0249/), and Microsoft's [ODBC](https://en.wikipedia.org/wiki/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:
- [RPostgres](https://rpostgres.r-dbi.org) for PostgreSQL,
- [RMariaDB](https://rmariadb.r-dbi.org) for MariaDB or MySQL,
- [RSQLite](https://rsqlite.r-dbi.org) for SQLite,
- [odbc](https://github.com/r-dbi/odbc) for databases that you can access via [ODBC](https://en.wikipedia.org/wiki/Open_Database_Connectivity),
- [bigrquery](https://github.com/r-dbi/bigrquery),
- ... .
You can install the released version of DBI from [CRAN](https://CRAN.R-project.org) with:
``` r
install.packages("DBI")
```
And the development version from [GitHub](https://github.com/) with:
``` r
# install.packages("devtools")
devtools::install_github("r-dbi/DBI")
```
## Example
The following example illustrates some of the DBI capabilities:
```{r example}
library(DBI)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while (!dbHasCompleted(res)) {
chunk <- dbFetch(res, n = 5)
print(nrow(chunk))
}
dbClearResult(res)
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](https://db.rstudio.com/) describes the tools and best practices in this ecosystem.
* The [DBI project site](https://r-dbi.org/) hosts a blog where recent developments are presented.
* [A history of DBI](https://dbi.r-dbi.org/articles/DBI-history.html) 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](https://dbi.r-dbi.org/CODE_OF_CONDUCT.html).
By contributing to this project, you agree to abide by its terms.