CODEWITHSUNDEEP
sqlrsqldfr-dbi
user1701545
user1701545

Reputation: 6190

Fetch columns by their names from an SQL database

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".

Upvotes: 0

Views: 1328

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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)

Upvotes: 1

Related Questions