Data

Tools

Indexes

Applications

Experiments

Open Source Science

r-odbc

Connect to ODBC databases (using the DBI interface)

https://github.com/r-dbi/odbc

Science Score: 36.0%

This score indicates how likely this project is to be science-related based on various indicators:

  • ○
    CITATION.cff file
  • ✓
    codemeta.json file
    Found codemeta.json file
  • ✓
    .zenodo.json file
    Found .zenodo.json file
  • ○
    DOI references
  • ○
    Academic publication links
  • ✓
    Committers with academic emails
    1 of 44 committers (2.3%) from academic institutions
  • ○
    Institutional organization owner
  • ○
    JOSS paper metadata
  • ○
    Scientific vocabulary similarity
    Low similarity (18.1%) to scientific vocabulary

Keywords

database odbc r

Keywords from Contributors

grammar data-manipulation rmarkdown pandoc literate-programming package-creation date-time travis-ci coverage-report coverage
Last synced: 6 months ago · JSON representation

Repository

Connect to ODBC databases (using the DBI interface)

Basic Info
  • Host: GitHub
  • Owner: r-dbi
  • License: other
  • Language: C++
  • Default Branch: main
  • Homepage: https://odbc.r-dbi.org/
  • Size: 16.4 MB
Statistics
  • Stars: 402
  • Watchers: 29
  • Forks: 111
  • Open Issues: 67
  • Releases: 20
Topics
database odbc r
Created over 9 years ago · Last pushed 6 months ago
Metadata Files
Readme Changelog License Code of conduct

README.Rmd 

---
output: github_document
---



```{r, echo = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-",
  eval = as.logical(Sys.getenv("ODBC_EVAL_README", "false"))
)
```

# odbc odbc website

