database - ¿Cómo escribir datos binarios en SQLite con dbWriteTable() de R DBI?

binary r-dbi

Question

Por ejemplo, cómo ejecutar el siguiente SQL equivalente (que se inserta en un campo BINARY(16) )

INSERT INTO Table1 (MD5) VALUES (X''6717f2823d3202449201145073ab871A''),(X''6717f2823d3202449301145073ab371A'')

usando dbWriteTable() ? Obra

dbWriteTable(db, "Table1", data.frame(MD5 = "X''6717f2823d3202449201145073ab871A''", ...), append = T, row.names = F)

no parece funcionar; escribe los valores como texto.

Al final, voy a tener un gran marco de hashes de datos que quiero escribir, y perfecto para usar dbWriteTable . Pero simplemente no puedo encontrar la forma de INSERT el data.frame en los campos de la base de datos binarios .

Answer 1

Entonces aquí hay dos posibilidades que parecen funcionar. El primero usa dbSendQuery(...) en un bucle (probablemente ya hayas pensado en esto ...).

db.WriteTable = function(con,table,df) {  # no error checking whatsoever...
  require(DBI)
  field <- colnames(df)[1] 
  for (i in 1:nrow(df)) {
    query <- sprintf("INSERT INTO %s (%s) VALUES (X''%s'')",table,field,df[i,1])
    rs    <- dbSendQuery(con,statement=query)
  }
  return(nrow(df))
}

library(DBI)
drv <- dbDriver("SQLite")
con <- dbConnect(drv)
rs  <- dbSendQuery(con, statement="CREATE TABLE hash (MD5 BLOB)")

df  <- data.frame(MD5=c("6717f2823d3202449201145073ab871A",
                        "6717f2823d3202449301145073ab371A"))

rs       <- db.WriteTable(con,"hash",df)
result.1 <- dbReadTable(con,"hash")
result.1
#                                                              MD5
# 1 67, 17, f2, 82, 3d, 32, 02, 44, 92, 01, 14, 50, 73, ab, 87, 1a
# 2 67, 17, f2, 82, 3d, 32, 02, 44, 93, 01, 14, 50, 73, ab, 37, 1a

Si su marco de datos de hashes es muy grande, entonces df.WriteFast(...) hace lo mismo que db.WriteTable(...) solo debería ser más rápido.

db.WriteFast = function(con.table,df) {
  require(DBI)
  field <- colnames(df)[1]
  lapply(unlist(df[,1]),function(x){
         dbSendQuery(con,
                     statement=sprintf("INSERT INTO %s (%s) VALUES (X''%s'')",
                                        table,field,x))})
}

Tenga en cuenta que result.1 es un marco de datos, y si lo usamos en una llamada a dbWriteTable(...) podemos escribir los hash con éxito en un BLOB. Entonces es posible.

str(result.1)
# ''data.frame'': 2 obs. of  1 variable:
#  $ MD5:List of 2
#   ..$ : raw  67 17 f2 82 ...
#   ..$ : raw  67 17 f2 82 ...

El segundo enfoque aprovecha el tipo de datos en raw de R para crear una trama de datos estructurada como result.1 , y la pasa a dbWriteTable(...) . Pensarías que esto sería fácil, pero no.

h2r = function(x) {
  bytes <- substring(x, seq(1, nchar(x)-1, 2), seq(2, nchar(x), 2))
  return(list(as.raw(as.hexmode(bytes))))
}
hash2raw = Vectorize(h2r)

df.raw=data.frame(MD5=list(1:nrow(df)))
colnames(df.raw)="MD5"
df.raw$MD5 = unname(hash2raw(as.character(df$MD5)))
dbWriteTable(con, "newHash",df.raw)
result.2 <- dbReadTable(con,"newHash")
result.2

all.equal(result.1$MD5,result.2$MD5)
# [1] TRUE

En este enfoque, creamos un marco de datos df.raw que tiene una columna, MD5 , en la que cada elemento es una lista de bytes sin formato. La función de utilidad h2r(...) toma una representación de caracteres del hash, lo divide en un vector de char(2) (los bytes), luego interpreta cada uno de ellos como hex ( as.hexmode(...) ), convierte el resultado a raw ( as.raw(...) ) y finalmente devuelve el resultado como una lista. Vectorize(...) es un contenedor que permite que hash2raw(...) tome como argumento un vector.

Personalmente, creo que es mejor usar el primer enfoque: aprovecha el mecanismo interno de SQLite para escribir hexadecimal en BLOB, y es mucho más fácil de entender.