Copy data between tables

Using Django Using the ORM
1

I have a queryset created by objects.filter() and joins 2 tables.
How do I then save this result to a different table?

Below is the example I am trying to work on. The queryset filter is working as expected.
I am now lost on how to write some of the fields into a different table and would welcome some guidance.

Models.py

#holds the race info
class Race(models.Model):
    event = models.ForeignKey(
            'Event',
            related_name = 'race_in_event',
            on_delete=models.PROTECT,
        )
    stage_number = models.IntegerField()
    schedule = models.ForeignKey(
            'Schedule',
            related_name='race_schedule',
            on_delete=models.PROTECT,
            blank=True, null=True,
        )
    race_number = models.IntegerField()
    sort_order = models.IntegerField()
    group = models.IntegerField()
    race_status = models.CharField(
            max_length=3,
            choices=type_choices,
            )

#updated during racing, holds live position
class RaceData(models.Model):
    race = models.ForeignKey(
        'Race',
        related_name = 'racedata_in_race',
        on_delete=models.PROTECT,
    )
    flight = models.ForeignKey(
        Flight,
        related_name = 'flight_in_racedata',
        on_delete=models.PROTECT,
    )
    boat = models.ForeignKey(
        Boat,
        related_name = 'boat_in_racedata',
        on_delete=models.PROTECT,
    )
    timestamp = models.DateTimeField(auto_now_add=True)
    event_type = models.CharField(
            max_length=3,
            choices=type_choices,
            )
    position=models.IntegerField(blank=True, null=True)
    notes=models.TextField(blank=True, null=True)

#a point in time snapshot of race positions generated after finish
class RaceScores(models.Model):
    race = models.ForeignKey(
        'Race',
        related_name = 'score_for_race',
        on_delete=models.PROTECT
    )
    boat = models.ForeignKey(
        'Boat',
        related_name = 'score_for_boat',
        on_delete=models.PROTECT
    )
    flight = models.ForeignKey(
        'Flight',
        related_name='score_for_flight',
        on_delete=models.PROTECT
    )
    team = models.ForeignKey(
        'Team',
        related_name='score_for_team',
        on_delete=models.PROTECT
    )
    position = models.IntegerField()
    scoring_code = models.CharField(max_length=25, blank=True)

views.py

# for the specified event_id get all the race where status=fin and get all related race_data

racelist = Race.objects.filter(event_id=event_id, race_status='fin').prefetch_related('racedata_in_race').all()

# with this result set, write race.id, racedata.position, racedata.boat_id, racedata.flight_id to the RaceScores table

Many thanks
Paul

2

You would do this the same way you’d create any other object.

The ORM provides no direct facilities for copying data from one model to another. Writing the data to a different model consists of the same steps - create the instances of the object and then save them. (The bulk_create function is available if that is going to be of any use to you.)

3

Thanks for the quick response.

I’ve added the iteration loop but I’m struggling to find how to reference the related fields:

Version a:

    for res in racelist:
        raceScoresNew = RaceScores(race_id=res.event_id, position=res.position, boat_id=res.boat_id, flight_id=res.flight_id)
        raceScoresNew.save()

Version b:

    for res in racelist:
        raceScoresNew = RaceScores(race_id=res.event_id, position=res.racedata_in_race.position, boat_id=res.racedata_in_race.boat_id, flight_id=res.racedata_in_race.flight_id)
        raceScoresNew.save()

I’d welcome some guidance on how to reference these related fields within the query.

I’ve opted not to go for the bulk_update() method as it’s likely I will need to do some error handling.

Many thanks
Paul

4

I’m sorry, I’m not following what you’re trying to ask here.

What related fields in which query?

Or, to phrase my question a different way, for each instance of RaceScore that you’re trying to create, what is the source of the data for each field?

5

I have assumed (possibly incorrectly) that this command creates a JOIN on the 2 tables selecting all the records in the RaceData table which match those in the Race table using the relationship on Race.id (racedata_in_race).

racelist = Race.objects.filter(event_id=event_id, race_status='fin').prefetch_related('racedata_in_race').all()

I would then like to iterate through each record in the joined tables
In the below code race_id comes from the Race table but position, boat_id, flight_id come from the RaceData table.

for res in racelist:
        raceScoresNew = RaceScores(race_id=res.race_id, position=res.position, boat_id=res.boat_id, flight_id=res.flight_id)

res.race_id does return the expected value but the other fields appear not to exist within the recordset with this naming convention.
My question is how do I refer to these fields to get their values - this is of course assuming that I have completed the initial table join correctly.

Many thanks

Paul

6

That is not correct. See prefetch_related for the differences between it and select_related.

Also I want to emphasize that neither of those two functions (select_related and prefetch_related) are required for accessing related entities - they are performance enhancements only.

But, neither of those factors above are directly relevent to addressing your question.

The first issue here is that because of the foreign key relationship between RaceData and Race, for any individual Race instance, you may have multiple related RaceData instances.

That means for each Race, you need to iterate through that set to retrieve the data you want, and this generally means a nested loop - unless you have some specific attribute on which you can filter for specific instances of RaceData.
e.g.

for res in racelist:
    for data in res.racedata_in_race.all():
        # do something with the RaceData for a particular instance of Race

Also see Related objects and Related objects reference

7

Thanks for your help.

The documentation is confusing around creating a join on relational objects (although technically correct) and whilst the select_related and prefetch_related create a SQL join, they don’t then return that into the queryset - something that I see has caused confusion for many moving to Django from other frameworks.

The key information for anyone else is that to join 2 tables is the need to query the first table and then iterate through each row querying the related table. The results can then be stored into a queryset and used as required.

8

Again, prefetch_related does not create an SQL join between the related tables. It causes a second query to be executed, and then performs the object association within Python.

This is only true if you’re following a foreign key relationship “backward” (from the “one” side of the one-to-many relationship) or if you’re looking for the related members of a many-to-many relationship. If the base object that you are querying contains the foreign key, or if you are working with a one-to-one relationship, then you can access those related objects directly.