One to Many Link between tables : Forums : PythonAnywhere

One to Many Link between tables

Hi Guys,

I'm really struggling with this:

I'm writing an application where there is a python script running separately to the Django web app. I am able to write to the Django Mysql DB via ORM - no problem there. Eventually this script will be scheduled.

The issue is that there are three tables: event, sessions, results, in the database, and I need to setup a one to many relationship. I have specified foreignkey fields in models.py but on retrieving the data it only returns the one record.

i.e.

A single event ---> Multiple Sessions ---> Multiple Results

I need a way to write the event id, to multiple sessions and session to results so that they are all linked. I feel that I need to retrieve the ID from the database in my script and then write that back into the next table. No matter what I do I cant get it to find the id and write it properly.

I think if I was using a Django view or template it would work but since im not - i'm jsut not sure how to achieve this.

Hopefully the below helps to explain it better.

Event

id            Url                   title                     date
1            www.            Test Event         22/01/18
2            www.             Event 2              24/02/18

Sessions

id        url               date                       name            time      sessclass            eventid_id
1        www.         22/01/18             test               2:00       qualify                  (**Should be Event table id field) e.g. 1
2        www.        22/01/18              test               3:00       heat1                   Same date so event id should be 1
3       www.        24/02/18                test2           9:00       qualify                 Event ID filed should be 2

I cant use the date field, because an event might run over multiple dates, so I need to write the ID into the Sessions table as a part of the loop. I've pasted my code below

Independent Script File:

tk_events = sh.OrgEvents()


for org_event in tk_events:
    events = sh.Events(org_event['url'])
    print ('Event: ' + org_event['title'])
    try:
        db = dbevent.objects.create(url=org_event['url'], title=org_event['title'], date=org_event['date'])

    except Exception as e:
        pass
    for event in events:
        for sessions in event:
            print ('-> Category: ' + sessions['title'])
            for session in sessions['session']:
                print ('    -> Session: ' + session['name'])
                #try:
                db = dbsessions.objects.create(url=session['url'],sessclass=sessions['title'],name=session['name'],date=session['date'],time=session['time'])
                #except Exception as e:
                    #pass
                    #db = dbsessions.objects.filter(url=session['url']).update(eventid_id=evtid['evtid'])
                    #for result in sh.Session(session['url']):
                    #resultindex = db.event.create_index("_id", unique=True)
                    #dbpost = db.result.insert_one(result)

Models.py

from django.db import models

class dbevent(models.Model):
    url = models.CharField(max_length=250, unique=True)
    title = models.CharField(max_length=250)
    date = models.CharField(max_length=50)

    class Meta:
        unique_together = ("url", "title")

    def __str__(self):
        """String for representing the Model object."""
        return '%s %s' % (self.title, self.date)



class dbsessions(models.Model):
    eventid = models.ForeignKey(dbevent, on_delete=models.CASCADE)
    url = models.CharField(max_length=250)
    date = models.CharField(max_length=50)
    name = models.CharField(max_length=250)
    time = models.CharField(max_length=50)
    sessclass = models.CharField(max_length=250)

    class Meta:
        unique_together = ("url", "sessclass")

class dbresults(models.Model):
    sessionid = models.ForeignKey(dbsessions, on_delete=models.CASCADE)
    bestlap = models.CharField(max_length=5)
    besttime = models.CharField(max_length=20)
    competitorname = models.CharField(max_length=250)
    diff = models.CharField(max_length=20)
    laps = models.CharField(max_length=20)
    position = models.CharField(max_length=20)
    racenumber = models.PositiveSmallIntegerField
    topspeed = models.CharField(max_length=20)
    totaltime = models.CharField(max_length=20)
    winner = models.BooleanField

[edit by admin: formatting]

deleted-user-4332250 | 4 posts | Aug. 22, 2018, 8:54 a.m. | permalink

Ok, So I think I see where I was going wrong.

So now I'm just trying to follow the Foreignkey in the views.py, from the Python Shell however if I get no results when trying this:, I know there is data in the dbresults table and url field.

The eventid field in dbsessions in the database is null, should this have something in it?

