I am using dplyr package to connect to PostgreSQL, and my code is as below:
# Connect to local PostgreSQL via dplyr
library('dplyr')
localdb <- src_postgres(dbname = 'postgres',
host = 'localhost',
port = 5432,
user = 'postgres',
password = '236236')
#Write table from R to PostgreSQL
iris<-as.data.frame(iris)
dbWriteTable(localdb$con,'iris',iris, row.names=FALSE)
The connection is successful, but after about 5 minutes, a message popped up, saying "Auto-disconnecting postgres connection (4308, 1)". I am not sure how this issue comes from, and I need to deal with large data which takes more than 5 minutes to write to PostgreSQL, so I want to know how to solve this auto-disconnecting issue.
I've had similar problems with src_sqlite(). The error that I got was Auto-disconnecting SQLiteConnection. Apparently, the usage of the src_* functions is now discouraged (from the documentation of tbl()).
However, modern best practice is to use tbl() directly on an DBIConnection.
Before I was using the code below. The code in itself didn't return any errors. However, after using the same db again, I would get the error Auto-disconnecting SQLiteConnection.
path <- 'C:/sql.db'
sql_data <- src_sqlite(path)
# work on the sql_data variable
tbl(sql_data)
DBI::dbDisconnect(thesql$con)
As already said, the usage of src_sqlite is discouraged. Using the preferred code below solved my issue.
sql_data <- DBI::dbConnect(RSQLite::SQLite(), dbname = path)
# work on the sql_data variable
tbl(sql_data)
DBI::dbDisconnect(thesql)
Pay attention that the disconnect statement slightly changed and that the SQLite() arguments are in fact dbConnect() arguments.