sqlalchemy - one-to-one relationship in two different fields of the same table with a different table

Question

I'm having this problem: I have two tables, one called Team and another called Event. The thing is, I want to have a one-to-one relationship between those two tables, but, in the Event table I want to have such relationship in two fields, one called home (for the home team) and another called away (from the away) team.

But, sqlalchemy is complaining about something that I'm doing wrong and I don't know what it is, the error is: «ArgumentError: Error creating backref 'Event' on relationship 'Event.away_team': property of that name exists on mapper 'Mapper|Team|bt_team'». What could I have been doing wrong? here is my models:

#!/usr/bin/env python2
# coding: utf-8
#                        VENI, SANCTE SPIRITUS

import sqlalchemy
import common_models
import datetime


class Team(common_models.Base):

    """ Database table for soccer teams
    """
    __tablename__ = "bt_team"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(30), unique=True)
    monitored_since = sqlalchemy.Column(
        sqlalchemy.DateTime, default=datetime.datetime.utcnow)

    # Huh, another field is not required?
    event_id = sqlalchemy.Column(
        sqlalchemy.Integer, sqlalchemy.ForeignKey("bt_event.id"))

    def __repr__(self):
        return "<Team {}>".format(self.name)


class Event(common_models.Base):

    """ Database table for the events
    """
    __tablename__ = "bt_event"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    home_team = sqlalchemy.orm.relationship(
        "Team", uselist=False, backref="Event")
    away_team = sqlalchemy.orm.relationship(
        "Team", uselist=False, backref="Event")
    # the "1" in the bet board
    home_wins = sqlalchemy.Column(sqlalchemy.String(7), nullable=True)
    # the "2" in the bet board
    away_wins = sqlalchemy.Column(sqlalchemy.String(7), nullable=True)
    # the "X" in the bet board
    draw = sqlalchemy.Column(sqlalchemy.String(7), nullable=True)
    # when will this event start? (and any bet can be made)
    event_date = sqlalchemy.Column(sqlalchemy.DateTime, nullable=True)
    # when was the last time this event was scraped?
    updated = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=True)

    def __repr__(self):
        return "<Event {} v {} ({} UK): 1 {} - X {} - 2 {}>".format(
            self.home_team.name, self.away_team.name,
            self.event_date.strftime("%d %B %H:%M"),
            self.home_wins, self.draw, self.away_wins)

    def get_match_teams(self):
        """ Return a string with the teams names which is the event name
        """
        return "{} v {}".format(self.home_team.name,
                                self.away_team.name)

and here is the full error:

E
======================================================================
ERROR: Get the bets for the next two soccer plays
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/nose/case.py", line 267, in setUp
    try_run(self.test, names)
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/nose/util.py", line 470, in try_run
    return func()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/tests/test_bets.py", line 46, in s
    team1 = betevent_models.Team(name="FK Krasnodar")
  File "<string>", line 2, in __init__
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 324, in _new_state_if_none                                                                                                                 
    state = self._state_constructor(instance, self)
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 725, in __get__                                                                                                                               
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 158, in _state_constructor                                                                                                                 
    self.dispatch.first_init(self, self.class_)
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 260, in __call__                                                                                                                                    
    fn(*args, **kw)
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2687, in _event_on_first_init                                                                                                                       
    configure_mappers()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2583, in configure_mappers                                                                                                                          
    mapper._post_configure_properties()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1688, in _post_configure_properties                                                                                                                 
    prop.init()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 144, in
 init                                                                                                                                    
    self.do_init()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1553, in do_init                                                                                                                             
    self._generate_backref()
  File "/home/jorge/coders/PPH/clientes/anton_b/app1-539916/codigo/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1743, in _generate_backref                                                                                                                   
    (backref_key, self, m))
ArgumentError: Error creating backref 'Event' on relationship 'Event.away_team': property of that name exists on mapper 'Mapper|Team|bt_team'

How can I achieve what I'm looking for?

EDIT: Seems like more appropriate to have a many-to-many relationship instead since a team can have N events and a event can have two teams.

Answer 1

this looks like your Event object just needs to refer to two Team objects, which would be two many to ones:

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Team(Base):
    __tablename__ = 'team'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class Event(Base):
    __tablename__ = 'event'

    id = Column(Integer, primary_key=True)
    home_id = Column(ForeignKey('team.id'))
    away_id = Column(ForeignKey('team.id'))

    home_team = relationship("Team", foreign_keys=home_id)
    away_team = relationship("Team", foreign_keys=away_id)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

t1, t2, t3, t4 = (
    Team(name='t1'), Team(name='t2'),
    Team(name='t3'), Team(name='t4')
)

sess.add_all([t1, t2, t3, t4])

sess.add_all([
    Event(home_team=t1, away_team=t2),
    Event(home_team=t3, away_team=t1),
    Event(home_team=t2, away_team=t4)
])

sess.commit()