sqlalchemy 过滤多列

# 使用SQLAlchemy过滤多列的实用指南

python
from sqlalchemy import create_engine, Column, String, Integer, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    gender = Column(String)
# 创建数据库连接
engine = create_engine('sqlite:///:memory:')
# 创建表
Base.metadata.create_all(engine)
# 添加测试数据
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
    User(name='Alice', age=28, gender='Female'),
    User(name='Bob', age=30, gender='Male'),
    User(name='Charlie', age=22, gender='Male'),
    User(name='Diana', age=32, gender='Female'),
])
session.commit()
# 过滤多列
filtered_users = session.query(User).filter(User.age.between(25, 35), User.gender == 'Female').all()
# 输出结果
for user in filtered_users:
    print(f"Name: {user.name}, Age: {user.age}, Gender: {user.gender}")

python
from sqlalchemy import or_
# 添加更多测试数据
session.add_all([
    User(name='Eva', age=29, gender='Female'),
    User(name='Frank', age=40, gender='Male'),
    User(name='Grace', age=26, gender='Female'),
])
session.commit()
# 使用or_和and_组合条件
advanced_filtered_users = session.query(User).filter(
    or_(
        and_(User.age.between(25, 35), User.gender == 'Female'),
        and_(User.age > 35, User.gender == 'Male')
    )
).all()
# 输出结果
for user in advanced_filtered_users:
    print(f"Name: {user.name}, Age: {user.age}, Gender: {user.gender}")