Repository files navigation

The goal of the odbc package is to provide aDBI-compliant interface toODBCdrivers. This makes it easy to connect databases such asSQLServer,Oracle,Databricks, andSnowflake.

The odbc package is an alternative toRODBC andRODBCDBI packages, and istypically much faster. Seevignette("benchmarks") to learn more.

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

Support for a given DBMS is provided by anODBC driver, whichdefines how to interact with that DBMS using the standardized syntax ofODBC and SQL. Drivers can be downloaded from the DBMS vendor or, ifyou’re a Posit customer, using theprofessionaldrivers.

Drivers are managed by adriver manager, which is responsible forconfiguring driver locations, and optionally nameddata sources thatdescribe how to connect to a specific database. Windows is bundled witha driver manager, while MacOS and Linux require installation ofunixODBC. Drivers often require some manualconfiguration; seevignette("setup") for details.

In theR interface, theDBI packageprovides a front-end while odbc implements a back-end to communicatewith the driver manager. The odbc package is built on top of thenanodbc C++ library. To interfacewith DBMSs using R and odbc:

You might also use thedbplyr packageto automatically generate SQL from your dplyr code.

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

install.packages("odbc")

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

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

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

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

(Seevignette("setup") for examples of connecting to a variety ofdatabases.)

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

dbListTables(con)

dbReadTable() will read a full table into an Rdata.frame().

data<- dbReadTable(con,"flights")

dbWriteTable() will write an Rdata.frame() to an SQL table.

dbWriteTable(con,"iris",iris)

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

df<- dbGetQuery(con,"SELECT flight, tailnum, origin FROM flights ORDER BY origin")

It is also possible to submit the query and fetch separately withdbSendQuery() anddbFetch(). This allows you to use then argumenttodbFetch() to iterate over results that would otherwise be too largeto fit in memory.