Section 4 Connecting and disconnecting SQL databases
4.1 Connection/disconnection script
Download the script dbconnect.R to the scripts folder you created.
The contents of the script are shown below. This will be described in detail in the workshop.
[1] # a function to connect to a PostgreSQL database
[2]
[3] library(RPostgreSQL)
[4]
[5] # run similar to this:
[6] # foo <- connectdb(dbname = "foo", username = "csde")
[7] # disconnectdb(foo)
[8]
[9] # make a connection
[10] connectdb <- connectDB <- function(dbname, host="localhost", port=5432, username){
[11] # if the db connection does not exist
[12] if(!exists(dbname)){
[13] # make the connection
[14] conn <- dbConnect(dbDriver("PostgreSQL"), dbname = dbname, host=host, port=port, user = username)
[15] # print a message
[16] message("connecting to ", dbname)
[17] return(conn)
[18] }
[19] # if the db connection exists
[20] if(exists(dbname)){
[21] # check to see if the connection is stale
[22] cmd <- sprintf("isPostgresqlIdCurrent(%s)", dbname)
[23] connectionIsCurrent <- eval(parse(text=cmd))
[24] # if the connection is stale, reconnect
[25] if(!connectionIsCurrent){
[26] message("establishing connection to ", dbname)
[27] conn <- dbConnect(dbDriver("PostgreSQL"), dbname = dbname, host=host, port=port, user = username)
[28] } else {
[29] # otherwise assign the string name to the db connection
[30] cmd <- sprintf("conn <- %s", dbname)
[31] eval(parse(text=cmd))
[32] }
[33] return(conn)
[34] }
[35] }
[36]
[37] # disconnect one
[38] disconnectdb <- function(conn){
[39] dbname <- dbGetInfo(dbObj = conn)$dbname
[40] # check to see if the object exists
[41] if(exists(dbname)){
[42] # check to see if the connection is current
[43] cmd <- sprintf("isPostgresqlIdCurrent(%s)", dbname)
[44] connectionIsCurrent <- eval(parse(text=cmd))
[45] if(connectionIsCurrent){
[46] message(sprintf("disconnecting %s", dbname))
[47] cmd <- sprintf("dbDisconnect(conn = %s)", dbname)
[48] eval(parse(text = cmd))
[49] cmd <- sprintf("rm(%s, envir = .GlobalEnv)", dbname)
[50] eval(parse(text = cmd))
[51]
[52] }
[53] }
[54] }
[55]
[56] # disconnect all connections
[57] disconnectall <- function(verbose = FALSE){
[58] # parse over all current connections, disconnecting each one
[59] O <- lapply(dbListConnections(drv = PostgreSQL()), dbDisconnect)
[60] if(verbose){
[61] message(O)
[62] }
[63] }
[64]
[65] # options for using sqldf (connect to PostgreSQL rather than SQLite)
[66] # settings
[67] sqldf.pg.options <- function(dbname="test", host="localhost", port=5432, user=Sys.getenv("USER") ){
[68] # get the password
[69] if(Sys.info()['sysname'] == "Windows"){
[70] pgpass = scan(file.path(Sys.getenv("APPDATA"), "postgresql/pgpass.conf"), what = "character")
[71] } else {
[72] pgpass = scan("~/.pgpass", what = "character")
[73] }
[74] passwd <- strsplit(pgpass, ":")[[1]][5]
[75]
[76] # set the options
[77] options(
[78] sqldf.RPostgreSQL.user = user,
[79] sqldf.RPostgreSQL.password = passwd,
[80] sqldf.RPostgreSQL.dbname = dbname,
[81] sqldf.RPostgreSQL.host = host,
[82] sqldf.RPostgreSQL.port = port
[83] )
[84] }
[85]
[86] # convenience function for testing whether a db table exists?
[87] tExists <- dbTableExists <- function(conn, table_schema, table_name){
[88] sql <- sprintf("select count(*) = 1 from information_schema.tables where table_schema = '%s' and table_name = '%s';", table_schema, table_name)
[89] dbGetQuery(conn = conn, sql)[1,1]
[90] } The script includes some convenience functions for connecting and disconnecting. The DBI functions these rely on are dbConnect() and dbDisconnect().
4.2 Making a database connection
Add these lines to your Rmd file:
<!--<start rmd_add_02.txt>-->
[R andSQL databases](http://staff.washington.edu/phurvitz/r_sql){target="_blank"}
# Simple database interaction
## Showing connection parameters
Sometimes we want to see the database connection parameters. The basic connection parameters are shown with the `dbGetInfo(conn)` function.
The connection parameters below:
```{r getinfo}
dbGetInfo(rsql)
```
show that the connection is to the `localhost` local server on port `5432`, connecting as user `csde` to the database `rsql`, and that the server is running PostgreSQL version `12.0.2` (though your version may vary.)
## Test that the connection actually works
Here we will run a simple function to show that we are able to access data in the database. The `dbListTables()` function shows the typically user-accessible tables in the database search path.
_`r table_nums(name = "dbt", caption = "Current database tables")`_
```{r q1}
dbt <- data.frame(table_name = dbListTables(conn = rsql))
kable(x = dbt, format = "html") %>% kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, fixed_thead = T, position = "left")
```
Currently the only table in the database (other than the internal tables that users typically do not use directlry) is the one set up by the PostGIS extension. If this Rmd file is rendered after we have added any tables to the database, there should be more tables listed.
# Disconnecting
To disconnect from the database, we can use the custom function `disconnectdb()`. Although we will not disconnect yet ... there's still work to be done.
```{r disconnect, eval=FALSE}
disconnectdb(conn = rsql)
```
<hr class="new4">
<!--<end rmd_add_02.txt>-->And then render the file:
rmarkdown::render("rmd/r_sql.rmd")The code demonstrates how to connect to the database, show that the connection has been made, and then disconnects.