如何从 SQLAlchemy JOIN 返回两个表的结果？

我们需要从两个未建立关系的表中，分别叫做 GeneralLedger 和 ConsolidatedLedger，检索数据。这两个表可以通过它们的共同字段 invoiceId 进行连接。在之前的尝试中，我们需要检索这两个表中所有匹配记录的组合。

2、解决方案

使用 records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all() 代码来查询这两个表并返回结果。

当迭代这些结果时，你需要使用 records.GeneralLedger 和 records.ConsolidatedLedger 来分别访问这两个表中的字段。例如：

for record in records:
    print(record.GeneralLedger)
    print(record.ConsolidatedLedger)

此外，可以使用 DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).count() 来计算两个表中匹配记录的总数。

最后，可以使用 records = paginate.Page(query, current_page, url=page_url) 分页查询结果，其中 query 是上面提到的查询对象， current_page 是当前页的页码， url 是分页链接的 URL。

from sqlalchemy.orm import aliased
from sqlalchemy.sql import select
from sqlalchemy import and_
from sqlalchemy.sql.expression import column, join
import sqlalchemy

def join_tables_with_alias(tables, joins):
    """
    Join SQLAlchemy tables using JOIN clauses on specific columns.

    Args:
        tables: SQLAlchemy models representing the tables to join.
        joins: List of SQLAlchemy joins with columns from each table to join on.

    Returns:
        joined_table: SQLAlchemy joined table with aliased columns.
    """
    alias_map = {}
    joined_table = None

    for i, table in enumerate(tables):
        # Create an alias for the table
        alias = aliased(table)

        # Store the alias in the map
        alias_map[table] = alias

        # Join the table
        if joined_table is None:
            joined_table = alias
        else:
            joined_table = joined_table.join(alias, joins[i])

    return joined_table


def join_tables_with_select(tables, joins):
    """
    Join SQLAlchemy tables using a SQLAlchemy select statement.

    Args:
        tables: SQLAlchemy models representing the tables to join.
        joins: List of SQLAlchemy joins with columns from each table to join on.

    Returns:
        joined_table: SQLAlchemy joined table with aliased columns.
    """
    # Create a list of column expressions for each table
    columns = [column(column_name) for table in tables]

    # Create a list of JOIN expressions
    joins = [join(table1, table2, join) for table1, table2, join in joins]

    # Create a SQLAlchemy select statement with the columns and joins
    statement = select(*columns).select_from(*joins)

    # Return the joined table
    return statement


def join_tables_with_sqlalchemy_join(tables, joins):
    """
    Join SQLAlchemy tables using the SQLAlchemy join() function.

    Args:
        tables: SQLAlchemy models representing the tables to join.
        joins: List of SQLAlchemy joins with columns from each table to join on.

    Returns:
        joined_table: SQLAlchemy joined table with aliased columns.
    """
    # Join the tables using the SQLAlchemy join() function
    joined_table = tables[0]
    for table, join in zip(tables[1:], joins):
        joined_table = joined_table.join(table, join)

    # Return the joined table
    return joined_table


# Define the SQLAlchemy models
Base = sqlalchemy.orm.declarative_base()

class GeneralLedger(Base):
    __tablename__ = 'generalledgers'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    invoiceId = sqlalchemy.Column(sqlalchemy.Integer)

class ConsolidatedLedger(Base):
    __tablename__ = 'consolidatedledgers'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key = True)
    invoiceId = sqlalchemy.Column(sqlalchemy.Integer)

# Create the SQLAlchemy session
session = sqlalchemy.orm.sessionmaker()()

# Define the tables and joins
tables = [GeneralLedger, ConsolidatedLedger]
joins = [GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId]

# Join the tables using the join_tables_with_alias() function
joined_table_alias = join_tables_with_alias(tables, joins)

# Join the tables using the join_tables_with_select() function
joined_table_select = join_tables_with_select(tables, joins)

# Join the tables using the join_tables_with_sqlalchemy_join() function
joined_table_sqlalchemy_join = join_tables_with_sqlalchemy_join(tables, joins)

# Print the results of the join
print(session.query(joined_table_alias).all())
print(session.execute(joined_table_select).fetchall())
print(session.query(joined_table_sqlalchemy_join).all())