Python: SQLAlchemy esempio m2m
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'>]
Commenti recenti