如何使用 SQLAlchemy 实现内连接? [英] How to achieve inner join using SQLAlchemy?

问题描述

如何使用 SQLAlchemy 实现内连接?我正在尝试进行简单的聊天

How to achieve inner join using SQLAlchemy ? I am trying to make simple chat

class Base(object):
    def __tablename__(self):
        return self.__name__.lower()

    id = Column(Integer, primary_key=True)

Base = declarative_base(cls=Base)

class PlayerModel(Base):
    __tablename__ = 'players'
    username = Column(String(30), nullable=False)
    email = Column(String(75), nullable=False)
    password = Column(String(128), nullable=False)

class MessageModel(Base):
    __tablename__ = 'messages'
    player_id = Column(Integer,ForeignKey('chats.id'), nullable=False)
    message = Column(String(2000), nullable=False)
    time = Column(TIMESTAMP, server_default=func.now())

    def __repr__(self):
        return "<Message('%s')>" % (self.type)

我想阅读比某个日期更早的所有消息，从而获得像

I want to read all messages younger than some date and in result to have list of dictionaries like

[{'username':'x','message':'y','time':'number0'},{'username':'y','message':'z','time':'number1'},
{'username':'x','message':'zz','time':'number'}]

为此我需要内部连接.如何使它起作用?

and for that I need inner join. How to make this to work ?

推荐答案

为此，您首先需要有一个 session 来进行 Query.此外，在 MessageModel 上拥有 relationship 会很方便.

For that you first need to have a session to make a Query. Additionally it can be convenient to have a relationship on your MessageModel.

class MessageModel(Base):
    __tablename__ = 'messages'
    player_id = Column(Integer,ForeignKey('chats.id'), nullable=False)
    message = Column(String(2000), nullable=False)
    time = Column(TIMESTAMP, server_default=func.now())
    player = relationship(PlayerModel, backref="messages")

这将在两个模型上创建关系.

This will create the relationship on both models.

results = (session.query(PlayerModel)
                  .join(PlayerModel.messages)
                  .values(PlayerModel.username,
                          MessageModel.message,
                          MessageModel.time))
# results will be a generator object

# This seems a bit convoluted, but here you go.
resultlist = []
for username, message, time in results:
    resultlist.append({'message': message,
                       'username': username,
                       'time': time})

可能有更优雅的方法来处理您的数据结构，但这种方法应该可行.

There may be more elegant ways to come to your data structure but this one should work.

这篇关于如何使用 SQLAlchemy 实现内连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！