Columna calculada SQLAlchemy

calculated-columns

Question

(Nueva alerta de usuario de SQLAlchemy) Tengo tres tablas: una persona, la tarifa por hora de las personas que comienza en una fecha específica y el informe de la hora diaria. Estoy buscando la forma correcta de tener el costo de una base de tiempo fuera de la tarifa por hora de las personas ese día.

Sí, podría calcular el valor en el momento de la creación y tener eso como parte del modelo, pero pensar en esto como un ejemplo de resumen de datos más complejos detrás de la cortina. ¿Cómo calculo el Time.cost? ¿Es una propiedad híbrida, una propiedad de columna o algo completamente diferente?

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())

Answer 1

El problema que tienes aquí, para resolverlo de la manera más elegante posible, usa técnicas SQLAlchemy muy avanzadas, así que sé que eres un principiante, pero esta respuesta te mostrará todo el camino hasta el final. Sin embargo, resolver un problema como este requiere recorrer un paso a la vez, y puede obtener la respuesta que desea de diferentes maneras a medida que avanzamos.

Antes de entrar en cómo combinar esto o lo que sea, debe pensar en el SQL. ¿Cómo podemos consultar Time.cost sobre una serie arbitraria de filas? Podemos vincular el Time to Person limpiamente porque tenemos una clave externa simple. Pero vincular Time to Payrate con este esquema en particular es complicado, porque Time se vincula con Payrate no solo a través de person_id sino también a través de trabajado en. En SQL, nos uniríamos más fácilmente usando "time.person_id = person.id AND time. trabajado ENTRE payrate.start_date AND payrate.end_date ". Pero aquí no tiene una "fecha de finalización", lo que significa que también tenemos que derivar eso. Esa derivación es la parte más complicada, así que lo que se me ocurrió comienza de esta manera (he escrito en minúsculas los nombres de sus columnas):

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

Puede haber otras formas de obtener esto, pero eso es lo que se me ocurrió; otras formas casi seguramente tendrían que suceder algo similar (es decir, subconsultas, uniones).

Por lo tanto, con una tarifa de inicio / finalización, podemos averiguar cómo sería una consulta. Queremos usar BETWEEN para hacer coincidir una entrada de tiempo con el intervalo de fechas, pero la última entrada de la tasa de pago tendrá un valor NULL para la fecha "final", por lo que una forma de evitarlo es utilizar COALESCE en una fecha muy alta (la otra es usar condicionales):

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

Ahora, lo que @hybrid puede hacer por usted en SQLAlchemy, cuando se ejecuta en el nivel de expresión SQL, es exactamente la parte "entry.hours * payrate_derived.hourly", eso es todo. Todos los miembros de JOIN y demás allí, tendrían que proporcionar externamente al híbrido.

Así que tenemos que meter esa gran subconsulta en esto:

class Time(...):
@hybrid_property
def cost(self):
# ....
@cost.expression
def cost(cls):
return cls.hours * <SOMETHING>.hourly

Así que vamos a averiguar qué es <SOMETHING> . Construye ese SELECCIONAR como un objeto:

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()

El híbrido de cost() , en el lado de la expresión, debería referirse a payrate_derived (haremos el lado de python en un minuto):

class Time(...):
@hybrid_property
def cost(self):
# ....
@cost.expression
def cost(cls):
return cls.hours * payrate_derived.c.hourly

Luego, para utilizar nuestro híbrido de cost() , tendría que estar en el contexto de una consulta que tenga esa unión. Tenga en cuenta que aquí usamos datetime.date.max de Python para obtener esa fecha máxima (¡muy útil!):

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()

Así que la unión es grande, y klunky, y tendremos que hacerlo a menudo, sin mencionar que necesitaremos cargar esa misma colección en Python cuando hagamos nuestro híbrido en Python. Podemos mapearlo usando relationship() , lo que significa que tenemos que configurar condiciones de unión personalizadas, pero también necesitamos mapear a esa subconsulta, usando una técnica menos conocida llamada mapeador no primario. Un asignador no primario le proporciona una forma de asignar una clase a una tabla arbitraria o construcción SELECT solo con el propósito de seleccionar filas. Normalmente, nunca necesitamos usar esto porque Query ya nos permite consultar columnas y subconsultas arbitrarias, pero para salir de una relationship() necesita una asignación. El mapeo necesita una clave primaria para ser definida, y la relación también necesita saber qué lado de la relación es "extraño". Esta es la parte más avanzada aquí y en este caso funciona de la siguiente manera:

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
)
)
)
)

Así que eso es lo último que tendríamos que ver de esa unión. Ahora podemos hacer nuestra consulta antes como:

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

y, finalmente, también podemos conectar nuestra nueva relación de payrate al híbrido de nivel Python:

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 solución que tenemos aquí requirió mucho esfuerzo, pero al menos la parte más compleja, el mapeo de la tasa de nómina, está completamente en un solo lugar y nunca necesitamos volver a verla.

Aquí hay un ejemplo completo de trabajo:

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

Salida (la primera línea es la versión agregada, el resto es el por objeto):

[(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

Answer 2

Muchas veces el mejor consejo que puedo dar es simplemente hacerlo diferente. Una columna calculada de varias tablas como esta es para lo que son las views base de datos Cree una vista basada en la tabla de tiempos (o cualquier otra cosa que desee) con su columna calculada, cree un modelo basado en la vista y listo. Esto probablemente también será menos estresante en la base de datos. Este es también un buen ejemplo de por qué es peligroso limitar el diseño a lo que se puede lograr a través de migrations automatizadas.