Add a dynamic value into RMySQL getQuery
One option is to manipulate the SQL string within the loop. At the moment you have a string literal, the 'df[2]'
is not interpreted by R as anything other than characters. There are going to be some ambiguities in my answer, because df
in your Q is patently not a data frame (it is a character vector!). Something like this will do what you want.
Store the output in a numeric vector:
require(RMySQL)
df <- c('a','b','c')
out <- numeric(length(df))
names(out) <- df
Now we can loop over the elements of df
to execute your query three times. We can set the loop up two ways: i) with i
as a number which we use to reference the elements of df
and out
, or ii) with i
as each element of df
in turn (i.e. a
, then b
, ...). I will show both versions below.
## Version i
for(i in seq_along(df)) {
SQL <- paste("SELECT max(ID) FROM table WHERE columna='", df[i], "';", sep = "")
out[i] <- dbGetQuery(con, SQL)
dbDisconnect(con)
}
OR:
## Version ii
for(i in df) {
SQL <- paste("SELECT max(ID) FROM table WHERE columna='", i, "';", sep = "")
out[i] <- dbGetQuery(con, SQL)
dbDisconnect(con)
}
Which you use will depend on personal taste. The second (ii) version requires you to set names on the output vector out
that are the same as the data inside out
.
Having said all that, assuming your actual SQL Query is similar to the one you post, can't you do this in a single SQL statement, using the GROUP BY
clause, to group the data before computing max(ID)
? Doing simple things in the data base like this will likely be much quicker. Unfortunately, I don't have a MySQL instance around to play with and my SQL-fu is weak currently, so I can't given an example of this.
Dynamic string in R
We can use paste:
Df <- sqlQuery(ch, paste("SELECT * FROM tblTest WHERE Id =", Id))
c
concatenates into a vector, paste
is for string concatenation.
Or we can use sprintf:
sprintf("SELECT * FROM tblTest WHERE Id = %s", Id)
RSQLite query with user specified variable in the WHERE field
SQLite will only see the string passed down for the query, so what you do is something like
sqlcmd <- paste("SELECT * FROM annual WHERE fiscal=", fiscal.year, sep="")
data.annual <- dbGetQuery(db, sqlcmd)
The nice thing is that you can use this the usual way to unwind loops. Forgetting for a second that you have ram restrictions, conceptually you can do
years <- seq(2000,2010)
data <- lapply(years, function(y) {
dbGetQuery(db, paste("SELECT * FROM annual WHERE fiscal=", y, sep="")
}
and now data is a list containing all your yearly data sets. Or you could keep the data, run your regression and only store the summary object.
Pass R variable to RODBC's sqlQuery?
Build the string you intend to pass. So instead of
example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")
do
example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (",
x, ")", sep=""))
which will fill in the value of x
.
Preparing character strings for sql in () query within dbGetQuery
Let’s assume that you have a query and want to substitute some range values in it.
sql <- "select col11,col23,col30
from schema.db.name
where col1 in (%s)
and col2 in (%s)
and col3 in (%s)
order by random()*600000 limit 100"
Here I will use sprintf to do the substitutions in the query,
so I have left markers (%s) in the string.
So let’s setup 3 ranges that we want to test for:
col1 <- 1:10
col2 <- c('a', 'f', 'z')
col3 <- c('name1', 'name2')
# create strings that are valid in SQL
col1_sql <- paste(col1, collapse = ',')
col2_sql <- paste0("'", col2, "'", collapse = ',') # put quotes on strings
col3_sql <- paste0("'", col3, "'", collapse = ',')
# now substitute back in query
sql_new <- sprintf(sql,
col1_sql, # strings we just constructed
col2_sql,
col3_sql
)
# print out the query
cat(sql_new)
Here is what the query will be:
select col11,col23,col30
from schema.db.name
where col1 in (1,2,3,4,5,6,7,8,9,10)
and col2 in ('a','f','z')
and col3 in ('name1','name2')
order by random()*600000 limit 100
Include headers when using SELECT INTO OUTFILE?
You'd have to hard code those headers yourself. Something like:
SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
FROM YourTable
INTO OUTFILE '/path/outfile'
Related Topics
Remove Duplicates Column Combinations from a Dataframe in R
R - Svd() Function - Infinite or Missing Values in 'X'
Accessing Y Columns with Duplicated Names in J of X[Y, J] Merges
Export Both Image and Data from R to an Excel Spreadsheet
Extract Data Between a Pattern from a Text File
Text Mining R Package & Regex to Handle Replace Smart Curly Quotes
Update a Ggplot Using a for Loop (R)
Contrast Between Label and Background: Determine If Color Is Light or Dark
R Sum Every K Columns in Matrix
Append Multiple CSV Files into One File Using R
Why Does As.Matrix Add Extra Spaces When Converting Numeric to Character
How to Capture the Output of System()
Create a Histogram for Weighted Values