2

I am trying to attach one SQLite database to another. On the terminal, I can do this pretty simply with the ATTACH command, but I am working in an R script and I want to get it to work through a DBI connection. I have been able to attach with system calls, but it would be really nice to do it through a database connection.

DBI Connection try:

library(DBI)

NewDBFile <- "new.db"
archiveFile <- "archive.db"

con <- dbConnect(RSQLite::SQLite(), NewDBFile)

rs <- dbExecute(conn = con, paste0("ATTACH ", archiveFile, " AS archive;"))

dbDisconnect(con)

With this I get an "Error: no such column: [database name]"

Any ideas on how I would go about doing this?

r2evans
  • 141,215
  • 6
  • 77
  • 149
Lia_G
  • 145
  • 1
  • 8
  • You need `select name from archive.sqlite_master WHERE type='table'` to get the tables in the archive database. – G. Grothendieck Aug 19 '21 at 18:43
  • The issue is that it won't attach in the first place. I get errors like "Error: no such column: [database name]" when trying to attach. Or no errors and it simply can't find the archive, even with the above syntax. If I attach via the terminal and then look for the tables, I can find them. If I have to I can write the whole sql file and use a system call, but I was hoping for another option. – Lia_G Aug 19 '21 at 19:10

1 Answers1

4

Demo of ATTACH working in R:

con1 <- DBI::dbConnect(RSQLite::SQLite(), "dat1.sqlite3")
DBI::dbWriteTable(con1, "tbl1", data.frame(id=1:3, aa=1:3))
con2 <- DBI::dbConnect(RSQLite::SQLite(), "dat2.sqlite3")
DBI::dbWriteTable(con2, "tbl2", data.frame(id=1:3, bb=11:13))
DBI::dbExecute(con1, "attach database 'dat2.sqlite3' as dat2")
# [1] 0

DBI::dbGetQuery(con1, "
  select t1.*, t2.bb
  from tbl1 t1
    left join dat2.tbl2 t2 on t1.id=t2.id")
#   id aa bb
# 1  1  1 11
# 2  2  2 12
# 3  3  3 13

Incidentally, this does not show the attached tables in a single query:

DBI::dbGetQuery(con1, "select * from sqlite_master")
#    type name tbl_name rootpage                                                       sql
# 1 table tbl1     tbl1        2 CREATE TABLE `tbl1` (\n  `id` INTEGER,\n  `aa` INTEGER\n)

DBI::dbGetQuery(con1, "select * from dat2.sqlite_master")
#    type name tbl_name rootpage                                                       sql
# 1 table tbl2     tbl2        2 CREATE TABLE `tbl2` (\n  `id` INTEGER,\n  `bb` INTEGER\n)

Basic info for me:

packageVersion("DBI")
# [1] '1.1.1'
packageVersion("RSQLite")
# [1] '2.2.1'
R.version
#                _                           
# platform       x86_64-w64-mingw32          
# arch           x86_64                      
# os             mingw32                     
# system         x86_64, mingw32             
# status                                     
# major          4                           
# minor          0.5                         
# year           2021                        
# month          03                          
# day            31                          
# svn rev        80133                       
# language       R                           
# version.string R version 4.0.5 (2021-03-31)
# nickname       Shake and Throw
r2evans
  • 141,215
  • 6
  • 77
  • 149