python - Assigning data to django model from CSV from iterator

Question

I'm reading info in from a CSV to my django model, but it keeps throwing an ValueError: Cannot assign "'Sheffield United'": "Match.home_team" must be a "Team" instance. I can add data fine in the admin interface (perhaps obviously), but trying to do it programmatically gives me that error.

I had the same issue with 'League', and commented it out just to test - both the league ("E2") object and Team 'Sheffield United' exist in the database, as I added them to test that.

I then changed them to, for example home_team = Team.objects.get(id=row[2]) as per this answer.. I think that may have solved the initial problem, but I now get: ValueError: invalid literal for int() with base 10: 'Sheffield United', which is baffling as it's a string.

Models.py:

class League (models.Model):
    name = models.CharField(max_length=2)
    last_modified = models.CharField(max_length=50)
    def __unicode__(self):
        return unicode(self.name)

class Team(models.Model):
    team_name = models.CharField(max_length=50)
    league = models.ForeignKey(League)
    team_colour = models.CharField(max_length=6, null=True, blank=True)
    def __unicode__(self):
        return unicode (self.team_name)

class Match(models.Model):
    RESULT_CHOICES = (
        ('H', 'Home'),
        ('D', 'Draw'),
        ('A', 'Away'))
    league = models.ForeignKey(League)
    match_date = models.DateTimeField()
    home_team = models.ForeignKey(Team)
    away_team = models.CharField(max_length=50)
    full_time_home_goals = models.PositiveSmallIntegerField(blank=True, null=True)
    full_time_away_goals = models.PositiveSmallIntegerField(blank=True, null=True)
    full_time_result = models.CharField(max_length=1, choices=RESULT_CHOICES, blank=True, null=True)
    half_time_home_goals = models.PositiveSmallIntegerField(blank=True, null=True)
    half_time_away_goals = models.PositiveSmallIntegerField(blank=True, null=True)
    half_time_result = models.CharField(max_length=1, choices=RESULT_CHOICES,blank=True, null=True)
    home_shots = models.PositiveSmallIntegerField(blank=True, null=True)
    away_shots = models.PositiveSmallIntegerField(blank=True, null=True)
    home_shots_on_target = models.PositiveSmallIntegerField(blank=True, null=True)
    away_shots_on_target = models.PositiveSmallIntegerField(blank=True, null=True)
    home_corners = models.PositiveSmallIntegerField(blank=True, null=True)
    away_corners = models.PositiveSmallIntegerField(blank=True, null=True)
    home_yellow = models.PositiveSmallIntegerField(blank=True, null=True)
    away_yellow = models.PositiveSmallIntegerField(blank=True, null=True)
    home_red = models.PositiveSmallIntegerField(blank=True, null=True)
    away_red = models.PositiveSmallIntegerField(blank=True, null=True)
    def __unicode__(self):
        return unicode(self.home_team) + " v " + unicode(self.away_team) + " " + unicode(self.match_date)
    class Meta:
        verbose_name_plural = "Matches"

The management command that I'm using is: (right now I'm running it from the commandline to debug it, so have stripped the BaseCommand subclassing out of this code - it didn't affect the error I was seeing.)

from django.core.management.base import BaseCommand, CommandError
import csv
import csvImporter
from core.models import Match

master_data = open ('/Users/chris/Dropbox/Django/gmblnew/data/testfile.csv', 'r') 
data = list(tuple(rec) for rec in csv.reader(master_data, delimiter=','))
from core.models import Match, League

for row in data:
    current_match = Match(
        league=row[0],
        match_date = row[1], 
        home_team = row[2],
        away_team = row[3],
        full_time_home_goals = row[4],
        full_time_away_goals = row[5],
        home_shots = row[10],
        away_shots = row[11],
        home_shots_on_target = row[12],
        away_shots_on_target = row[13],
        home_corners = row[16],
        away_corners = row[17],
        full_time_result = row[6],
    )
    print current_match 

