0

I have a data.frame stored in an SQL database, created with the dbWriteTable function of the R sqldf package.

I was wondering whether there is a way to fetch specific columns by their names from this data.frame in this SQL database.

For fetching rows by their names, this code works:

#let's say the row names I want to select from the data.frame are in a vector called selected_row_names, where the data.frame is called df and the SQL database is called db
query_text <- paste("('",paste(selected_row_names,collapse="', '"),"')",sep="")
query_expression_text <- paste("select * from ",df," where row_names in ",query_text," order by row_names",sep="")
selected_df = dbGetQuery(db, query_expression_text)

My question is whether there's anything equivalent for selecting columns by their names?

If I try this code, where my selected column names are: "col-1T-A_m1" and "col-1T-A_m2":

query_string <- paste("select col-1T-A_m1, col-1T-A_m1 from ", df, sep="")
selected_df  <- dbGetQuery(db, query_string)

I get this error message:

Error in sqliteSendQuery(con, statement, bind.data) : error in statement: unrecognized token: "1T".
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user1701545
  • 5,706
  • 14
  • 49
  • 80

1 Answers1

1

Assuming you are running raw SQL queries, then you should be able to simply specify the column names you want after SELECT, e.g.

query_string <- paste("select col1, col2, col3 from ", df)
selected_df  <- dbGetQuery(db, query_string)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I'm getting this error message: Error in sqliteSendQuery(con, statement, bind.data) : error in statement: unrecognized token: "1T". My query is: "select col-1T-A_m1, col-1T-A_m2, from df" – user1701545 Apr 14 '16 at 17:10
  • You need to escape the column names because there are spaces. So try this: `select \`col-1T-A_m1\`, \`col-1T-A_m2\`, from df` – Tim Biegeleisen Apr 14 '16 at 23:24