tidystep - dbcooper:new way to connect with database

dbcooper in R

install dbcooper:

Code

devtools::install_github("chriscardillo/dbcooper")

Code

library(dbcooper)
library(tidyverse)

connection

Code

lahman_db <- dbplyr::lahman_sqlite()
lahman_db

<SQLiteConnection>
  Path: C:\Users\ThinkPad\AppData\Local\Temp\RtmpGAkaJN\lahman.sqlite
  Extensions: TRUE

initial dbcooper

Code

dbc_init(lahman_db, "lahman")

list table under database

Code

lahman_list()

 [1] "AllstarFull"         "Appearances"         "AwardsManagers"     
 [4] "AwardsPlayers"       "AwardsShareManagers" "AwardsSharePlayers" 
 [7] "Batting"             "BattingPost"         "CollegePlaying"     
[10] "Fielding"            "FieldingOF"          "FieldingOFsplit"    
[13] "FieldingPost"        "HallOfFame"          "HomeGames"          
[16] "LahmanData"          "Managers"            "ManagersHalf"       
[19] "Parks"               "People"              "Pitching"           
[22] "PitchingPost"        "Salaries"            "Schools"            
[25] "SeriesPost"          "Teams"               "TeamsFranchises"    
[28] "TeamsHalf"           "sqlite_stat1"        "sqlite_stat4"

Code

lahman_allstar_full()

# Source:   SQL [?? x 8]
# Database: sqlite 3.39.4 [C:\Users\ThinkPad\AppData\Local\Temp\RtmpGAkaJN\lahman.sqlite]
   playerID  yearID gameNum gameID       teamID lgID     GP startingPos
   <chr>      <int>   <int> <chr>        <chr>  <chr> <int>       <int>
 1 gomezle01   1933       0 ALS193307060 NYA    AL        1           1
 2 ferreri01   1933       0 ALS193307060 BOS    AL        1           2
 3 gehrilo01   1933       0 ALS193307060 NYA    AL        1           3
 4 gehrich01   1933       0 ALS193307060 DET    AL        1           4
 5 dykesji01   1933       0 ALS193307060 CHA    AL        1           5
 6 cronijo01   1933       0 ALS193307060 WS1    AL        1           6
 7 chapmbe01   1933       0 ALS193307060 WS1    AL        1           7
 8 simmoal01   1933       0 ALS193307060 CHA    AL        1           8
 9 ruthba01    1933       0 ALS193307060 NYA    AL        1           9
10 averiea01   1933       0 ALS193307060 CLE    AL        1          NA
# … with more rows

SQL:

Code

lahman_query("SELECT
              playerID
              FROM AllstarFull
            ")

# Source:   SQL [?? x 1]
# Database: sqlite 3.39.4 [C:\Users\ThinkPad\AppData\Local\Temp\RtmpGAkaJN\lahman.sqlite]
   playerID 
   <chr>    
 1 aaronha01
 2 aaronha01
 3 aaronha01
 4 aaronha01
 5 aaronha01
 6 aaronha01
 7 aaronha01
 8 aaronha01
 9 aaronha01
10 aaronha01
# … with more rows

dplyr:

Code

lahman_allstar_full() %>% select(playerID)

# Source:   SQL [?? x 1]
# Database: sqlite 3.39.4 [C:\Users\ThinkPad\AppData\Local\Temp\RtmpGAkaJN\lahman.sqlite]
   playerID 
   <chr>    
 1 aaronha01
 2 aaronha01
 3 aaronha01
 4 aaronha01
 5 aaronha01
 6 aaronha01
 7 aaronha01
 8 aaronha01
 9 aaronha01
10 aaronha01
# … with more rows

execute to create a delete table:

Code

lahman_execute("CREATE TABLE Players AS
                  SELECT playerID, SUM(AB) AS AB
                  FROM Batting
                  GROUP BY playerID")

Reference

https://github.com/chriscardillo/dbcooper

https://github.com/machow/dbcooper-py

http://varianceexplained.org/files/dbcooper-rstudio-conf-2022.pdf

https://zh.wikipedia.org/wiki/D%C2%B7B%C2%B7%E5%BA%AB%E6%9F%8F