在以下设置中,如何从所有汽车轮胎中检索一组SensorData?
汽车有很多轮胎,轮胎有一个SensorData,因此在SQL中,我会做类似
SELECT sd.*
FROM Car AS c
INNER JOIN Tire AS t ON c.id = t.car_id
INNER JOIN SensorData AS sd ON t.id = sd.tire_id
class Car(Base):
pass
class Tire(Base):
car = relationship("Car", backref="tires")
class SensorData(Base):
tire = relationship("Tire", backref=backref("sensor_data", uselist=False))
我猜想有一种比以下方法更优雅的方法来获取所有SensorData,但我找不到它。
tires = Car.query.get(1).tires
sensor_datas = [sensor_data for tire.sensor_data in tires]
谁能建议更好的方法?我在想我应该能够做类似的事情
sensor_datas = Car.query.get(1).sensor_datas
OR
sensor_datas = Car.query.get(1).tires.sensor_data
最佳答案
这些解决方案将在sensor_datas
上返回只读的Car
属性,有关更多信息,请参见注释:
class Car(Base):
id = Column(Integer, primary_key=True)
# results in a collection of sensor_data objects. This method is the
# cleanest reading by far, but behaves a little differently from the
# other two in terms of how the collection is constructed. Accessing
# the association proxy causes sqlalchemy to query the tire table for
# for the `Car`'s `Tire`s and then iterates over each one, accessing
# its `sensor_data` attribute to construct the collection on `Car`.
# With the lazy loading defaults, this resulted in 5 queries to
# construct the relationship, so depending on how you need to use it
# you might consider some sort of eager loading.
sensor_datas_aprox = association_proxy('tires', 'sensor_data')
# Joins Tire and SensorData. TBH the docs only mention
# using secondary in context of m:n relationship, but
# this seems to work OK.
sensor_datas_rel = relationship(
'SensorData',
secondary='join(Tire, SensorData)',
viewonly=True
)
# a query enabled property, queries for the data on
# access, pretty self explanatory.
@property
def sensor_datas_prop(self):
return object_session(self).query(SensorData).\
join(Tire).filter(
Tire.car_id == self.id,
SensorData.tire_id == Tire.id
).all()
class Tire(Base):
id = Column(Integer, primary_key=True)
car_id = Column(Integer, ForeignKey('car.id'))
car = relationship("Car", backref="tires")
class SensorData(Base):
tire_id = Column(Integer, ForeignKey('tire.id'), primary_key=True)
tire = relationship("Tire", backref=backref("sensor_data", uselist=False))
if __name__ == '__main__':
s = Session()
for _ in range(2):
car = Car()
car.tires = [Tire(sensor_data=SensorData()) for _ in range(4)]
s.add(car)
s.commit()
for car in s.query(Car):
print('*' * 100)
print(car.sensor_datas_rel)
print(car.sensor_datas_prop)
print(car.sensor_datas_aprox)
输出:
****************************************************************************************************
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
****************************************************************************************************
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]
关于python - 如何通过多个级别的SQLAlchemy关系检索一组对象?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56070657/