Turns a collection of historical Betfair data into a queryable SQL database.
Install the package from PyPI:
pip install betfairdatabaseOn some platforms, it may be required to install tzdata which contains IANA time zone database:
pip install tzdata
- Index the folder holding historical Betfair data to turn it into a database.
- Use SQL queries to select data.
import betfairdatabase as bfdb
path_to_data = "./my_betfair_data"
bfdb.index(path_to_data) # Create an index to convert the folder into a database
# Select all greyhound races in Sheffield
dataset = bfdb.select(
path_to_data, where="eventTypeId='4339' AND eventVenue='Sheffield'"
)
for market in dataset:
print(
market["marketDataFilePath"], # Path to the stream data file
market["marketMetadataFilePath"], # Path to the market metadata file
)Both the self-recorded and official Betfair data files are supported. The historical data can be grouped and divided into any subfolder hierarchy, but it must follow this convention:
- Market metadata (market catalogue or market definition) is stored in a JSON file named
<market id>.json. - Market data file (containing stream data) is stored in the same folder as the market metadata file. It shares the same basename
<market id>and ends with.zip,.gzor.bz2, or it has no extension (uncompressed data).
A sample database structure is shown below:
my_betfair_data/
├── arbitrary_folder/
├── 1.22334455.json # Market metadata file
├── 1.22334455 # Uncompressed market data file
├── 1.55667788.json # Market metadata file
└── 1.55667788.zip # Compressed market data file
If a market metadata file is missing, it will be created from the most recent market definition found in the market data file. If no market definition is present in the data file, it will not be possible to index the file.
select() method accepts the following arguments:
database_dir: Main directory of the database initialised withindex().columns: A list of columns (field names) to retrieve. If omitted, all columns are returned. View a list of available columns by callingbetfairdatabase.columns().where: SQL query to execute.limit: Maximum number of results to return. If omitted, all results are returned.return_dict: IfTrue(default), results are returned as a dictionary where keys are column names and values are data. IfFalse, results are returned as tuples containing only data. The second option is faster but makes data harder to work with.
Below are several examples of selecting and filtering data:
import betfairdatabase as bfdb
path_to_data = "./my_betfair_data"
# Return all market ids and paths to data files in the database
bfdb.select(path_to_data, columns=["marketId", "marketDataFilePath"])
# Return full market metadata for horse racing win markets
bfdb.select(path_to_data, where="eventTypeId='7' AND marketType='WIN'")
# Return full market metadata for a maximum of 100 BSP markets
bfdb.select(path_to_data, where="bspMarket=true", limit=100)
# Return a maximum of 250 data file paths for horse and greyhound racing
bfdb.select(
path_to_data,
columns=["marketDataFilePath"],
where="eventTypeId IN ('7', '4339') AND marketType='WIN'",
limit=250,
)Database can be updated with new files using insert method. This is much faster and more efficient than reindexing the whole database on each update. Files are moved by default, but they can also be copied if copy=True argument is provided.
import betfairdatabase as bfdb
bfdb.insert("./my_betfair_data", "./my_capture_dir")Database index can be exported to a CSV file with the export() method. This is useful for debugging, visualising data and post-processing it with external tools.
import betfairdatabase as bfdb
csv_file = bfdb.export("./my_betfair_data", "./my_data_dump")
print(csv_file) # Prints: ./my_data_dump/my_betfair_data.csvThroughout the course of database's lifetime, indexed files may get removed. clean() method checks for the presence of indexed market data files and removes the missing entries from the index, avoiding the need to reindex the whole database on every single file removal. However, reindexing the database may be the faster option when a large number of files has been removed.
import betfairdatabase as bfdb
bfdb.clean("./my_betfair_data")To quickly check the number of indexed markets in the database, run:
import betfairdatabase as bfdb
bfdb.size("./my_betfair_data")All of the above methods can also be accessed through OOP interface via BetfairDatabase class. This is useful when performing multiple operations on the same database as the database directory needs to be provided only once.
from betfairdatabase import BetfairDatabase
db = BetfairDatabase("./my_betfair_data")
db.index()
db.select()
db.insert("./my_capture_dir")
db.export()
db.clean()
db.size()The package also installs a bfdb command line app, which provides access to the following methods:
bfdb index "./my_database_dir" # Index a database
bfdb export "./my_database_dir" "./my_db_dump.csv" # Export a database
bfdb insert "./my_database_dir" "./my_captured_data" # Update the database
bfdb clean "./my_database_dir" # Clean the database
bfdb size "./my_database_dir" # Check database sizeThe amount of displayed information is controlled with the following options:
-v/--verbose: Increases the amount of displayed messages. Useful for debugging.--no-progress-bar: Hides progress bars. Useful when logging output to a file.-q/--quiet: Suppress printing to terminal, including error messages. Also hides progress bars.
For more information about the command line interface, run:
bfdb --help