Easiest way to rebuild a Statcast database

[jestarr]

I followed this great post in order to build my Statcast database. https://billpetti.github.io/2018-02-19-build-statcast-database-rstats/

The only additions were:

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "statcast.sqlite3")

dbWriteTable(con, "statcast", statcast_bind)

I went to look at the Statcast data on Baseball Savant, and it looks like some of the pitch type data has changed. I haven't looked at every year yet, but it has definitely changed for 2017.

2 Questions for you.

1. What's the easiest way to rebuild the database? At a minimum, I'd want to replace the 2017 values in my database.

2. Can I bind 1 season at a time to the Database using the dbWriteTable function above, or would that function overwrite my existing "statcast" table in the database?

Thanks!

[BillPetti]

You can drop all rows in a database using code similar to this--this will delete all rows with observations from 2017 using game_year as the filtering variable:

dbGetQuery(<your db connection>, "DELETE from <your db table> where game_year = 2017")

And you can append a set of data to your existing table, assuming the tables have identical columns:

dbWriteTable(<your db connection>, <your db table>, <data frame to append>, append = TRUE, overwrite = FALSE)