postgresql - R: how to query from a database multiple times based on different dates

Question

I have an R Markup file in which I establish a database connection, query data, and store the data in an csv file. The query is based on a specific date range. How can I automated make multiple queries, so that one after another e.g. every week is queried from the database? I cannot make a query for e.g. the whole year, but I need to store the data separately for each week. I could make a data frame, in which I have two columns for the start and end date, which I would like to use for the query.

But how can I automatically run the queries multiple times depending on the date data frame?

My code so far:

#load libraries

drv <- PostgreSQL()
db_con <- dbConnect(drv, host=my_host, user=my_user, dbname=my_name, port=my_port, password=my_password)

start = "2015-01-01"
end = "2015-01-02"

result <- dbGetQuery(
db_con, 
"SELECT * FROM table WHERE date >= start AND date <= end;")

st_write(result, pathname)

question from:https://stackoverflow.com/questions/65944181/r-how-to-query-from-a-database-multiple-times-based-on-different-dates

Answer 1

Consider parameterization using DBI::sqlInterpolate with a Map (wrapper to mapply) iteration:

db_con <- dbConnect(
             PostgreSQL(), host=my_host, user=my_user, dbname=my_name, 
             port=my_port, password=my_password)
          )

# ALL WEEKLY DATES IN 2015
dates_df <- data.frame(
                 start=seq.Date(as.Date("2015-01-01"), as.Date("2016-01-01"), by="week"),
                 end=seq.Date(as.Date("2015-01-08"), as.Date("2016-01-08"), by="week")
            )

# USER DEFINED METHOD TO QUERY AND WRITE DATA
query_db <- function(s, e) {
   # PREPARED STATEMENT WITH PLACEHOLDERS
   sql <- "SELECT * FROM table WHERE date >= ?start AND date <= ?end;"

   # BIND PARAMETERS AND QUERY
   stmt <- DBI::sqlInterpolate(db_con, sql, start=s, end=e)
   result <- dbGetQuery(db_con, stmt)

   # WRITE DATA TO DISK 
   st_write(result, pathname)

   # RETURN QUERY RESULTSET
   return(result)
}

# WRITE AND STORE DATA IN MEMORY
df_list <- Map(query_db, dates_df$start, dates_df$end)