Select data from MySQL database and put in R dataframe

Question

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

"Never" inject data into a query string, use bound parameters (or glue::glue_sql, though I generally recommend binding over glue). See db.rstudio.com/best-practices/run-queries-safely — r2evans
– r2evans, Commented Apr 27, 2022 at 15:50

r2evans · Accepted Answer · 2022-04-27 22:53:41Z

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

edited Apr 27, 2022 at 22:53

r2evans

167k8 gold badges92 silver badges176 bronze badges

answered Apr 27, 2022 at 14:47

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rustymarmot Over a year ago

Thanks for the code onyambu but this did not work. This is what I eventually did. ``` 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 ,"'")) ```

r2evans · Accepted Answer · 2022-04-29 00:42:29Z

0

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

edited Apr 29, 2022 at 0:42

r2evans

167k8 gold badges92 silver badges176 bronze badges

answered Apr 27, 2022 at 22:07

rustymarmot

1211 silver badge10 bronze badges

5 Comments

r2evans Over a year ago

Ummm, I think you misunderstood both my comment and the link. Injecting data like this into your query is really bad practice, for several reasons. I strongly suggest you look harder at @onyambu's answer and confirm that it works for you. If you can come back rationale why this must be the only answer and onyambu's clearly does not work, then I'll remove my downvote, otherwise I'll assert-by-vote that this way is not canonical, not good practice, and can lead to problems in performance if not other issues.

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

rustymarmot Over a year ago

Thanks, @r2evans . I'll take your advice and concede to those more experienced than me and retry onyambu's method on the weekend and let you know.

rustymarmot Over a year ago

@r2evans . Tried again and it definitely does not work. I get the follwing error.. Error in .local(conn, statement, ...) : unused argument (params = list("walker"))

r2evans Over a year ago

OK, I found the problem with why that doesn't work ... RMySQL hasn't had any significant code updates in (for the most part) 7 years, before params= become part of DBI-spec. Because of the age of RMySQL, I really suggest you install the MySQL odbc driver (dev.mysql.com/downloads/connector/odbc) and switch from RMySQL package to the odbc package.

Collectives™ on Stack Overflow

Select data from MySQL database and put in R dataframe

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related