Select data from MySQL database and put in R dataframe-CodePudding

I can access a MySQL database and store output to an R dataframe using the following script where sam_pn = walker

con <- dbConnect(MySQL(),
                 user = user,
                 password = password,
                 host = host,
                 dbname = dbname)

df = dbGetQuery(con, "SELECT *
                            FROM sam AS s
                            JOIN che AS c ON c.che_label = s.sam_label1
                            WHERE sam_pn =  'walker'")

But what i would like to do is store 'walker' as an R value pn and then use pn value in the sql query like below so i can vary the pn value.... but it does not work. The syntax is not right. Note sam and che are tables in the database

pn = 'walker'
df = dbGetQuery(con, "SELECT *
                            FROM sam AS s
                            JOIN che AS c ON c.che_label = s.sam_label1
                            WHERE sam_pn =  'pn'")

CodePudding user response：

pn = 'walker'
df = dbGetQuery(con, "SELECT *
                            FROM sam AS s
                            JOIN che AS c ON c.che_label = s.sam_label1
                            WHERE sam_pn =  ?",
      params = list(pn))

CodePudding user response：

This is what worked in the end

pn = 'walker' 

data = dbGetQuery(con, paste0("SELECT *
                            FROM sam AS s
                            JOIN che AS c ON c.che_label = s.sam_label1
                            WHERE sam_pn =  '", pn ,"'"))