在sqlalchemy中,我在两个表之间有一对多的映射:一个表表示运动员,另一个表对应运动员的成绩。运动员可以有任意数量的分数。我试图根据运动员成绩的乘积来筛选运动员。以下是两个表的代码:
ECHO = False
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
engine = create_engine('sqlite:///:memory:', echo=ECHO)
Base = declarative_base()
class Athlete(Base):
__tablename__ = 'athletes'
id = Column(Integer, primary_key=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
athlete0 = Athlete(id = 0)
athlete1 = Athlete(id = 1)
athlete2 = Athlete(id = 2)
session.add_all([
athlete0,
athlete1,
athlete2])
session.commit()
class Score(Base):
__tablename__ = 'scores'
pos = Column(Integer, primary_key=True)
score = Column(Integer)
athlete_id = Column(Integer, ForeignKey('athletes.id'))
athlete = relationship("Athlete", backref=backref('scores', order_by=pos))
Base.metadata.create_all(engine)
athlete0.scores = [Score(score = 4), Score(score = 3), Score(score = 5)]
athlete1.scores = [Score(score = 2), Score(score = 1)]
athlete2.scores = [Score(score = 3), Score(score = 8), Score(score = 10), Score(score = 7)]
session.commit()
下面是我想做的事情:
^{pr2}$
找到了我自己问题的答案。我在下面用sqlalchemy核心来表示,因为这就是我最近使用的。在
诀窍是使用WITH RECURSIVE来计算乘积:
Python代码如下所示:
结果是:
^{pr2}$python函数gen_语句生成的要插入占位符表中的SQL(w/indentation被我改为更具可读性)是:
奇怪的是,我写入
placeholder
表然后从中读取的原因是,如果我只是迭代select语句返回的行,sqlalchemy 1.0会为产生0行的> 365
请求抛出一个错误。理论上,它应该只产生0行。但是,当语句的结果刚刚插入表placeholder
时,它会按预期插入0行。在希望这段代码能对你有所帮助。 只需在运动员课程中增加混合动力车属性。在
问题是:
^{pr2}$相关问题 更多 >
编程相关推荐