'Select data from MySQL database and put in R dataframe

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'")
sqlrrmysql


Solution 1:[1]

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))

Solution 2:[2]

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 ,"'"))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 r2evans
Solution 2 r2evans

Related Questions