>>> session = dbevent.objects.filter(pk=1).values('dbsessions__url')                                                                                                                                              
>>> print (session)
<QuerySet [{'dbsessions__url': None}]>

based on models.py

from django.db import models

class dbevent(models.Model):
    url = models.CharField(max_length=250, unique=True)
    title = models.CharField(max_length=250)
    date = models.CharField(max_length=50)

    class Meta:
        unique_together = ("url", "title")

    def __str__(self):
        """String for representing the Model object."""
        return '%s %s' % (self.title, self.date)



class dbsessions(models.Model):
    eventid = models.ForeignKey(dbevent, on_delete=models.CASCADE, null=True)
    url = models.CharField(max_length=250)
    date = models.CharField(max_length=50)
    name = models.CharField(max_length=250)
    time = models.CharField(max_length=50)
    sessclass = models.CharField(max_length=250)

    class Meta:
        unique_together = ("url", "sessclass")

class dbresults(models.Model):
    sessionid = models.ForeignKey(dbsessions, on_delete=models.CASCADE, null=True)
    bestlap = models.CharField(max_length=5)
    besttime = models.CharField(max_length=20)
    competitorname = models.CharField(max_length=250)
    diff = models.CharField(max_length=20)
    laps = models.CharField(max_length=20)
    position = models.CharField(max_length=20)
    racenumber = models.PositiveSmallIntegerField
    topspeed = models.CharField(max_length=20)
    totaltime = models.CharField(max_length=20)
    winner = models.BooleanField

[edit by admin: formatting]

deleted-user-4332250 | 4 posts | Aug. 23, 2018, 2:27 a.m. | permalink

Django has pretty good built-in support for following foreign keys "backwards" like that. For example, if you have an event and you want to get all associated sessions via the model you've posted above, you can use session_set. Here's some code you might use:

event = dbevent.objects.get(id=1)
for session in event.session_set.all():
    # do something with the session
    for result in session.result_set.all():
        # do something with the result

A couple of stylistic suggestions:

The normal Django pattern is that you don't put "id" in the ForeignKey relationship -- that is, instead of having, for example,

sessionid = models.ForeignKey(dbsessions, on_delete=models.CASCADE, null=True)

you would have

session = models.ForeignKey(dbsessions, on_delete=models.CASCADE, null=True)

It's also good practice in Python to give classes MixedCase names -- so, DbSessions rather than dbsessions.

BTW this Django help page on many-to-one relationships is really good.

giles | 12087 posts | PythonAnywhere staff | Aug. 24, 2018, 11:14 a.m. | permalink

I feel I should add that I have PythonAnywhere Tasks to manage my Django app, and they are all calls to custom Django commands. See here:

https://docs.djangoproject.com/en/2.1/howto/custom-management-commands/

An example of how I do this is in tasks something like:

/home/nverse/nvtest3/nvlive1/test_topup_sysdb_from_splits_generic.sh

The bash script might look like:

#!/bin/bash
echo Topping up Test Generic Starsystems
cd /home/nverse/nvtest3/nvlive1/
/home/nverse/.virtualenvs/nvtest/bin/python manage.py topup_from_splits generic
echo Done

/home/nverse/nvtest3/nvlive1/ is the root of my django project, and... /home/nverse/.virtualenvs/nvtest/bin/python is my virtualenv.

and the Django command something like:

from django.core.management.base import BaseCommand
from gm import db_tools

class Command(BaseCommand):
  help = 'Generate systems from split csvs and populate database'

  def add_arguments(self, parser):
      parser.add_argument('system_type',  type=str)

  def handle(self, *args, **options):
      out = ''
      systype = options['system_type']
      # message = "No Systems"

      out = db_tools.topup_from_splits(systype)

      self.stdout.write(self.style.SUCCESS(out))

This ensures any database related stuff, which is in the imported db_tools behaves as it would in a template or view. I find this is the best way to manage scheduled tasks that work with a Django app. Just posting this because I had many problems similar to the op before I standardised maintaining my Django apps in PA like this.

nverse | 75 posts | Aug. 26, 2018, 6:25 p.m. | permalink