bancaldo™

Utilizzo SQLAlchemy ed il suo ORM per relazionare gli
oggetti Team, Player e Form.
Sono semplicemente gli oggetti rappresentanti una Squadra
di calcio (Team), i giocatori che formano la rosa (Player)
e le formazion che verranno schierate di volta in volta (Form).
Considerando di avere gli oggetti

Questo è lo “scheletro” per la relazione tra gli oggetti
Team, Player (one-to-many)
Team, Form (one-to-many)
Form, Player (many-to-many)

'''Data module for sql alchemy ORM'''

from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy import ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref
from sqlalchemy.orm import relation as relationship
from sqlalchemy.orm import sessionmaker


Base = declarative_base()
Engine = create_engine('sqlite:///provafanta.db', echo = False)
Metadata = Base.metadata
# m2m relationship
m2m_form_players = Table('m2m_form_players', Metadata,
                         Column('forms_id', Integer,
                                ForeignKey('forms.id')),
                         Column('players_id', Integer,
                                ForeignKey('players.id')))

class Player(Base):
    '''Player class for ORM Mapping'''
    __tablename__ = 'players'
    id = Column(Integer, primary_key = True)
    idgaz = Column(Integer)
    name = Column(String)

    team_id = Column(Integer, ForeignKey('teams.id'))
    form_id = Column(Integer, ForeignKey('forms.id'))
    
    def __init__(self, idgaz, name):
        self.idgaz = idgaz
        self.name = name
    def __repr__(self):
        return "<Player ('%s id: %s')>" % (self.name, self.idgaz)

class Team(Base):
    '''Team class for ORM Mapping. args: name'''
    __tablename__ = 'teams'
    id = Column(Integer, primary_key = True)
    name = Column(String)

    players = relationship("Player", backref = backref('Team'),
                           cascade = 'all,delete-orphan')
    forms = relationship("Form", backref = backref('Team'),
                         cascade = 'all,delete-orphan')
    
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return "<Team '%s'>" % (self.name)

class Form(Base):
    '''Daily-Team class for ORM Mapping'''
    __tablename__ = 'forms'
    id = Column(Integer, primary_key = True)
    desc = Column(String)

    team_id = Column(Integer, ForeignKey('teams.id'))
    # m2m relationship
    player_id = relationship("Player",
                             secondary = m2m_form_players,
                             backref = backref('Form'),
                             single_parent = True) 
    
    def __init__(self, desc):
        self.desc = desc
    def __repr__(self):
        return "<Formazione '%s'>" % (self.desc)

Metadata.create_all(Engine)
Session = sessionmaker(bind = Engine)
session = Session()

come da documentazione, per ottenere una relazione m2m, è
necessario appoggiarsi ad una tabella secondaria, per cui
definiamo una tabella che relazioni Form e Player:

m2m_form_players = Table('m2m_form_players', Metadata,
                         Column('forms_id', Integer,
                                ForeignKey('forms.id')),
                         Column('players_id', Integer,
                                ForeignKey('players.id')))

da richiamare all’interno della classe Form, puntando
all’id del Player che fa riferimento alla tabella seocndaria:

    player_id = relationship("Player",
                             secondary = m2m_form_players,
                             backref = backref('Form'),
                             single_parent = True,
                             cascade = 'all,delete-orphan') 

mentre nella classe Player, facciamo riferimento all’id
del Form come ForeignKey:

    form_id = Column(Integer, ForeignKey('forms.id'))

Per quello che riguarda la relazione one-to_many, tra
Team e Player e tra Team e Form, bastano semplicemente
i riferimenti alla ForeignKey nelle classi Player e
Form (relativamente a Team.id) e le relationship relative
nella classe Team

class Player(Base):
    ...
    team_id = Column(Integer, ForeignKey('teams.id'))
    ...
class Form(Base):
    ...
    team_id = Column(Integer, ForeignKey('teams.id'))
    ...
