R - Dataframe values inside mysql query in R

337
August 05, 2017, at 06:33 AM

In R - Is it possible to make a mysql query with a IN, where the values in that IN are from a DataFrame column?

EXAMPLE of what I'm trying to do:

Directory = read.csv("worlddirectory.csv", header = TRUE, sep = ",",stringsAsFactors=FALSE)
Active_Customers = Directory[(Directory$Status == "Active"),]
PhoneNumbers = dbGetQuery(DBConnection,
"
Select
db.phonenumbers,
db.names
from
database db
where
db.country IN
(
Active_Customers$Country
);"
Answer 1

As we can see here, the expected statement looks like:

WHERE column_name IN (value1, value2, ...);

We can use paste with the argument collapse=", " to obtain the desired format. I think this should work:

PhoneNumbers = dbGetQuery(DBConnection,
paste0("SELECT db.phonenumbers, db.names ",
"FROM database db ",
"WHERE db.country IN (",
       paste(Active_Customers$Country,collapse=", "),");"))

Example:

Active_Customers <- data.frame(Country=c("NL","BE","US"))
paste0("SELECT db.phonenumbers, db.names ",
"FROM database db ",
"WHERE db.country IN (",
paste(Active_Customers$Country,collapse=", "),");")

Output:

[1] "SELECT db.phonenumbers, db.names FROM database db WHERE db.country IN (NL, BE, US);"

Hope this helps!

Rent Charter Buses Company
READ ALSO
I don&#39;t know how to write a query to display some specific information

I don't know how to write a query to display some specific information

I am trying to display some information from a SQL database and I don't know exactly how to write the queryI have a table which contains the "name" and the "studentship" of some students

207
MySQL: Updating entry WITHOUT updating timestamp

MySQL: Updating entry WITHOUT updating timestamp

I have a timestamp in a mysql table with attribute "ON UPDATE CURRENT_TIMESTAMP"Is there a way to manually disable updating the timestamp on a special occasion? (eg: updating the entry to revise a blog post, but not to re-date it)

309
Inserting data using mysqli

Inserting data using mysqli

This code gets through all of the debugs but for some reason, it is still not insertingIt tries to check if the username already exists in the database and if it doesn't, it adds it

305