SQLAlchemy group \u by（）简单链接表所需的求和帮助

Question

我四处寻找类似主题的帮助，但没有找到解决我的问题的正确方法，这个问题涉及SQLAlchemy/Flask-SQLAlchemy。你知道吗

情况是这样的：我有3个表处理彩票数据。表格如下：“莱佛士”表格：

class RaffleModel(db.Model):
    __tablename__ = 'raffles'

    id = db.Column(db.Integer, primary_key=True)

    # relationship to users
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship("UserModel", backref="raffles")

    # relationship to ticket_md
    ticket_md_id = db.Column(db.Integer, db.ForeignKey('ticket_mds.id'))
    ticket_md = db.relationship("TicketMetaDataModel", backref="raffles")

    def __init__(self, user, ticket_md):
        self.user = user
        self.ticket_md = ticket_md

使用以下示例数据：

id    user_id    ticket_md_id
1     1          1
2     2          1
3     3          1
4     1          2
5     2          2

“用户”表：

class UserModel(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(25))
    last_name = db.Column(db.String(25))
    won = db.Column(db.Boolean, default=False, nullable=False)

    # has backref via user.raffles

    def __init__(self, first_name, last_name, won):
        self.first_name = first_name
        self.last_name = last_name
        self.won = won

使用以下示例数据：

id    first_name    last_name    won
1     Rob           Stark        False
2     Stan          Clark        False
3     Tom           Dark         False

最后一张“票证”表：

class TicketMetaDataModel(db.Model):
    __tablename__ = 'ticket_mds'

    id = db.Column(db.Integer, primary_key=True)
    raffle_type = db.Column(db.String(20))
    description = db.Column(db.String(200))
    modifier = db.Column(db.Integer)

    # has backref via ticket_md.raffles

    def __init__(self, raffle_type, description, modifier):
        self.raffle_type = raffle_type
        self.description = description
        self.modifier = modifier

使用以下示例数据：

id    raffle_type    description    modifier
1     type_1         blah blah      21
2     type_2         blah blah      5

我在这里的问题在于，我似乎不能很好地找出一个查询来获得票点的总和（基于TicketMetaDataModel.modifier修改器属性）由用户从莱佛士表分组，以以下格式返回结果：

名、姓、总分 e、 g.：

Rob    Stark    26
Stan   Clark    26
Tom    Dark     21

我试过以下方法（但没能再进一步）：

query = db.session.query(RaffleModel.id, UserModel.first_name, UserModel.last_name, TicketMetaDataModel.modifier)
query = query.join(UserModel).join(TicketMetaDataModel)
results = query.all()
results equals:

[(1, 'Rob', 'Stark', 21), (2, 'Stan', 'Clark', 21), (3, 'Tom', 'Dark', 21),(4, 'Rob', 'Stark', 5), (5, 'Stan', 'Clark', 5)]

我知道这很难消化，但任何帮助都将不胜感激

Answer 1

这引起了我的兴趣，我做了一个独立的例子

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, ForeignKey, String, Boolean
from sqlalchemy.orm import relationship, sessionmaker

from pprint import pformat as pf

Base = declarative_base()

class ReprMixin(object):

    """A mixin to implement a generic __repr__ method"""

    def as_dict(self):
        """return instance as a dictionary"""
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    def __repr__(self):
        return '<%s(%s)>' % (self.__class__.__name__, ', '.join([f'{c.name} = {getattr(self, c.name)}' for c in self.__table__.columns]))

class RaffleModel(ReprMixin, Base):
    __tablename__ = 'raffles'

    id = Column(Integer, primary_key=True)

    # relationship to users
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("UserModel", backref="raffles")

    # relationship to ticket_md
    ticket_md_id = Column(Integer, ForeignKey('ticket_mds.id'))
    ticket_md = relationship("TicketMetaDataModel", backref="raffles")

    def __init__(self, user, ticket_md):
        self.user = user    
        self.ticket_md = ticket_md

    @classmethod
    def insert_defaults(cls, session):
        for user_id, ticket_id in zip([1,2,3,1,2],[1,1,1,2,2]):
            user = session.query(UserModel).filter_by(id=user_id).scalar()
            ticket = session.query(TicketMetaDataModel).filter_by(id=ticket_id).scalar()
            session.add(cls(user, ticket))
        session.commit()