class Team(Base):
    ...
    players = relationship("Player", backref = backref('Team'),
                           cascade = 'all,delete-orphan')
    forms = relationship("Form", backref = backref('Team'),
                         cascade = 'all,delete-orphan')
    ...

Ho provato ad inserire quelche dato per verificare la correttezza
della struttura:

 
>>> team = Team(name = 'A')
>>> session.add(team)
>>> session.commit()
>>> team = session.query(Team).filter(Team.name == 'A').one()
>>> pl = Player(name = 'CESAR', idgaz = 101)
>>> pl
<Player ('CESAR id: 101')>
>>> session.add(pl)
>>> session.commit()
>>> pl.team_id = team.id
>>> t2 = Team(name='B')
>>> session.add(t2)
>>> session.commit()
>>> pl1 = Player(name = 'MOTTA', idgaz = 201)
>>> session.add(pl1)
>>> pl1.team_id = team.id
...
>>> player = Player(name = 'PAZZINI', idgaz = 805)
>>> session.add(player)
>>> session.commit()
>>> team = session.query(Team).filter(Team.name == 'A').one()
>>> player.team_id = team.id
>>> team.players
[<Player ('CESAR id: 101')>, <Player ('MOTTA id: 201')>, <Player ('MAICON id: 202')>, <Player ('PAZZINI id: 805')>]

Ora inserisco la formazione per la giornata 1

>>> form = Form(desc = 'A_1')
>>> session.add(form)
>>> form.team_id = team.id
>>> session.commit()
>>> form.Team
<Team 'A'>
>>> team.players
[<Player ('CESAR id: 101')>, <Player ('MOTTA id: 201')>, <Player ('MAICON id: 202')>]
>>> # aggiungo 2 player di team in formazione
>>> form.player_id.append(pl)
>>> form.player_id.append(pl1)
>>> session.commit()

facciamo un controllo per vie traverse

>>> f = session.query(Form).filter(Form.id == 1).one()
>>> f
<Formazione 'A_1'>
>>> f.player_id
[<Player ('CESAR id: 101')>, <Player ('MOTTA id: 201')>]
>>> for player in f.player_id: print player.name

CESAR
MOTTA
>>> ### aggiungo formazione
>>> 
>>> form = Form(desc = 'A_2')
>>> session.add(form)
>>> session.commit()
>>> team = session.query(Team).filter(Team.name == 'A').one()
>>> team.players
[<Player ('CESAR id: 101')>, <Player ('MOTTA id: 201')>, <Player ('MAICON id: 202')>, <Player ('PAZZINI id: 805')>]
>>> team.players[1:]
[<Player ('MOTTA id: 201')>, <Player ('MAICON id: 202')>, <Player ('PAZZINI id: 805')>]
>>> for player in team.players[1:]:
	form.player_id.append(player)

>>> session.commit()

controllo che funzioni la relazione m2m per vie traverse

>>> team
<Team 'A'>
>>> team.forms
[<Formazione 'A_1'>]
>>> form.team_id = team.id
>>> session.commit()
>>> team.forms
[<Formazione 'A_1'>, <Formazione 'A_2'>]
>>> f1 = team.forms[0]
>>> f1.player_id
[<Player ('CESAR id: 101')>, <Player ('MOTTA id: 201')>, <Player ('PAZZINI id: 805')>]
>>> f2 = team.forms[1]
>>> f2
<Formazione 'A_2'>
>>> f2.player_id
[<Player ('MOTTA id: 201')>, <Player ('MAICON id: 202')>, <Player ('PAZZINI id: 805')>]
>>> pazzo = f2.player_id[-1:][0]
>>> pazzo
<Player ('PAZZINI id: 805')>
>>> pazzo.Team
<Team 'A'>
>>> pazzo.Form
[<Formazione 'A_1'>, <Formazione 'A_2'>]
>>> pazzo.form_id
>>> pazzo.Form
[<Formazione 'A_1'>, <Formazione 'A_2'>]