mysql_in_R.R

library(RMySQL)
library(GetoptLong)

# had to work with a secure connection to a MySQL
# make a option file, e.g. opts.cnf
"
[client]
user=kenny
host=mysqldb
ssl-ca=ca.crt
ssl-cert=cert.crt
ssl-key=mykey.key
"

# other assignments 
# e.g. df_name = "extraction_{{country}}"

# run query multiple times across different country tables
countries = c("singapore", "malaysia", "thailand")
for (country in countries) {
    country_query = qq(query, code.pattern="\\{\\{CODE\\}\\}")
    country_df_name = qq(df_name, code.pattern="\\{\\{CODE\\}\\}")
    con = dbConnect(MySQL(), user="kenny", dbname=db, host="mysqldb", default.file="opts.cnf")
    tmp_df = dbGetQuery(con, country_query)
    assign(country_df_name, tmp_df)
    dbDisconnect(con)
}