Leer bases de datos SQL en R

Manipulaci贸n de datos con R Importar y exportar datos
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:")

Puedes comprobar las conexiones con dbListConnections(dvr). Desafortunadamente, esta es una funci贸n obsoleta y no todos los controladores implementan este m茅todo, por lo que tendr谩s que hacer un seguimiento de tus conexiones por ti mismo.

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)

Puedes obtener m谩s informaci贸n sobre SQL y las consultas SQL en el siguiente sitio web: https://sqlearning.com/

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)