lundi 26 octobre 2015

SQLAlchemy relationships to same table

I have declared following models:

from sqlalchemy import (
    Column,
    Table,
    Integer,
    Date,
    String,
    ForeignKey,
)

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

from sqlalchemy.orm import relationship, backref

engine = create_engine('sqlite:///data.sqlite')
DBSession = sessionmaker(bind=engine)

Base = declarative_base()


lineup = Table('lineups', Base.metadata,
               Column('match_id', Integer, ForeignKey('data.id')),
               Column('player_id', Integer, ForeignKey('players.id')))


class Match(Base):
    __tablename__ = 'data'

    id = Column(Integer, primary_key=True)
    date = Column(Date)
    tournament = Column(String)
    team1 = Column(String)
    team2 = Column(String)
    team1_lineup = relationship('Player', secondary=lineup)
    team2_lineup = relationship('Player', secondary=lineup)
    best_of = Column(Integer)
    maps = relationship('Map')
    score = Column(String)


class Map(Base):
    __tablename__ = 'maps'

    id = Column(Integer, primary_key=True)
    match = Column(Integer, ForeignKey('data.id'))
    name = Column(String)
    score = Column(String)


class Player(Base):
    __tablename__ = 'players'

    id = Column(Integer, primary_key=True)
    nickname = Column(String)
    team = Column(String)

And I'm creating new Match object this way:

match = Match(...) # all kwargs except team1_lineup and team2_lineup

p1 = Player(id=1, nickname='p1', team='team')
p2 = Player(id=2, nickname='p2', team='team')
p3 = Player(id=3, nickname='p2', team='team')

match.team1_lineup.append(p1)
match.team2_lineup.append(p2)
match.team2_lineup.append(p3)

After commiting new object, I'm querying it.

>>> from hltv.models import Match, DBSession
>>> s = DBSession()
>>> m = s.query(Match).first()
>>> m.team1_lineup
[<hltv.models.Player object at 0x7f1a93009d10>, <hltv.models.Player object at 0x7f1a93009d90>, <hltv.models.Player object at 0x7f1a93009e10>]
>>> m.team2_lineup
[<hltv.models.Player object at 0x7f1a93009d10>, <hltv.models.Player object at 0x7f1a93009d90>, <hltv.models.Player object at 0x7f1a93009e10>]

The problem is m.team1_lineup and m.team2_lineup are the same. How can I solve this? Additionally, how do I assign ID to every lineup (lineups with same players should have same ID)?

Aucun commentaire:

Enregistrer un commentaire