Leer bases de datos SQL en R

El paquete DBI es una interfaz para comunicar R y sistemas de gestión de bases de datos relacionales. Este paquete puede conectar a MySQL, PostgreSQL, SQLite, MariaDB y a otras bases de datos a través de sus propios drivers. En este tutorial aprenderás a conectarte a diferentes bases de datos, a realizar consultas y a agregar y eliminar tablas.

Conexiones a bases de datos SQL

La función dbConnect de DBI crea una conexión a una base de datos a través de un driver y los parámetros de autenticación necesarios, como el nombre de usuario, la contraseña, el host, el puerto y el nombre de la base de datos o del fichero de base de datos. En las siguientes subsecciones repasaremos cómo conectarse a las bases de datos más habituales.

MySQL

Para conectarte a una base de datos MySQL tienes que especificar el controlador MySQL de RMySQL y especificar tus parámetros de autenticación.

library(DBI)

db <- dbConnect(RMySQL::MySQL(),
                user = "tu_nombre_usuario",
                host = "tu_host",
                password = "tu_contraseña",
                dbname = "your_database_name",
                port = "tu puerto")

PostgreSQL

Para conectarte a una base de datos PostgreSQL necesitarás utilizar RPostgreSQL::PostgreSQL() y tus parámetros de autenticación.

library(DBI)

db <- dbConnect(RPostgreSQL::PostgreSQL(),
                user = "tu_nombre_usuario",
                host = "tu_host",
                password = "tu_contraseña",
                dbname = "your_database_name",
                port = "tu puerto")

MariaDB

Una conexión a MariaDB puede hacerse a través del driver RMariaDB::MariaDB(). Puedes conectarte a una base de datos remota con tus credenciales o estableciendo un grupo en tu archivo .my.cnf, mariadb.cnf o my.ini (el archivo de opciones por defecto de MariaDB).

library(DBI)

db <- dbConnect(RMariaDB::MariaDB(), group = "my-db")

# O:
db <- dbConnect(RMariaDB::MariaDB(),
                host = "your-db.your-company.com",
                user = "tu_nombre_usuario",
                password = "tu_contraseña")

SQLite

La última conexión que revisaremos es a SQLite a través de RSQLite::SQLite(). En este escenario sólo necesitas especificar el nombre de tu base de datos.

library(DBI)

db <- dbConnect(RSQLite::SQLite(), "your-db.sqlite")

# Base de datos en memoria
# db <- dbConnect(RSQLite::SQLite(), ":memory:")

Leer tablas

En esta sección vamos a utilizar la siguiente base de datos SQLite de ejemplo llamada db que contiene dos tablas. Puedes utilizar el siguiente código para crearla en tu directorio de trabajo:

# Datos de ejemplo
df <- data.frame(x = 1:26, LETTERS)

db <- dbConnect(RSQLite::SQLite(), dbname = 'sample_db.sqlite')

# Tablas de ejemplo
dbWriteTable(conn = db, name = "tabla_1", value = df)
dbWriteTable(conn = db, name = "tabla_2", value = mtcars)

Lista de tablas disponibles

Puedes listar las tablas disponibles con la función dbListTables.

dbListTables(db)

"tabla_1"  "tabla_2"

Como ya hemos dicho, esta base de datos tiene dos tablas llamadas "tabla_1" y "tabla_2".

Lista de campos disponibles

También puedes listar los campos de cada una de las tablas con dbListFields.

dbListFields(db, "tabla_1")

"x"       "LETTERS"

Por ejemplo, la primera tabla contiene dos campos denominados "x" y "LETRAS".

Leer tablas específicas con dbReadTable

Una vez que conozcas los nombres de las tablas de tus bases de datos puedes leerlas con la función dbReadTable. En el siguiente ejemplo estamos leyendo la tabla llamada "tabla_1".

df <- dbReadTable(db, "tabla_1")

df

    x LETTERS
1   1       A
2   2       B
3   3       C
4   4       D
5   5       E
...

Ejecutar consultas (queries)

Es posible ejecutar consultas SQL a la base de datos con la función dbSendQuery. A continuación, tendrás que utilizar la función dbFetch para obtener los registros deseados de la consulta. Finalmente, necesitarás liberar los recursos con dbClearResult.

En el siguiente ejemplo estamos extrayendo 5 filas del campo llamado x de la tabla llamada tabla_1:

# Selecciona la variable "x" de "tabla_1"
query <- dbSendQuery(db, "SELECT x FROM tabla_1")

# Primeras 5 filas
n5 <- dbFetch(query, n = 5)

n5

# Limpiamos recursos
dbClearResult(query)

Agregar y eliminar tablas

Las funciones dbWriteTable y dbRemoveTable permiten añadir o eliminar tablas de la base de datos. Si quieres añadir una nueva tabla llamada tabla_3 a la base de datos puedes hacer lo siguiente:

df <- iris[1:10, ]

dbWriteTable(db, "tabla_3", df)

Ahora puedes comprobar que la tabla se ha añadido a la base de datos con dbListTables.

dbListTables(db)

"tabla_1"  "tabla_2" "tabla_3"

Por último, considera que quieres eliminar la segunda tabla. Para ello puedes escribir lo siguiente:

dbRemoveTable(db, "tabla_2")

Cerrar una conexión

Cuando termines tu trabajo puedes cerrar la conexión a la base de datos con la función dbDisconnect. Si estabas trabajando con una base de datos temporal, ésta será eliminada.

dbDisconnect(db)

También puedes cerrar todas las conexiones listando todas las conexiones con dbListConnections y cerrando todas a la vez siguiendo el ejemplo de abajo. Esto sólo está disponible para algunos controladores, ya que dbListConnections es una función obsoleta.

# Especifica el driver
dvr <- RMySQL::MySQL()

lapply(dbListConnections(dvr), dbDisconnect)