sqlalchemy将两个表连接在一起

Question

我想将一个类对象映射到一个表，该表是两个表之间的联接，并且选择（映射）一个表中的所有列和联接表中的只有一列。你知道吗

join_table = join(table1, table2, tabl1.c.description==table2.c.description)
model_table_join= select([table1, table2.c.description]).select_from(join_table).alias()

我做得对吗？你知道吗

Answer 1

如果您只想从连接中拉入一个额外的列，那么我就不想使用任意的select映射。作为documentation points out：

The practice of mapping to arbitrary SELECT statements, especially complex ones as above, is almost never needed; it necessarily tends to produce complex queries which are often less efficient than that which would be produced by direct query construction. The practice is to some degree based on the very early history of SQLAlchemy where the mapper() construct was meant to represent the primary querying interface; in modern usage, the Query object can be used to construct virtually any SELECT statement, including complex composites, and should be favored over the “map-to-selectable” approach.

您只需在应用程序中选择额外的列：

session.query(Table1Model, Table2Model.description).join(Table2Model)

或者可以在Table1Model和association property上注册一个关系，该关系总是拉入额外的列：

class Table1Model(Base):
    # ...

    _table2 = relationship('Table2Model', lazy='join')
    description = association_proxy('_table2', 'description')

当您在Table1Model实例上与关联行交互时，association属性管理关联行的Table2Model.description列。你知道吗

这就是说，如果您必须使用join()查询作为基础，那么您可以使用^{} mapper argument从联接中排除额外的重复列：

join_table = join(table1, table2, table1.c.description == table2.c.description)

class JoinedTableModel(Base):
    __table__ = join_table
    __mapper_args__ = {
        'exclude_properties' : [table1.c.description]
    }

然后，新模型使用联接中的所有列来创建具有相同名称的属性，但“exclude\u properties”中列出的属性除外。你知道吗

或者，您可以继续在模型中使用重复的列名，只需给它们一个新名称：

join_table = join(table1, table2, table1.c.description == table2.c.description)

class JoinedTableModel(Base):
    __table__ = join_table

    table1_description = table1.c.description

可以通过这种方式重命名联接中的任何列，此时它们将不再与另一个表中具有相同基名称的其他列冲突。你知道吗