Purpose of Code

The purpose of the main function (get_player_penalties()) is to:

  • Generate simple SQL statements from user input and a template

  • Query a database using the generated SQL and return a summary table to the user

Ultimately, I will reference this code to create similar functions (and SQL templates) that return different tables to the user. My intent was to make the structure modular enough to be able to create new functions, with few changes to the structure.

Eventually, I hope to use these functions in a Shiny App, though the user will not have access to input text into the app - nor will they have write access to the database. (So, I am not concerned about SQL Injections for the purposes of this review).


My code relies on R and the following R packages:

  • DBI

  • glue

  • RSQLite

Those packages can be installed with:

install.packages(c('DBI', 'glue', 'RSQLite'))

Database Layout & Example Context

I created a toy example of my code so that it would be easy for others to run. It includes an example database with three tables. This is a database tracking player penalties for each game. My example function summarizes penalties by player across a season or a specific game. The database looks like this:

  • game has columns game_id and season

  • penalty has columns game_id, player_id, and penalty_code,

  • lu_tbl_penalty has columns penalty_code and penalty_desc

My main function is called get_player_penalties(). As I mentioned in the "Purpose" section, I hope to apply the same structure of this function to create others. (e.g., get_player_scores() or get_player_stats()).

Requested Feedback

Any feedback would be great, though I am specifically concerned with making my code modular enough to apply to other queries that might come up. In that sense, what could I do to improve how this code might scale?


To help orient you, I provide the code in the following order:

  • Code to generate an example database

  • Code to simulate a .sql file "template"

  • The main function for review

  • Example uses

# Create Example Database

## Penalty Table
penalty <- data.frame(
  game_id = c("001", "001", "001", "001", "002", "002", "002", "003"),
  player_id = c("99", "99", "99", "55", "77", "99", "55", "77"),
  penalty_code = c("b", "b", "i", "b", "i", "g", "i", "i"),
  stringsAsFactors = F

## Game Table
game <- data.frame(
  game_id = c("001", "002", "003"),
  season = c("2018", "2018", "2019"),
  stringsAsFactors = F

## Penalty Lookup Table
lu_tbl_penalty <- data.frame(
  penalty_code = c("g", "i", "b"),
  penalty_desc = c("Gross Misconduct", "Illegal Move", "Block"),
  stringsAsFactors = F  

## Create temporary Database in memory
## Store Connection Object in `con`
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

## Write tables to temp Database
DBI::dbWriteTable(con, "penalty", penalty)
DBI::dbWriteTable(con, "game", game)
DBI::dbWriteTable(con, "lu_tbl_penalty", lu_tbl_penalty)

# Example SQL template
sql_template <- "SELECT penalty_desc AS Penalty,
       COALESCE(penalty_count, 0) AS 'Count'
FROM lu_tbl_penalty left join (
    SELECT penalty_code,
           COUNT(penalty_code) AS penalty_count
    FROM penalty LEFT JOIN game ON (penalty.game_id = game.game_id)
    WHERE penalty.game_id = {game_id}
    AND player_id = {player_id}
    AND game.season = {season}
    GROUP BY penalty_code
) x ON (lu_tbl_penalty.penalty_code = x.penalty_code)
ORDER BY penalty_count DESC, penalty_desc"

# Main function
get_player_penalties <- function(con, player_id = NULL, scope = "season", values = 2018){
  player_id <- paste0("'", player_id, "'")
  game_id <- "penalty.game_id"
  season <- "game.season"
  assign(scope, paste0("'", values, "'"))
  sql <- glue::glue(sql_template,
                    player_id = player_id,
                    game_id = game_id,
                    season = season)
  DBI::dbGetQuery(con, sql)

# Examples Uses
get_player_penalties(con, "99", "game_id", "001")
get_player_penalties(con, "99")
get_player_penalties(con, "77", values = 2019)



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.