I have a piece of code that creates an SQL query from the columns of a dataframe row :

a <- c("a1")
b <- c("b1")
c <- c("c1")
df <- data.frame(a, b, c)

query = "INSERT INTO table (a, b, c) VALUES ("

for (j in 1:ncol(df)) {
  if (j < ncol(df)) {
    query <- paste0(query, df[1, j], ", ")  
  } else {
    query <- paste0(query, df[1, j], ");")
  }
}

The point is I have to insert a comma between the elements, but no comma after the last element so that the query works.

Here is what I want to get :

query = "INSERT INTO table (a, b, c) VALUES ("a1", "b1", "c1");"

Do you have an idea of a simpler way to write it ?

Thank you.

CodePudding user response：

We assume:

1. a comma is wanted between elements even though the question refers to a colon
2. df_layer and i are used in the question but not defined. We assume that the output shown is what is wanted, that df has one row as in the question and that i and df_layer can be disregarded.

1) Use sprintf, shQuote and toString. shQuote(df, "cmd") can be optionally shortened to just shQuote(df) on Windows.

s <- sprintf('INSERT INTO table (a, b, c) VALUES (%s);', 
  toString(shQuote(df, "cmd")))
cat(s, "\n")
## INSERT INTO table (a, b, c) VALUES ("a1", "b1", "c1"); 

2) or possibly this variation to also insert the column names

s2 <- sprintf('INSERT INTO table (%s) VALUES (%s);', 
  toString(names(df)), toString(shQuote(df, "cmd")))
cat(s2, "\n")
## INSERT INTO table (a, b, c) VALUES ("a1", "b1", "c1"); 

Note

Input is

df <- data.frame(a = "a1", b = "b1", c = "c1")

CodePudding user response：

 paste0(
      'INSERT INTO TABLE (a, b, c) VALUES ("', 
      paste(df$a, df$b, df$c, sep = '", "'), 
      '")'
    )

CodePudding user response：

I think just using the paste fonction can work well in your case i used the collapse parameters to specify the delimiters and just added with another parse the end of the string i just changed the string delimiters to enable the use of " inside of the string.

b <- c("b1")
c <- c("c1")
df <- data.frame(a, b, c)
i <- 1
query = 'INSERT INTO table (a, b, c) VALUES ("'


df
paste0(query,paste(df[i,], collapse = '", "'),'");')

if you want you could do the same for the column names

b <- c("b1")
c <- c("c1")
df <- data.frame(a, b, c)
i <- 1
query = 'INSERT INTO table ('
query2 = ') VALUES ("'

colnames(df)
paste0(query,paste(colnames(df),collapse=", "),query2,paste(df[i,], collapse = '", "'),'");')

CodePudding user response：

You can just join all values like this:

paste(df[1,], collapse = '", "')

in your example it would look like this:

a <- c("a1")
b <- c("b1")
c <- c("c1")
df <- data.frame(a, b, c)

query = "INSERT INTO table (a, b, c) VALUES ("
query <- paste0(query, paste(df[1,], collapse = '", "'), ");")

CodePudding user response：

I like to use the glue package, though I highly, highly recommend using the glue_data_safe() function for anything outward facing that takes user input.

df = data.frame(a = "a1", b = "b1", c = "c1")
table = "(a, b, c)"
glue::glue("insert into {table} values ({paste(df[1,], collapse = ',')})")

> "insert into (abc) values (a1,b1,c1)"