Getting data to SQLite database in Django

Question

I'm new to Python and Django and I have ran into problems, I couldnt find answer to. I'm using Django 1.7 with SQLite3 and Python 3.4. I'm trying to make a sports league table, by getting data from a third party website and posting it to my own. So far I can get the needed data from the web. I have set up the tables to my SQLite db and can write the data, which I manually enter, to my page with django-tables2 app. The problem is that I cant figure out, how to automate my script so it could automatically write the data to the tables in SQLite.

Here is my models.py file:

from django.db import models
import django_tables2 as tables
from django.db import connection

class Ranking(models.Model):
    Team_name = models.CharField(max_length=25,unique=True)
    League = models.CharField(max_length=15,)
    League_points = models.CharField(max_length=3,)

# If I just add the rows from def te() to the end of the script and run the 
#server with manage.py runserver, it adds values "Team name", "First league", 
#"11" to the correct places, but closes the server instantly, because these 
#values have been added already.

    def te():
        cursor = connection.cursor()

        cursor.execute("""INSERT INTO EEtabel_ranking ('Team_name', 'League', 'League_points') VALUES (?, ?, ?)""", ("Team name", "First league", "11"))

    def __str__(self):

        return ' '.join([
            self.Summoner_name,
            self.League,
            self.League_points,
        ])

# This is for creating the table with data from database
class SimpleTable(tables.Table):
    class Meta:
        model = Ranking

Now, what I'd like to achieve, is to get the data to the table and update it once a day. So if visitors go to my page, they see the latest updated table and they dont trigger a new update. The updates should happen at fixed times.

Here is the script, which helps me to get data from the web:

from wsgi.openshift.myapp import MyAppClient  # MyApp is an app, which connects to the website, I'm making a connection to, to get data from there.

list_of_team_names = ['team1', 'team2', 'team3', 'etc']
client = MyAppClient.MyAppClient(key, "league_name")


team = client.search(list_of_team_names)
for x in list_of_team_names:
    print(team.name)
    league = client.league_data()
    print(league.tier)
    print(league.entries.leaguePoints)
    team = client.next()

#What this does, is that it searches the results of the teams in the "list_of_team_names".
#And gets the results one by one.

What the result looks like (an example): Team1 First League 34 Team2 First league 45 Team3 First league 10 etc.

So to sum up that horribly long story - my questions in short:

1)How to get the data to the SQLite database correctly?

2)How to make it so the data gets overwriten?

I mean that if I already have Team1 with 34 points in the table. and after recieving 3 points, my table wouldnt look like: Team1 34 pts, Team1 37 pts. It should replace the previous records.

3)How to get the data to update itself once a day (or hour or twice a day (custom time))?

I thank you in advance and hope that one day I'll get more experienced, so I wouldnt have to ask these kind of silly questions again. :P

Why are you using the raw connection to begin with? Use the ORM methods instead. — Colonel Thirty Two
– Colonel Thirty Two, Commented Dec 21, 2014 at 23:24
Yes, thank you. Lego here told me, how to use ORM, so I switched to that ;) — Jagulari
– Jagulari, Commented Dec 22, 2014 at 19:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

You are doing it wrong^TM

Platforms like Django take provide Object-relation mappers that allow you to abstract away direct interaction with the database and work with programming objects instead.

To be quite honest, I have no idea if you are writing the database query correctly, and the be brutally honest, I don't care. The Django-ORM exists for a very good reason, it makes things easier. Could you craft a raw database query to insert rows into a django databse, probably, but this is much easier:

from wsgi.openshift.myapp import MyAppClient

list_of_team_names = ['team1', 'team2', 'team3', 'etc']
client = MyAppClient.MyAppClient(key, "league_name")

team = client.search(list_of_team_names)
for x in list_of_team_names:
    ranking = Ranking(
      Team_name = team.name
      League = league.tier
      League_points = league.entries.leaguePoints
    )
    ranking.save()

models.py exists to define the objects you want to work with, so that by instantiating an object you prepare it for the database (which is why you need to explicitly call save in this case).

We can could instead create and save, like so:

ranking = Ranking.objects.create(
      Team_name = team.name
      League = league.tier
      League_points = league.entries.leaguePoints
    )

or, directly get an object, or create if none exists:

was_created, ranking = Ranking.objects.get_create(
      Team_name = team.name
      League = league.tier
      League_points = league.entries.leaguePoints
    )

Which is probably much shorter and clearer than the related SQL queries and code required to check if objects exists and return them.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 21, 2014 at 23:52

user764357

Sign up to request clarification or add additional context in comments.

3 Comments

Jagulari Over a year ago

Thank you, using ORM is the right thing to do! But now, how should I implement the code, you've written, to my app? It goes into my models.py file? Should it be inside the class Ranking? Should i put it inside a new def? And how to I run it, if I want to fill my table at will? Thank you!

Jagulari Over a year ago

Okay, thank you! I got it to work now. It scrolls through the list of the teams and enters the data from web to my database. I entered Legos script directly to models.py file and it starts as soon as I runserver. I'd like it not to start at that time, so it starts only if I tell it to run updates. any ideas? and also How can i make the new data to overwrite the old data? Thank you!

user764357 Over a year ago

If you put it into your models.py, then yes it will be run at server launch. Django has to initialise your models to work with them. I'd strongly recommend re-reading the Django Tutorial to understand how the framework works.

Collectives™ on Stack Overflow

Getting data to SQLite database in Django

1 Answer 1

You are doing it wrong^TM

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You are doing it wrongTM

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related

You are doing it wrong^TM