Original traceback (on 'must be an instance' error:)

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 453, in execute_from_command_line
    utility.execute()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 272, in fetch_command
    klass = load_command_class(app_name, subcommand)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 77, in load_command_class
    module = import_module('%s.management.commands.%s' % (app_name, name))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/utils/importlib.py", line 35, in import_module
    __import__(name)
  File "/Users/chris/Dropbox/Django/gmblnew/core/management/commands/ImportCSV.py", line 24, in <module>
    full_time_result = row[6],
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/base.py", line 403, in __init__
    setattr(self, field.name, rel_obj)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/fields/related.py", line 405, in __set__
    self.field.name, self.field.rel.to._meta.object_name))
ValueError: Cannot assign "'Sheffield United'": "Match.home_team" must be a "Team" instance.

Most recent traceback:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 453, in execute_from_command_line
    utility.execute()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 272, in fetch_command
    klass = load_command_class(app_name, subcommand)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/core/management/__init__.py", line 77, in load_command_class
    module = import_module('%s.management.commands.%s' % (app_name, name))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/utils/importlib.py", line 35, in import_module
    __import__(name)
  File "/Users/chris/Dropbox/Django/gmblnew/core/management/commands/ImportCSV.py", line 14, in <module>
    home_team = Team.objects.get(id=row[2]),
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/manager.py", line 143, in get
    return self.get_query_set().get(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 379, in get
    clone = self.filter(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 655, in filter
    return self._filter_or_exclude(False, *args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 673, in _filter_or_exclude
    clone.query.add_q(Q(*args, **kwargs))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1266, in add_q
    can_reuse=used_aliases, force_having=force_having)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1197, in add_filter
    connector)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/where.py", line 71, in add
    value = obj.prepare(lookup_type, value)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/where.py", line 339, in prepare
    return self.field.get_prep_lookup(lookup_type, value)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/fields/__init__.py", line 322, in get_prep_lookup
    return self.get_prep_value(value)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/fields/__init__.py", line 555, in get_prep_value
    return int(value)
ValueError: invalid literal for int() with base 10: 'Sheffield United'

At the moment, I'm reading in some initial data for testing, but manipulating CSVs into the database is something that I'll be doing regularly, so some guidance would be appreciated. (I've had a look at a couple of CSVImporter tools - right now, I don't want to use them, as I want to understand the guts of what I'm doing, and I feel like what I've written should suffice, if I can get past this problem.)

Answer 1

Since home_team is a ForeignKey, it can only accept instances of that model; you are trying to pass it a string which is the name of the home team, that's what this error means:

ValueError: Cannot assign "'Sheffield United'": "Match.home_team" must be a "Team" instance.

In your importer script, you need to search for the object that represents the home team, and assign that as the foreign key. You can use get_or_create to either fetch an existing team, or create a new team for the team name; like this:

from django.core.management.base import BaseCommand, CommandError
import csv
import csvImporter
from core.models import Match

master_data = open ('/Users/chris/Dropbox/Django/gmblnew/data/testfile.csv', 'r') 
data = list(tuple(rec) for rec in csv.reader(master_data, delimiter=','))
from core.models import Match, League, Team

for row in data:
    league, _ = League.objects.get_or_create(name=row[0])
    home_team, _ = Team.objects.get_or_create(team_name=row[2], league=league)
    away_team, _ = Team.objects.get_or_create(team_name=row[3], league=league)
    current_match = Match(
        league = league,
        home_team = home_team,
        away_team = away_team,
        match_date = row[1], 
        full_time_home_goals = row[4],
        full_time_away_goals = row[5],
        home_shots = row[10],
        away_shots = row[11],
        home_shots_on_target = row[12],
        away_shots_on_target = row[13],
        home_corners = row[16],
        away_corners = row[17],
        full_time_result = row[6],
    )
    print current_match

This line Team.objects.get_or_create(team_name=row[2]) means:

"Try to get a Team object whose team_name is the same as the value for row[2], if it doesn't exist, create a new Team object and return it instead"

get_or_create will return a 2-tuple, and the second part is a boolean to tell you if a new item was created or an existing item retrieved. Since we are only interested in the first part, I updated the code to only use the instance and ignore the second value.