[![Project Status: Active – The project has reached a stable, usable state and is being actively developed.](http://www.repostatus.org/badges/latest/active.svg)](https://www.repostatus.org/)
[![CRAN_Status_Badge](http://www.r-pkg.org/badges/version/odbc)](https://cran.r-project.org/package=odbc)
[![R-CMD-check](https://github.com/r-dbi/odbc/actions/workflows/R-CMD-check.yaml/badge.svg)](https://github.com/r-dbi/odbc/actions/workflows/R-CMD-check.yaml)
[![Codecov test coverage](https://codecov.io/gh/r-dbi/odbc/branch/main/graph/badge.svg)](https://app.codecov.io/gh/r-dbi/odbc?branch=main)


The goal of the odbc package is to provide a [DBI](https://dbi.r-dbi.org/)-compliant interface to [ODBC](https://learn.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc) drivers. This makes it easy to connect databases such as [SQL Server](https://www.microsoft.com/en-us/sql-server/), Oracle, [Databricks](https://www.databricks.com/), and Snowflake.

The odbc package is an alternative to
[RODBC](https://cran.r-project.org/package=RODBC) and [RODBCDBI](https://cran.r-project.org/package=RODBCDBI) packages, and is typically much faster. See `vignette("benchmarks")` to learn more.

## Overview

The odbc package is one piece of the R interface to databases with support for ODBC:

```{r whole-game, eval = TRUE, echo = FALSE}
#| fig-alt: > 
#|   A diagram containing four boxes with arrows linking each pointing left to 
#|   right. The boxes read, in order, "R interface," "driver manager," 
#|   "ODBC driver," and "DBMS." The left-most box, R interface, contains 
#|   three smaller components, labeled "dbplyr," "DBI," and "odbc."
knitr::include_graphics("man/figures/whole-game.png")
```

Support for a given DBMS is provided by an **ODBC driver**, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit customer, using the [professional drivers](https://docs.posit.co/pro-drivers/).

Drivers are managed by a **driver manager**, which is responsible for configuring driver locations, and optionally named **data sources** that describe how to connect to a specific database. Windows is bundled with a driver manager, while MacOS and Linux require installation of [unixODBC](https://www.unixodbc.org/). Drivers often require some manual configuration; see `vignette("setup")` for details.

In the **R interface**, the [DBI package](https://dbi.r-dbi.org/) provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the
[nanodbc](https://nanodbc.github.io/nanodbc/) C++ library. To interface with DBMSs using R and odbc:

```{r r-interface, eval = TRUE, echo = FALSE}
#| fig-alt: >
#|   A high-level workflow for using the R interface in 3 steps. 
#|   In step 1, configure drivers and data sources, the functions 
#|   odbcListDrivers() and odbcListDataSources() help to interface with the 
#|   driver manager. In step 2, the dbConnect() function, called with the 
#|   first argument odbc(), connects to a database using the specified ODBC 
#|   driver to create a connection object "con." Finally, in step 3, that 
#|   connection object can be passed to various functions to retrieve 
#|   information on database structure, iteratively develop queries, and 
#|   query data objects.
knitr::include_graphics("man/figures/r-interface.png")
```

You might also use the [dbplyr package](https://dbplyr.tidyverse.org/) to automatically generate SQL from your dplyr code.

## Installation 

Install the latest release of odbc from CRAN with the following code:

```r
install.packages("odbc")
```

To get a bug fix or to use a feature from the development version, you can install the development version of odbc from GitHub:

```r
# install.packages("pak")
pak::pak("r-dbi/odbc")
```

## Usage

To use odbc, begin by creating a database connection, which might look something like this:

```{r}
#| eval: false
library(DBI)

con <- dbConnect(
  odbc::odbc(),
  driver = "SQL Server",
  server = "my-server",
  database = "my-database",
  uid = "my-username",
  pwd = rstudioapi::askForPassword("Database password")
)
```

(See `vignette("setup")` for examples of connecting to a variety of databases.)

`dbListTables()` is used for listing all existing tables in a database.

```{r}
#| eval: false
dbListTables(con)
```

`dbReadTable()` will read a full table into an R `data.frame()`.

```{r}
#| eval: false
data <- dbReadTable(con, "flights")
```

`dbWriteTable()` will write an R `data.frame()` to an SQL table.

```{r}
#| eval: false
dbWriteTable(con, "iris", iris)
```

`dbGetQuery()` will submit a SQL query and fetch the results:

```{R}
#| eval: false
df <- dbGetQuery(
  con,
  "SELECT flight, tailnum, origin FROM flights ORDER BY origin"
)
```

It is also possible to submit the query and fetch separately with `dbSendQuery()` and `dbFetch()`. This allows you to use the `n` argument to `dbFetch()` to iterate over results that would otherwise be too large to fit in memory.

Owner

  • Name: r-dbi
  • Login: r-dbi
  • Kind: organization

R + databases

GitHub Events

Total
  • Create event: 15
  • Release event: 2
  • Issues event: 59
  • Watch event: 15
  • Delete event: 9
  • Issue comment event: 126
  • Push event: 114
  • Pull request event: 76
  • Pull request review event: 73
  • Pull request review comment event: 51
  • Fork event: 6
Last Year
  • Create event: 15
  • Release event: 2
  • Issues event: 59
  • Watch event: 15
  • Delete event: 9
  • Issue comment event: 126
  • Push event: 114
  • Pull request event: 76
  • Pull request review event: 73
  • Pull request review comment event: 51
  • Fork event: 6

Committers

Last synced: 8 months ago

All Time
  • Total Commits: 1,275
  • Total Committers: 44
  • Avg Commits per committer: 28.977
  • Development Distribution Score (DDS): 0.379
Past Year
  • Commits: 56
  • Committers: 5
  • Avg Commits per committer: 11.2
  • Development Distribution Score (DDS): 0.5
Top Committers
Name Email Commits
Jim Hester j****r@g****m 792
detule o****i@g****m 89
Hadley Wickham h****m@g****m 74
Simon P. Couch s****h@g****m 67
Bradley White b****w@a****g 67
Greg Miller j****m@g****m 32
Kirill Müller k****r@m****g 29
Greg Miller g****b@m****c 22
Jonathan McPherson j****n@r****m 20
Aaron Jacobs a****s@r****m 10
Edgar Ruiz e****z 7
Xianying Tan s****n@1****m 7
Greg Miller j****m@j****m 6
James Blair b****9 5
Javier Luraschi j****i@h****m 4
Jeroen Ooms j****s@g****m 4
Bruno Tremblay b****y@l****m 3
Gábor Csárdi c****r@g****m 3
Will Beasley w****y@h****m 3
khotilov k****h@g****m 3
Bruno Tremblay b****o@b****a 2
Robert r****g@g****m 2
vkapartzianis v****s@g****m 2
hoxo-m h****e@g****m 2
sharon wang s****g 1
Václav Hausenblas v****s@s****z 1
Steve Condylios s****s@g****m 1
Bill Evans B****s@A****m 1
Kyle G. Lundstedt k****e@i****m 1
Ruiyi Zhang r****g@1****m 1
and 14 more...
Committer Domains (Top 20 + Academic)
google.com: 2 rstudio.com: 2 acm.org: 1 mailbox.org: 1 mllr.cc: 1 126.com: 1 jgm-macbookpro2.roam.corp.google.com: 1 lacapitale.com: 1 boostao.ca: 1 seznam.cz: 1 activedecisionsupport.com: 1 industryvault.com: 1 1607capital.com: 1 sydney.edu.au: 1 ohdsi.org: 1 yahoo.de: 1 altfeld-im.de: 1 kowarik.net: 1

Issues and Pull Requests

Last synced: 6 months ago

All Time
  • Total issues: 280
  • Total pull requests: 296
  • Average time to close issues: 10 months
  • Average time to close pull requests: 25 days
  • Total issue authors: 152
  • Total pull request authors: 17
  • Average comments per issue: 2.96
  • Average comments per pull request: 1.11
  • Merged pull requests: 237
  • Bot issues: 0
  • Bot pull requests: 0
Past Year
  • Issues: 43
  • Pull requests: 88
  • Average time to close issues: 26 days
  • Average time to close pull requests: 11 days
  • Issue authors: 31
  • Pull request authors: 6
  • Average comments per issue: 1.26
  • Average comments per pull request: 0.59
  • Merged pull requests: 61
  • Bot issues: 0
  • Bot pull requests: 0
View more stats
Top Authors
Issue Authors
  • simonpcouch (33)
  • hadley (28)
  • ThomasSoeiro (12)
  • detule (11)
  • blairj09 (9)
  • krlmlr (7)
  • atheriel (5)
  • nwstephens (4)
  • ghost (4)
  • edgararuiz (4)
  • vivas89 (3)
  • apalacio9502 (3)
  • nattomi (3)
  • daniepi (3)
  • metanoid (2)
Pull Request Authors
  • simonpcouch (119)
  • detule (112)
  • hadley (35)
  • atheriel (10)
  • blairj09 (3)
  • stevecondylios (2)
  • emgns04 (2)
  • gaborcsardi (2)
  • meztez (2)
  • But2ene (2)
  • krlmlr (1)
  • jrnold (1)
  • tnederlof (1)
  • Odraio (1)
  • fh-afrachioni (1)
Top Labels
Issue Labels
bug (59) mssql (26) feature (22) documentation (16) upkeep (16) encoding 🔤 (11) snowflake (9) oracle (9) connection-pane :window: (8) teradata (7) reprex (5) azure (4) hive (4) postgres (4) performance (3) redshift (3) db2 (3) databricks (3) netezza (2) impala (2) mysql (2) informix (1) drill (1) msaccess (1) athena (1)
Pull Request Labels
snowflake (6)

Packages

  • Total packages: 2
  • Total downloads: unknown
  • Total dependent packages: 1
    (may contain duplicates)
  • Total dependent repositories: 10
    (may contain duplicates)
  • Total versions: 58
proxy.golang.org: github.com/r-dbi/odbc
  • Versions: 49
  • Dependent Packages: 0
  • Dependent Repositories: 0
Rankings
Dependent packages count: 7.0%
Average: 8.2%
Dependent repos count: 9.3%
Last synced: 6 months ago
conda-forge.org: r-odbc
  • Versions: 9
  • Dependent Packages: 1
  • Dependent Repositories: 10
Rankings
Dependent repos count: 11.1%
Forks count: 18.3%
Average: 19.9%
Stargazers count: 21.2%
Dependent packages count: 29.0%
Last synced: 6 months ago

Dependencies

.github/workflows/R-CMD-check.yaml actions
  • actions/checkout v2 composite
  • r-lib/actions/check-r-package v2 composite
  • r-lib/actions/setup-pandoc v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
.github/workflows/db.yaml actions
  • actions/checkout v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
  • mcr.microsoft.com/mssql/server 2017-latest-ubuntu docker
  • postgres * docker
.github/workflows/pkgdown.yaml actions
  • JamesIves/github-pages-deploy-action 4.1.4 composite
  • actions/checkout v2 composite
  • r-lib/actions/setup-pandoc v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
  • r-lib/actions/setup-tinytex v2 composite
.github/workflows/pr-commands.yaml actions
  • actions/checkout v2 composite
  • r-lib/actions/pr-fetch v2 composite
  • r-lib/actions/pr-push v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
.github/workflows/test-coverage.yaml actions
  • actions/checkout v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
docker/Dockerfile docker
  • rocker/r-devel-san latest build
DESCRIPTION cran
  • R >= 3.2.0 depends
  • DBI >= 1.0.0 imports
  • Rcpp >= 0.12.11 imports
  • bit64 * imports
  • blob >= 1.2.0 imports
  • hms * imports
  • methods * imports
  • rlang * imports
  • DBItest * suggests
  • RSQLite * suggests
  • covr * suggests
  • magrittr * suggests
  • testthat * suggests
  • tibble * suggests
.github/workflows/db-windows.yml actions
  • actions/checkout v2 composite
  • ankane/setup-mysql v1 composite
  • ankane/setup-postgres v1 composite
  • potatoqualitee/mssqlsuite v1.7 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite