SQLAlchemy colonne calculée

(Nouveau SQLAlchemy alerte utilisateur) j'ai trois tables: une personne, les personnes taux horaire de départ à une date précise, et tous les jours des rapports de temps. Je suis à la recherche de la bonne façon d'avoir le coût d'une base de Temps hors de la personnes taux horaire de la journée.

Oui, je pourrais calculer la valeur au moment de la création et que, dans le cadre du modèle, mais pensez à cela comme un exemple de synthèse de données plus complexe derrière le rideau. Comment puis-je calculer le Temps.coût? Est-il un hybrid_propery, un column_property ou quelque chose de complètement différent?

class Person(Base):
    __tablename__ = 'person'
    personID = Column(Integer, primary_key=True)
    name = Column(String(30), unique=True)

class Payrate(Base):
    __tablename__ = 'payrate'
    payrateID = Column(Integer, primary_key=True)
    personID  = Column(Integer, ForeignKey('person.personID'))
    hourly    = Column(Integer)
    starting  = Column(Date)
    __tableargs__ =(UniqueConstraint('personID', 'starting',
                                     name='uc_peron_starting'))

class Time(Base):
    __tablename__ = 'entry'
    entryID  = Column(Integer, primary_key=True)
    personID = Column(Integer, ForeignKey('person.personID'))
    workedon = Column(Date)
    hours    = Column(Integer)

    person = relationship("Person")

    def __repr__(self):
        return "<{date} {hours}hrs ${0.cost:.02f}>".format(self, 
                      date=self.workedon.isoformat(), hours=to_hours(self.hours))

    @property
    def cost(self):
        '''Cost of entry
        '''
        ## This is where I am stuck in propery query creation
        return self.hours * query(Payrate).filter(
                             and_(Payrate.personID==personID,
                                  Payrate.starting<=workedon
                             ).order_by(
                               Payrate.starting.desc())

InformationsquelleAutor Frustrated | 2013-06-26

Le problème que vous avez ici, à résoudre élégamment que possible, utilise très avancé SQLAlchemy techniques, donc je sais que vous êtes un débutant, mais cette réponse va vous montrer tout le chemin à la fin. Cependant, la résolution d'un problème comme celui-ci exige de la marche à travers une étape à la fois, et vous pouvez obtenir la réponse que vous voulez de différentes manières que nous traversons.

Avant de vous lancer dans la façon de cet hybride ou autre, vous devez penser à la SQL. Comment pouvons-nous nous interroger pour le Moment.coût d'un arbitraire de la série de lignes? Nous pouvons lier le Temps à la Personne proprement parce que nous avons une simple clé étrangère. Mais pour lier le Temps de Payrate, avec ce schéma est un peu difficile, parce que le Temps des liens vers Payrate pas seulement via des person_id mais aussi via workedon - en SQL, nous aimerions participer à ce plus facilement à l'aide de "temps.person_id = la personne.l'id ET le temps.workedon ENTRE payrate.date_debut ET payrate.end_date". Mais vous n'avez pas de "end_date" ici, ce qui signifie que nous devons tirer aussi. Que la dérivation est la partie la plus délicate, de sorte que je suis venu avec commence comme ça (je l'ai mis en minuscule vos noms de colonne):

SELECT payrate.person_id, payrate.hourly, payrate.starting, ending.ending
FROM payrate LEFT OUTER JOIN
(SELECT pa1.payrate_id, MIN(pa2.starting) as ending FROM payrate AS pa1
JOIN payrate AS pa2 ON pa1.person_id = pa2.person_id AND pa2.starting > pa1.starting
GROUP BY pa1.payrate_id
) AS ending ON payrate.payrate_id=ending.payrate_id

Il pourrait y avoir d'autres façons de faire cela, mais c'est ce que je suis venu avec - d'autres manières serait presque certainement certains même genre de chose (c'est à dire les sous-requêtes, jointures).

Donc avec un payrate de début/fin, nous pouvons comprendre ce qu'est une requête devrait ressembler. Nous voulons utiliser ENTRE correspondre à un moment de l'entrée à la plage de date, mais la dernière payrate entrée aura la valeur NULL pour la "fin" de la date, donc une façon de contourner ce problème est d'utiliser FUSENT à l'encontre d'une très grande date (l'autre est d'utiliser le conditionnel):

SELECT *, entry.hours * payrate_derived.hourly
FROM entry
JOIN
    (SELECT payrate.person_id, payrate.hourly, payrate.starting, ending.ending
    FROM payrate LEFT OUTER JOIN
    (SELECT pa1.payrate_id, MIN(pa2.starting) as ending FROM payrate AS pa1
    JOIN payrate AS pa2 ON pa1.person_id = pa2.person_id AND pa2.starting > pa1.starting
    GROUP BY pa1.payrate_id
    ) AS ending ON payrate.payrate_id=ending.payrate_id) as payrate_derived
