1

Gracias Patricio Moracho por tu observación. Replanteo la pregunta. Estoy trabajando con R Studio y MySQL. A través de R estoy cargando y haciendo modificaciones en las tablas de MySQL . Adjunto un ejemplo Creo la tabla en MySQL, ajunto script (uso una tabla de ejemplo que incluye R)

CREATE TABLE `mtcars` (
  `row_names` text,
  `mpg` double DEFAULT NULL,
  `cyl` double DEFAULT NULL,
  `disp` double DEFAULT NULL,
  `hp` double DEFAULT NULL,
  `drat` double DEFAULT NULL,
  `wt` double DEFAULT NULL,
  `qsec` double DEFAULT NULL,
  `vs` double DEFAULT NULL,
  `am` double DEFAULT NULL,
  `gear` double DEFAULT NULL,
  `carb` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Una vez conectado a esta base de datos con R, base que llamé "prueba", cargo la tabla con el siguiente comando.

dbWriteTable(conn = mydb, name = "mtcars", mtcars, overwrite = TRUE, row.names = TRUE)
  • mtcars es una tabla de ejemplo que tiene cargada R.

Por otro lado, sé que puedo hacer un Update de uno o más datos con el siguiente comando. En este ejempolo modifico el valor de la columna 'mpg' donde row.names = 'Mazda Rx4'; así tenemos:

dbSendQuery(mydb, "UPDATE prueba.mtcars SET mpg = 99 WHERE row_names = 'Mazda Rx4';")

De esta forma el Update se da correctamente, pero necesito que en lugar de ingresar el monto directamente, necesito utilizar una variable que pueda tomar cualquier valor, y modificar varios campos de la tabla simultaneamente.

Si creo el elemento llamado 'monto' y le asigno un valor, digamos el 99 del ejemplo anterior, como sigue.

monto <- 99 ## en R

Y sustituyo el 99 por 'valor'

dbSendQuery(mydb, "UPDATE prueba.mtcars SET mpg = monto WHERE row_names = 'Mazda Rx4';")

Lo anterior me genera el siguiente error.

Error in .local(conn, statement, ...) : 
  could not run statement: Unknown column 'valor' in 'field list'

Lo que provoca que el comando no sea dinámico, ya que este elemento llamado 'monto' podría tomar cualquier valor.

No sé si existe algún comando en R que permita hacer UPDATE de bloque de datos. O qué alternativa me recomiendan.

Gracias.

Mario Martínez
  • 13
  • 5
  • Es muy importante que leas [ask] para poder mejorar tu pregunta y que esta sea bien recibida por la comunidad, ya tiene algunos días y parece que nadie se ha interesado. Te hago algunos comentarios para ver si podemos revertir esta situación. (a) Tomate un poco de tiempo para formatear el código de tu pregunta para hacerla más clara y atractiva. (b) Te sugiero que agregues la etiqueta `R` va a tener mayor visibilidad. – Patricio Moracho Oct 09 '18 at 21:02
  • (c) Plantea la pregunta desde el problema inicial, ahora esta más enfocada en la solución que ya tienes. Suerte. – Patricio Moracho Oct 09 '18 at 21:05

2 Answers2

1

Lo que te falta saber, es como conectar las variables de R con las sentencias SQL. Tienes básicamente dos formas de hacerlo.

1. Crear la sentencia dinámica completa

monto <- 99
modelo <- 'Mazda RX4'
SQL <- paste0("UPDATE prueba.mtcars SET mpg = ", monto, " WHERE row_names = '", modelo, "';")
SQL

Aquí lo que estamos haciendo es, mediante paste0(), crear dinámicamente la sentencia SQL de forma completa, concatenando cada parte de la sentencia con cada variable. Lo único que restaría es ejecutarla con dbSendQuery(mydb, SQL). Es una forma sencilla y cómoda, el principal beneficio es que podríamos verificar la sentencia antes de ejecutarla, sin embargo este método tiene algunos problemas:

  • No aprovecha las eventuales optimizaciones que pueda hacer el motor de base de datos, cuando repites la misma sentencia varias veces, cada ejecución es una nueva consulta para el motor.
  • Es susceptible de sufrir un ataque de inyección SQL, si no controlas el valor de las variables con las que construyes la sentencia final, nada impide que se puedan manipular para terminar generando una sentencia SQL peligrosa.

2. Comunicar las variables con la sentencia SQL mediante dbBind()

Esta es la forma óptima si apuntas a una solución que va más allá de una herramienta personal. La idea es que creas la sentencia e indicas con ciertas palabras clave los lugares dónde se espera una variable. Hay dos formas de hacer esto, de forma posicional o por nombre:

a. De forma posicional

monto <- 99
modelo <- 'Mazda RX4'

smt <- dbSendStatement(mydb, "UPDATE mtcars SET mpg = ? WHERE row_names = ?;")
dbBind(smt, list(monto, modelo))

Por empezar, en el caso de sentencias de actualización que no nos devuelven registros, si bien es posible usar dbSendQuery(), es más lógico hacer uso de dbSendStatement(), más allá de esto, lo que hacemos es crear una sentencia dónde indicamos ? para el lugar dónde irían nuestras variables y luego usamos dbBind() para conectar dicha sentencia con las variables en cuestión, en el mismo orden de aparición, pasando estas variables como una lista.

b. Por nombre

Mucho más explícito es hacer esta conexión entre sentencias y variables mediante una lista con nombres:

smt <- dbSendStatement(mydb, "UPDATE mtcars SET mpg = $monto WHERE row_names = $modelo;")
dbBind(smt, list(monto=monto, modelo=modelo))

La idea es simple, indicamos cada variable en la sentencia, con un nombre determinado y anteponemos $, luego la lista deberemos crearla e indicar los mismo nombres para cada elemento.

Por último, ¿Cómo podemos crear de forma óptima un conjunto de sentencias de actualización?

# Creamos una lista con los modelos y valores a actualizar
valores <- list(modelo = c('Mazda RX4', 'Ferrari Dino', 'Honda Civic'),
                monto = c(10, 12, 15)
)

# Consultamos los valores iniciales
smt <- dbSendQuery(mydb, "SELECT * FROM mtcars WHERE row_names=$modelo")
dbBind(smt, valores[1])
dbFetch(smt)
dbClearResult(smt)


# Actualizamos los valores
smt <- dbSendStatement(mydb, "UPDATE mtcars SET mpg = $monto WHERE row_names = $modelo;")
dbBind(smt, valores)
dbFetch(smt)
dbClearResult(smt)

# Consultamos y verificamos los cambios
smt <- dbSendQuery(mydb, "SELECT * FROM mtcars WHERE row_names=$modelo")
dbBind(smt, valores[1])
dbFetch(smt)
dbClearResult(smt)
RolandoAmir
  • 5
  • 2
Patricio Moracho
  • 54,367
  • 12
  • 35
  • 68
  • Nuevamente Gracias Patricio. – Mario Martínez Oct 14 '18 at 21:45
  • Nuevamente Gracias Patricio, ya hice la prueba con la primera opción, y perfecto, y estoy intentando con la segunda forma de hacerlo, pero al ejecutar el script que incluyes me genera un error de sintaxis, es este Error in .local(conn, statement, ...) : could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WHERE row_names = ?' at line 1 No sé si es por que uso la versión gratuita de R o debo correr algún paquete (librería). – Mario Martínez Oct 14 '18 at 21:51
  • El error hace referencia que su sentencia sql no está bien escrita (puede ser porque no escribiste bien algún atributo, el nombre de la tabla no es correcta, entre otros) – Edgar Gc Jul 08 '20 at 22:08
0

Por alguna razón no me funcionó con DBI, encontré una alternativa, por lo que les comparto: utilizando el package dbx (que está construido encima de DBI, por lo que es compatible), lo bueno es que tiene funciones especificas para realizar Updates:

library(dbx)
records <- data.frame(row_names = c('Mazda RX4', 'Ferrari Dino', 'Honda Civic'),
                      mpg = c(10,12,15))
dbxUpdate(mydb, "mtcars", records, where_cols=c("row_names"))

Personalmente me parece más consistente porque los nombres de las columnas de los nuevos datos a actualizar (records) tienen que ser coherentes con los de la tabla a actualizar en el base de datos, además en una sola línea.

Adicionalmente dbx tiene funciones para realizar Upserts (actualizar o insertar si no existe), por si alguien lo necesita.

RolandoAmir
  • 5
  • 2