Collect & Store Data From API using requests in python to SQLite database

I am new to python and django. But I am trying to set up an app in my project which collects odds data from an API then parses out what I need from the json, and then saves it to a table in an SQLite database.

So far I have the following, which gives me the data I would like to collect and store. The data will need to be called and updated if changed once or twice a day to ensure it's up to date.

def BluebetNrlOdds():
    """Fetch and extract JSON data from Bluebet Australia"""
    import requests
    import json

    # Bluebet Rugby League Odds API.
    link = 'https:/xxxxxxxxxx.com.au/json/reply/MasterCategoryRequest?EventTypeID=102&WithLevelledMarkets' \
           '=true&WithLevelledMarkets=true '
    # Request data from link as 'str'
    nrldata = requests.get(link).text
    # convert 'str' to Json
    nrldata = json.loads(nrldata)

    # NRL ODDS DATA
    for nrl_odds in nrldata['MasterCategories'][0]['Categories'][0]['MasterEvents']:
        competition = nrl_odds['CategoryName']
        event_id = nrl_odds['MasterEventId']
        event_title = nrl_odds['MasterEventName']
        start_time = nrl_odds['MaxAdvertisedStartTime']
        home_team = nrl_odds['Markets'][0]['OutcomeName']
        home_team_win_odds = nrl_odds['Markets'][0]['Price']
        home_team_win_handicap = nrl_odds['Markets'][2]['Points']
        home_team_handicap_odds = nrl_odds['Markets'][2]['Price']
        home_team_1_12 = nrl_odds['Markets'][4]['Price']
        home_team_13 = nrl_odds['Markets'][6]['Price']
        away_team = nrl_odds['Markets'][1]['OutcomeName']
        away_team_win_odds = nrl_odds['Markets'][0]['Price']
        away_team_win_handicap = nrl_odds['Markets'][3]['Points']
        away_team_win_handicap_odds = nrl_odds['Markets'][3]['Price']
        away_team_1_12 = nrl_odds['Markets'][5]['Price']
        away_team_13 = nrl_odds['Markets'][7]['Price']

        print(competition, event_id, event_title, start_time, home_team, home_team_win_odds, home_team_win_handicap, home_team_handicap_odds, home_team_1_12, home_team_13, away_team, away_team_win_odds, away_team_win_handicap, away_team_win_handicap_odds, away_team_1_12, away_team_13)

when you run that code it prints

NRL Telstra Premiership 1047531 Penrith Panthers v Parramatta Eels 2022-09-09T09:55:00.0000000Z Penrith Panthers 1.4 -7.5 1.95 2.85 2.58 Parramatta Eels 1.4 7.5 1.85 3.8 7.6
NRL Telstra Premiership 1047539 Melbourne Storm v Canberra Raiders 2022-09-10T07:40:00.0000000Z Melbourne Storm 1.4 -8.5 2.0 3.05 2.52 Canberra Raiders 1.4 8.5 1.8 3.85 7.9
NRL Telstra Premiership 1047538 Cronulla-Sutherland Sharks v North Queensland Cowboys 2022-09-10T09:50:00.0000000Z Cronulla-Sutherland Sharks 1.7 -2.5 1.9 3.1 3.45 North Queensland Cowboys 1.7 2.5 1.9 3.35 5.2
NRL Telstra Premiership 1047530 Sydney Roosters v South Sydney Rabbitohs 2022-09-11T06:05:00.0000000Z Sydney Roosters 1.6 -4.5 1.95 2.95 3.15 South Sydney Rabbitohs 1.6 4.5 1.85 3.6 5.8

I now need to save that data into SQlite database.

So far i have a seperate database.py file which has the following code in it

import sqlite3

# connect to database
connection = sqlite3.connect('puntsportsau')

# create cursor
c = connection.cursor()

c.execute("""SELECT TABLE bluebet_au_rugby_league_odds
(
    competition        text,
    event_id                integer
        constraint bluebet_au_rugby_league_odds_pk
            primary key,
    event_title             text,
    start_time              integer,
    home_team               text,
    home_team_win_odds      integer,
    home_team_handicap      integer,
    home_team_handicap_odds integer,
    home_team_1_12          integer,
    home_team_13            integer,
    away_team               integer,
    away_team_win_odds      integer,
    away_team_handicap      integer,
    away_team_handicap_odds integer,
    away_team_13            integer
, away_team_1_12 integer)

;""")

connection.commit()
connection.close()

In the models.py file for the package I have the following

# Rugby League Odds

class BluebetNrlOdds(models.Model):
      competition = models.CharField(max_length=255)
      event_id = models.IntegerField
      event_title = models.TextField(max_length=250)
      start_time = models.DateTimeField(auto_now_add=True)
      home_team = models.CharField(max_length=255)
      home_team_win_odds = models.IntegerField
      home_team_handicap = models.IntegerField
      home_team_handicap_odds = models.IntegerField
      home_team_1_12 = models.IntegerField
      home_team_13 = models.IntegerField
      away_team = models.CharField(max_length=255)
      away_team_win_odds = models.IntegerField
      away_team_handicap = models.IntegerField
      away_team_handicap_odds = models.IntegerField
      away_team_1_12 = models.IntegerField
      away_team_13 = models.IntegerField

      class Meta:
          verbose_name = 'Bluebet NRL Odds'
          verbose_name_plural = 'Bluebet NRL Odds'

     def __str__(self):
          return self.name

I am stuck with how I can get the data collected and parsed from the requests to save it to the SQlite database table. I need to do it once or twice a day.

Can anyone give me some direction on what I need to do or show me an example that I can work with to figure it out?

I hope that's enough detail, let me know if you need anything further to assist.

Any help greatly appreciated.

Welcome to the world of Python and Django;

You can run Django commands with a corn job to build a complete use case.

Quick notes:

In Python, the function name should follow the PEP-8 guide. You can read more about PEP-8 style-guide

Function names should be lowercase, with words separated by underscores as necessary to improve readability. Variable names follow the same convention as function names. mixedCase is allowed only in contexts where that's already the prevailing style (e.g. threading.py), to retain backwards compatibility.

Drop the use of database.py. A Django project can handle the cursor connection using Django's settings.DATABASES

I'd advise you to go through building your first Django app, which will guide you on how to quickly start a Django project and the concepts behind using manag.py settings.py and so on

Back to Top