面试题：Python的SQLAlchemy库中如何实现复杂的多表联合查询

Question

假设有两个表`orders`（包含字段`order_id`主键，`customer_id`，`order_date`）和`customers`（包含字段`customer_id`主键，`customer_name`，`phone`），使用SQLAlchemy库查询出每个订单对应的客户名称和电话，并且按照订单日期降序排列，写出相应的Python代码。

Answer 1

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建数据库引擎
engine = create_engine('sqlite:///:memory:')

# 创建基类
Base = declarative_base()

# 定义orders表模型
class Orders(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    customer_id = Column(Integer)
    order_date = Column(Date)

# 定义customers表模型
class Customers(Base):
    __tablename__ = 'customers'
    customer_id = Column(Integer, primary_key=True)
    customer_name = Column(String)
    phone = Column(String)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 查询每个订单对应的客户名称和电话，并按订单日期降序排列
result = session.query(Orders.order_id, Customers.customer_name, Customers.phone)\
   .join(Customers, Orders.customer_id == Customers.customer_id)\
   .order_by(Orders.order_date.desc())

# 输出结果
for row in result:
    print(row)

# 关闭会话
session.close()