SQL query contains 'c(' when being generated from rmysql

Question

I am using the library RMySQL to generate an SQL statement

query <- sprintf("foo", paste(mydf, collapse = "', '",sep = ','))

When i have a look at the query it seems to be putting everything in brackets for example a column would consist of monatory terms 10.50, 20.50, 50.33 but when i look at the SQL it has it as c(10.50,20.50, 50.33) which causes my SQL to crash. Does anyone know why?

Reproducible example

mydf <- data.frame(
   X = sample(1:10),
   Y = sample(c("yes", "no"), 10, replace = TRUE)
 )

 # Construct the update query by looping over the data fields
 query <- sprintf("INSERT INTO feedback (X,Y) VALUES ('%s')",
                  paste(mydf, collapse = "', '",sep = ','))


> cat(query)
INSERT INTO feedback (X,Y) VALUES ('c(8, 6, 10, 9, 3, 4, 5, 7, 2, 1)', 'c(1, 2, 1, 2, 2, 1, 1, 1, 2, 2)')

Thanks

Please add a minimal reproducible example. Right now your question depends on the structure of your data but we aren't given that information. Also I'm guessing you modified your sprintf statement but you did so in such a way that it couldn't possibly give the result you say it does since all that would come out would be "foo" possibly replicated a few times. Also note that while your overall goal is to use this with RMySQL that the question itself isn't dependent on that at all and boils down to "how to get the result I want instead of the one I am getting". — Dason, Sep 15 '16 at 13:53
Hi @m0h3n, i have updated the question with an example. Apologies for the delay — John Smith, Sep 15 '16 at 14:20
Do you really want to insert two strings that each contains some numbers? You won't be able to query them without first splitting the strings. Did you want to insert number pairs instead? — Panagiotis Kanavos, Sep 15 '16 at 14:41
so in reality there are 45 columns. My aim is in the end to append the 45 columns of data to a mysql table which also contains 45 columns so in the case above X would be column 1, Y would be column 2 and so on. — John Smith, Sep 15 '16 at 14:42
@JohnSmith 45 columns of what? Storing both text and numbers in the same field is a serious problem. Again, the values will be unusable. Should I guess that the table contains 2 rows only? Perhaps you wanted a text and number column, with 45 rows? Or 90 columns that go in pairs (number, flag) ? Besides, storing text like `yes`/`no` means that you can't perform Boolean operations on them — Panagiotis Kanavos, Sep 15 '16 at 14:45
Sorry, I wasn't being clear. The sample table above that we are trying to add to mysql is a dataframe with 10 rows and 2 columns. So the table in the MYSQL DB is going to have the same number of columns with the variable names X and Y. — John Smith, Sep 15 '16 at 14:56
Simply use `dbWriteTable(..., append=TRUE)` to append dataframe to similarly structured db table. — Parfait, Sep 15 '16 at 15:00
hi @Parfait, Please use this as the answer and i will mark it. out of curiosity is it possible to specify the columns to append to. For example we have an 45 columns i want to append to but one of the columns in the table is set to automatically insert a timestamp with each update. Also does this code allow for concurrent updates? — John Smith, Sep 15 '16 at 15:52

score 1 · Accepted Answer · edited May 23 '17 at 10:29

As mentioned, consider using dbWriteTable() to import a dataframe to an existing MySQL database table. Dataframe columns should match database table's column names and types (or coercible types) or types specified using a named list in its field.types argument:

dbWriteTable(conn, "MyTable", mydf, row.names = FALSE, append = TRUE)

Also, dataframe does not need to be exhaustive to fill every current column in database table, so leave out any automated fields like autoincrement IDs and timestamps.

Consider even using a staging temp table regularly cleaned out which might be needed to avoid duplicates (w/ NOT IN or NOT EXISTS or LEFT JOIN / IS NULL queries) and/or match different named or additional columns in df not in db table:

dbSendQuery(conn, "DELETE FROM MyTempTable")  # OR USE overwrite = TRUE in next line
dbWriteTable(conn, "MyTempTable", mydf, row.names = FALSE, append = TRUE) 
dbSendQuery(conn, "INSERT INTO MyTable (Col1, Col2, Col3, ...)
                   SELECT Col1, Col2, Col3 ... FROM MyTempTable")

SQL query contains 'c(' when being generated from rmysql

Reproducible example

1 Answers1