ON entry.workedon BETWEEN payrate_derived.starting AND COALESCE(payrate_derived.ending, "9999-12-31")
AND entry.person_id=payrate_derived.person_id
ORDER BY entry.person_id, entry.workedon

Maintenant ce que @hybride peut faire pour vous dans SQLAlchemy, lors de l'exécuter à l'expression SQL niveau, c'est exactement juste à l'entrée".heures * payrate_derived.horaire de la partie", c'est tout. Toutes les JOINDRE et il, vous aurez besoin de fournir à l'extérieur de l'hybride.

Nous avons donc besoin de bâton que de gros sous-requête dans ce:

class Time(...):
    @hybrid_property
    def cost(self):
        # ....

    @cost.expression
    def cost(cls):
        return cls.hours * <SOMETHING>.hourly

Donc, essayons de comprendre ce que <SOMETHING> est. L'accumulation de SÉLECTIONNER un objet:

from sqlalchemy.orm import aliased, join, outerjoin
from sqlalchemy import and_, func

pa1 = aliased(Payrate)
pa2 = aliased(Payrate)
ending = select([pa1.payrate_id, func.min(pa2.starting).label('ending')]).\
            select_from(join(pa1, pa2, and_(pa1.person_id == pa2.person_id, pa2.starting > pa1.starting))).\
            group_by(pa1.payrate_id).alias()

payrate_derived = select([Payrate.person_id, Payrate.hourly, Payrate.starting, ending.c.ending]).\
    select_from(outerjoin(Payrate, ending, Payrate.payrate_id == ending.c.payrate_id)).alias()

La cost() hybride, sur l'expression de côté, il serait nécessaire de se référer à payrate_derived (nous ferons le python côté dans une minute):

class Time(...):
    @hybrid_property
    def cost(self):
        # ....

    @cost.expression
    def cost(cls):
        return cls.hours * payrate_derived.c.hourly

Ensuite, pour utiliser notre cost() hybride, il devrait être dans le contexte d'une requête qui a cette jointure. Remarque ici, nous utilisons Python datetime.date.max pour obtenir que max date (pratique!):

print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
                    select_from(Time).\
                    join(Time.person).\
                    join(payrate_derived,
                            and_(
                                payrate_derived.c.person_id == Time.person_id,
                                Time.workedon.between(
                                    payrate_derived.c.starting,
                                    func.coalesce(
                                        payrate_derived.c.ending,
                                        datetime.date.max
                                    )
                                )
                            )
                    ).\
                    all()

De sorte que la jointure est grand, et klunky, et nous avons besoin de le faire souvent, pour ne pas mentionner que nous allons avoir besoin de charger cette même collection en Python lorsque nous faisons de notre en-Python hybride. Nous pouvons la carte à l'aide de relationship(), ce qui signifie que nous avons à mettre en place des conditions de jointure, mais nous avons aussi besoin de la carte pour que la sous-requête, à l'aide d'un moins connus technique appelée non-primaire mappeur. Un non-primaire mapper vous permet de mapper une classe à l'arbitraire de la table ou de SÉLECTIONNER construire juste pour les fins de la sélection de lignes. Nous avons l'habitude de ne jamais avoir besoin d'utiliser cette Requête étant déjà nous permet de requête pour arbitraire, les colonnes et les sous-requêtes, mais pour sortir d'un relationship() il a besoin d'une cartographie. La cartographie a besoin d'une clé primaire à définir, et la relation a aussi besoin de savoir de quel côté de la relation est "étranger". C'est la partie la plus avancée ici et dans ce cas, il fonctionne comme ceci:

from sqlalchemy.orm import mapper, relationship, foreign

payrate_derived_mapping = mapper(Payrate, payrate_derived, non_primary=True,
                                        primary_key=[
                                            payrate_derived.c.person_id,
                                            payrate_derived.c.starting
                                        ])
Time.payrate = relationship(
                    payrate_derived_mapping,
                    viewonly=True,
                    uselist=False,
                    primaryjoin=and_(
                            payrate_derived.c.person_id == foreign(Time.person_id),
                            Time.workedon.between(
                                payrate_derived.c.starting,
                                func.coalesce(
                                    payrate_derived.c.ending,
                                    datetime.date.max
                                )
                            )
                        )
                    )

De sorte que c'est la dernière que nous aurions du voir de cette jointure. Nous pouvons maintenant faire notre requête plus tôt que:

print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
                    select_from(Time).\
                    join(Time.person).\
                    join(Time.payrate).\
                    all()

et, enfin, nous pouvons fil de notre nouveau payrate relation dans le Python niveau de l'hybride ainsi:

class Time(Base):
    # ...

    @hybrid_property
    def cost(self):
        return self.hours * self.payrate.hourly

    @cost.expression
    def cost(cls):
        return cls.hours * payrate_derived.c.hourly

La solution que nous avons ici pris beaucoup d'efforts, mais au moins la partie la plus complexe, qui payrate de cartographie, est entièrement en un seul endroit et nous n'avons jamais besoin de le regarder de nouveau.

