使用请求从API收集数据并将其存储到SQLite数据库

iaqfqrcu  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(175)

我对巨蟒和 Django 还是个新手。但我正试图在我的项目中设置一个应用程序,它从API收集赔率数据,然后从JSON解析出我需要的数据,然后将其保存到SQLite数据库中的表中。
到目前为止,我有以下内容,它们为我提供了我想要收集和存储的数据。如果数据每天更改一到两次,则需要调用和更新数据,以确保数据是最新的。

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)

运行该代码时,它会打印

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

我现在需要将该数据保存到SQlite数据库中。
到目前为止,我已经有了一个单独的database ase.py文件,其中包含以下代码

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()

在包的Models.py文件中,我有以下内容

# 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

我陷入了如何从请求中收集和解析数据以将其保存到SQlite数据库表中的问题。我一天需要做一到两次。
有没有人可以给我一些指导,告诉我需要做什么,或者给我举个例子,让我知道该怎么做?
我希望这是足够的细节,让我知道如果你需要任何进一步的协助。
任何帮助都不胜感激。

ycggw6v2

ycggw6v21#

欢迎来到巨蟒和 Django 的世界;
您可以使用CORE作业运行Django命令来构建完整的用例。

快速说明:
在Python中,函数名称应该遵循PEP-8指南。您可以阅读有关PEP-8样式指南的更多信息
函数名称应为小写,并根据需要使用下划线分隔单词,以提高可读性。变量名遵循与函数名相同的约定。为了保持向后兼容性,只允许在已经是主流样式的上下文(例如threading.py)中使用MixedCase。
Drop使用database.py. Django项目可以使用Django的设置处理光标连接。DATABASES
我建议您阅读building your first Django app,它将指导您如何快速启动Django项目,以及使用manag.pysettings.py等背后的概念

相关问题