Sqlalchemy 简明教程
SQLAlchemy ORM - Applying Filter
在本章中,我们将讨论如何应用过滤器以及某些过滤器操作及其代码。
查询对象表示的结果集可以通过使用 filter() 方法来满足某些条件。filter 方法的一般用法如下:
session.query(class).filter(criteria)
在以下示例中,通过条件 (ID>2) 过滤了客户表上的 SELECT 查询获得的结果集:
result = session.query(Customers).filter(Customers.id>2)
此语句将转换为以下 SQL 表达式:
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id > ?
由于绑定参数 (?) 给定为 2,因此只会显示 ID 列 >2 的那些行。完整代码如下所示:
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).filter(Customers.id>2)
for row in result:
print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
在 Python 控制台中显示的输出如下所示:
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com