CODEWITHSUNDEEP
rsqlitedplyr
wdkrnls
wdkrnls

Reputation: 4692

How to rename a SQLite table with dplyr?

I want to change the name of a SQLite table using R/dplyr.

From the documentation, it looks like (at first glance) it should be just:

db <- src_sqlite("db/path.sqlite", create = TRUE)
mtcars %>% add_rownames() %>% rename(car = rowname) -> mtcars2
copy_to(db, mtcars2, indexes = list("car"))
q <- "ALTER TABLE mtcars2 RENAME TO mtcars3"
tbl(db, sql(q))

However, this gives an error. I thought maybe it was the missing semi-colon, but I get the same error in either case.

 Error in sqliteSendQuery(conn, statement) (from #4) : 
  error in statement: near "ALTER": syntax error

The top of the traceback shows: 

21: .Call(rsqlite_query_send, con@Id, as.character(statement), bind.data)
20: sqliteSendQuery(conn, statement)
19: .local(conn, statement, ...)
18: DBI::dbSendQuery(con, build_sql("SELECT * FROM ", sql))
17: DBI::dbSendQuery(con, build_sql("SELECT * FROM ", sql))

Is there a way to change the table name relying on the connection generated by src_sqlite, or do I have to close this connection and open another connection using RSQLite/DBI directly? For my application, I'd much prefer the former.

Thanks in advance for any pointers.

Upvotes: 3

Views: 1179

Answers (1)

K.Daisey
K.Daisey

Reputation: 464

To do this using DBI you can use DBI:::dbSendQuery(db$con, sql(q)). By using db$con, you do not need to open another connection.

Upvotes: 3

Related Questions