Ici un exemple de travail:

from sqlalchemy import create_engine, Column, Integer, ForeignKey, Date, \
UniqueConstraint, select, func, and_, String
from sqlalchemy.orm import join, outerjoin, relationship, Session, \
aliased, mapper, foreign
from sqlalchemy.ext.declarative import declarative_base
import datetime
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
person_id = Column(Integer, primary_key=True)
name = Column(String(30), unique=True)
class Payrate(Base):
__tablename__ = 'payrate'
payrate_id = Column(Integer, primary_key=True)
person_id  = Column(Integer, ForeignKey('person.person_id'))
hourly    = Column(Integer)
starting  = Column(Date)
person = relationship("Person")
__tableargs__ =(UniqueConstraint('person_id', 'starting',
name='uc_peron_starting'))
class Time(Base):
__tablename__ = 'entry'
entry_id  = Column(Integer, primary_key=True)
person_id = Column(Integer, ForeignKey('person.person_id'))
workedon = Column(Date)
hours    = Column(Integer)
person = relationship("Person")
@hybrid_property
def cost(self):
return self.hours * self.payrate.hourly
@cost.expression
def cost(cls):
return cls.hours * payrate_derived.c.hourly
pa1 = aliased(Payrate)
pa2 = aliased(Payrate)
ending = select([pa1.payrate_id, func.min(pa2.starting).label('ending')]).\
select_from(join(pa1, pa2, and_(
pa1.person_id == pa2.person_id,
pa2.starting > pa1.starting))).\
group_by(pa1.payrate_id).alias()
payrate_derived = select([Payrate.person_id, Payrate.hourly, Payrate.starting, ending.c.ending]).\
select_from(outerjoin(Payrate, ending, Payrate.payrate_id == ending.c.payrate_id)).alias()
payrate_derived_mapping = mapper(Payrate, payrate_derived, non_primary=True,
primary_key=[
payrate_derived.c.person_id,
payrate_derived.c.starting
])
Time.payrate = relationship(
payrate_derived_mapping,
viewonly=True,
uselist=False,
primaryjoin=and_(
payrate_derived.c.person_id == foreign(Time.person_id),
Time.workedon.between(
payrate_derived.c.starting,
func.coalesce(
payrate_derived.c.ending,
datetime.date.max
)
)
)
)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=False)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e)
p1 = Person(name='p1')
session.add(p1)
session.add_all([
Payrate(hourly=10, starting=datetime.date(2013, 5, 17), person=p1),
Payrate(hourly=15, starting=datetime.date(2013, 5, 25), person=p1),
Payrate(hourly=20, starting=datetime.date(2013, 6, 10), person=p1),
])
session.add_all([
Time(person=p1, workedon=datetime.date(2013, 5, 19), hours=10),
Time(person=p1, workedon=datetime.date(2013, 5, 27), hours=5),
Time(person=p1, workedon=datetime.date(2013, 5, 30), hours=5),
Time(person=p1, workedon=datetime.date(2013, 6, 18), hours=12),
])
session.commit()
print session.query(Person.name, Time.workedon, Time.hours, Time.cost).\
select_from(Time).\
join(Time.person).\
join(Time.payrate).\
all()
for time in session.query(Time):
print time.person.name, time.workedon, time.hours, time.payrate.hourly, time.cost

De sortie (la première ligne est la version agrégée, le reste est de la par objet):

[(u'p1', datetime.date(2013, 5, 19), 10, 100), (u'p1', datetime.date(2013, 5, 27), 5, 75), (u'p1', datetime.date(2013, 5, 30), 5, 75), (u'p1', datetime.date(2013, 6, 18), 12, 240)]
p1 2013-05-19 10 10 100
p1 2013-05-27 5 15 75
p1 2013-05-30 5 15 75
p1 2013-06-18 12 20 240

"Wow!", c'est tout ce que je peux dire.
si vous mettez un end_date sur votre Payrate moitié de table le code ici s'en va.
J'étais également à la recherche à l'aide de starting <= workedon order by starting DESC limit 1
Je préfère de beaucoup de votre explication, comme le générique de plain-pied grâce à des solutions sur le masquage de la complexité. Très bien fait. J'ai juste besoin de finir la lecture sur les différentes parties de SQLAlchemy vous toucher.

InformationsquelleAutor zzzeek

0

De nombreuses fois, le meilleur conseil que je puisse donner est de simplement faire de différent. Un multi-table colonne calculée comme c'est ce que la base de données vues sont pour. Construire une vue basée sur la table de Temps (que vous voulez) avec votre colonne calculée en elle, de construire un modèle basé sur la vue, et vous êtes fixés. Ce sera certainement moins stressant sur la base de données. C'est aussi un bon exemple de pourquoi la limitation de la conception de ce qui peut être accompli par l'automatisation des les migrations est dangereux.

InformationsquelleAutor juanitogan

Vous devez vous connecter pour publier un commentaire.