class UserModel(ReprMixin, Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(25))
    last_name = Column(String(25))
    won = Column(Boolean, default=False, nullable=False)

    # has backref via user.raffles

    def __init__(self, first_name, last_name, won):
        self.first_name = first_name
        self.last_name = last_name
        self.won = won

    @classmethod
    def insert_defaults(cls, session):
        for first_name, last_name, won in zip("Rob Stan Tom".split(),"Stark Clark Dark".split(), [False]*3):
            session.add(cls(first_name, last_name, won))
        session.commit()


class TicketMetaDataModel(ReprMixin, Base):
    __tablename__ = 'ticket_mds'

    id = Column(Integer, primary_key=True)
    raffle_type = Column(String(20))
    description = Column(String(200))
    modifier = Column(Integer)

    # has backref via ticket_md.raffles

    def __init__(self, raffle_type, description, modifier):
        self.raffle_type = raffle_type
        self.description = description
        self.modifier = modifier

    @classmethod
    def insert_defaults(cls, session):
        for raffle_type, description, modifier in zip("type1 type2".split(), "desc1 desc2".split(), [21,5]):
            session.add(cls(raffle_type, description, modifier))
        session.commit()

if __name__ == '__main__':


    from pprint import pprint as pp
    engine = create_engine('sqlite://')
    Session = sessionmaker()

    sess = Session(bind=engine)

    Base.metadata.create_all(bind=engine)

    TicketMetaDataModel.insert_defaults(sess)
    UserModel.insert_defaults(sess)
    RaffleModel.insert_defaults(sess)


    print('************************')
    pp(sess.query(UserModel).all())
    print('************************')
    pp(sess.query(RaffleModel).all())
    print('************************')
    pp(sess.query(TicketMetaDataModel).all())


    from sqlalchemy import func
    query = sess.query(UserModel.first_name, UserModel.last_name, func.sum(TicketMetaDataModel.modifier))

    query2 = query.join(RaffleModel.user)\
                .join(RaffleModel.ticket_md)\
                .group_by(UserModel.first_name, UserModel.last_name)

    print('************************')
    print(query)
    print(query.all())
    print('************************')
    print(query2.all())
    print(query2)

    Base.metadata.drop_all(engine)

运行它，它返回

************************
[<UserModel(id = 1, first_name = Rob, last_name = Stark, won = False)>,
 <UserModel(id = 2, first_name = Stan, last_name = Clark, won = False)>,
 <UserModel(id = 3, first_name = Tom, last_name = Dark, won = False)>]
************************
[<RaffleModel(id = 1, user_id = 1, ticket_md_id = 1)>,
 <RaffleModel(id = 2, user_id = 2, ticket_md_id = 1)>,
 <RaffleModel(id = 3, user_id = 3, ticket_md_id = 1)>,
 <RaffleModel(id = 4, user_id = 1, ticket_md_id = 2)>,
 <RaffleModel(id = 5, user_id = 2, ticket_md_id = 2)>]
************************
[<TicketMetaDataModel(id = 1, raffle_type = type1, description = desc1, modifier = 21)>,
 <TicketMetaDataModel(id = 2, raffle_type = type2, description = desc2, modifier = 5)>]
************************
SELECT users.first_name AS users_first_name, users.last_name AS users_last_name, sum(ticket_mds.modifier) AS sum_1 
FROM users, ticket_mds
[('Tom', 'Dark', 78)]
************************
[('Rob', 'Stark', 26), ('Stan', 'Clark', 26), ('Tom', 'Dark', 21)]
SELECT users.first_name AS users_first_name, users.last_name AS users_last_name, sum(ticket_mds.modifier) AS sum_1 
FROM raffles JOIN users ON users.id = raffles.user_id JOIN ticket_mds ON ticket_mds.id = raffles.ticket_md_id GROUP BY users.first_name, users.last_name

如果RaffleModel是UserModel和TicketMetadataModel之间的多对多关系的关联表，那么您的模型将受益匪浅

Answer 2

如果我答对了你的问题。您正在尝试按已通过联接获得的值进行分组。你知道吗

from sqlalchemy import func
query = db.session.query(UserModel.first_name, UserModel.last_name, func.sum(TicketMetaDataModel.modifier))
query = query.join(RaffleModel,RaffleModel.user_id==UserModel.id).join(RaffleModel.ticket_md_id==TicketMetaDataModel.id)
.group_by(UserModel.first_name, UserModel.last_name)

它应该会给你要求的结果