sportdb-readers - sport.db readers for leagues, seasons, clubs, match schedules and results, and more

Usage

Step 1

Setup the (SQL) database. Let's use and build a single-file SQLite database (from scratch), as an example:

require 'sportdb/readers'

SportDb.connect( adapter:  'sqlite3',
                 database: './england.db' )
SportDb.create_all       ## build database schema (tables, indexes, etc.)

Step 2

Let's read in some leagues, seasons, clubs, and match schedules and results. Let's use the public domain football.db datasets for England (see openfootball/england), as an example:

= English Premier League 2015/16

Matchday 1

[Sat Aug 8]
  Manchester United      1-0  Tottenham Hotspur
  AFC Bournemouth        0-1  Aston Villa
  Everton FC             2-2  Watford FC
  Leicester City         4-2  Sunderland AFC
  Norwich City           1-3  Crystal Palace
  Chelsea FC             2-2  Swansea City
[Sun Aug 9]
  Arsenal FC             0-2  West Ham United
  Newcastle United       2-2  Southampton FC
  Stoke City             0-1  Liverpool FC
[Mon Aug 10]
  West Bromwich Albion   0-3  Manchester City

...

(Source: england/2015-16/1-premierleague-i.txt)

and let's try:

## assumes football.db datasets for England in ./england directory
##   see github.com/openfootball/england
SportDb.read( './england/2015-16/1-premierleague-i.txt' )
SportDb.read( './england/2015-16/1-premierleague-ii.txt' )

## let's try another season
SportDb.read( './england/2019-20/1-premierleague.txt' )

All leagues, seasons, clubs, match days and rounds, match fixtures and results, and more are now in your (SQL) database of choice.

The proof of the pudding - Let's query the (SQL) database using the sport.db ActiveRecord models:

include SportDb::Models

pl_2015_16 = Event.find_by( key: 'eng.1.2015/16' )
#=> SELECT * FROM events WHERE key = 'eng.1.2015/16' LIMIT 1

pl_2015_16.teams.count  #=> 20
#=> SELECT COUNT(*) FROM teams
#      INNER JOIN events_teams ON teams.id = events_teams.team_id
#      WHERE events_teams.event_id = 1

pl_2015_16.games.count  #=> 380
#=>  SELECT COUNT(*) FROM games
#      INNER JOIN rounds ON games.round_id = rounds.id
#      WHERE rounds.event_id = 1

pl_2019_20 = Event.find_by( key: 'eng.1.2019/20' )
pl_2015_16.teams.count  #=> 20
pl_2015_16.games.count  #=> 380

# -or-

pl = League.find_by( key: 'eng.1' )
#=> SELECT * FROM leagues WHERE key = 'eng.1' LIMIT 1

pl.seasons.count   #=> 2
#=> SELECT COUNT(*) FROM seasons
#     INNER JOIN events ON seasons.id = events.season_id
#     WHERE events.league_id = 1

# and so on and so forth.

Bonus: As an alternative pass in the "package" directory or a zip archive and let read figure out what datafiles to read in:

## assumes football.db datasets for England in ./england directory
##   see github.com/openfootball/england
SportDb.read( './england' )
## -or-   use a zip archive download
SportDb.read( './england.zip' )

That's it.

Frequently Asked Questions (F.A.Q.s) and Answers

Q: What about reading in datasets in comma-separated values (CSV) format? Example:

Round, Date,              Team 1,               FT,  HT,  Team 2
1,     (Fri)  9 Aug 2019, Liverpool FC,         4-1, 4-0, Norwich City FC
1,     (Sat) 10 Aug 2019, West Ham United FC,   0-5, 0-1, Manchester City FC
1,     (Sat) 10 Aug 2019, AFC Bournemouth,      1-1, 0-0, Sheffield United FC
1,     (Sat) 10 Aug 2019, Burnley FC,           3-0, 0-0, Southampton FC
1,     (Sat) 10 Aug 2019, Crystal Palace FC,    0-0, 0-0, Everton FC
1,     (Sat) 10 Aug 2019, Watford FC,           0-3, 0-1, Brighton & Hove Albion FC
1,     (Sat) 10 Aug 2019, Tottenham Hotspur FC, 3-1, 0-1, Aston Villa FC
1,     (Sun) 11 Aug 2019, Leicester City FC,    0-0, 0-0, Wolverhampton Wanderers FC
1,     (Sun) 11 Aug 2019, Newcastle United FC,  0-1, 0-0, Arsenal FC
1,     (Sun) 11 Aug 2019, Manchester United FC, 4-0, 1-0, Chelsea FC
...

(Source: england/2019-20/eng.1.csv)

Yes, you can. See the sportdb-importers library / gem ยป

License

The sportdb-readers scripts are dedicated to the public domain. Use it as you please with no restrictions whatsoever.

Questions? Comments?

Send them along to the Open Sports & Friends Forum/Mailing List. Thanks!