从MySQL数据库中选择数据，然后放入R DataFrame

Question

我可以使用以下脚本访问MySQL数据库，并将输出存储到R数据框架中，其中 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'")

值 pn ，然后在下面的SQL查询中使用PN值，以便我可以更改Pn值....但它不起作用。语法不正确。注意 sam 和 che 是数据库中的表格

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

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

Answer 2

这是最终起作用的

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