构造类
from collections import namedtuple
Book = namedtuple('Book',['title','price','author']
实例： book1 = Book('python',39,'刘哲')
book1.naem =
应用
导入创建的类: Employee，Department 在F盘pms 的文件
row
for row in Employee.query.all: print (row.name, row.id)
label
for row in db.session.query(Employee.name.label('姓名')):<br> print(row.姓名)<br>
limit
for emp in Employee.query.limit(5):<br> print(emp) 打印前5个<br>
for emp in db.session.query(Employee).order_by(Employee.salary).limit(5):<br> print(emp) 按工资升序的前5个<br>
offset()
for emp in Employee.query.order_by(Employee.salary).offset(10).limit(10):<br> print(emp) 跳过10再取10个<br>
filter_by
for emp in db.session.query(Employee).filter_by(name='齐珍生'):<br> print(emp) 找姓名等于齐珍生的<br>
contains
for emp in db.session.query(Employee).filter(Employee.name.contains('小')):<br> print(emp) 姓名中包含 小 字的<br>
filter
for emp in db.session.query(Employee).filter(Employee.name=='齐珍生'):<br> print(emp) 找姓名等于齐珍生的<br>
for emp in Employee.query.filter(Employee.salary > 7000.0):<br> print(emp) 工资大于7000<br>
startswith
for emp in Employee.query.filter(Employee.name.startswith('刘')):<br> print(emp) 找出姓刘的 （即以刘开头）<br>
for emp in Employee.query.filter(Employee.salary > 5000.0).filter(Employee.name.startswith('王')):<br> print(emp) 工资大于5000姓王的<br>
for emp in Employee.query.filter(Employee.salary > 5000.0).filter(Employee.gender=='男').filter(Employee.name.startswith('刘')):<br> print(emp) 大于5000姓刘的男人<br>
like
for emp in Employee.query.filter(Employee.name.like('刘%')):<br> print(emp) 找出姓刘的<br>
for emp in db.session.query(Employee).filter(Employee.name.like('%龙%')):<br> print(emp) 姓名中包含龙字的<br> <br>
for emp in Employee.query.filter(Employee.name.like('_天%')):<br> print(emp) 找出姓名中第二个字 带 天字的<br>
in_ / is_
for emp in db.session.query(Employee).filter(Department.name.in_(['技术部'])):<br> print(emp) 技术部门的员工<br>
for emp in db.session.query(Employee).filter(~Department.name.in_(['技术部','财务部'])):<br> print(emp) 不包括技术部和财务部的员工<br>
for emp in Employee.query.filter(Employee.name.is_('齐珍生')):<br> print(emp)<br>
for emp in db.session.query(Employee).filter(Employee.name.startswith('刘'), Employee.gender=='男'):<br> print(emp) 姓刘 为男<br>
导入 from sqlalchemy import and_<br>
for emp in db.session.query(Employee).filter(and_(Employee.name.startswith('刘'), Employee.gender=='女')):<br> print(emp) 姓刘为女<br>
导入 from sqlalchemy import or_
for emp in db.session.query(Employee).filter(or_(Employee.name.startswith('刘'), Employee.name.startswith('张'))):<br> print(emp) 找出姓刘的和姓张的<br>
导入 from sqlalchemy import desc<br>
for emp in db.session.query(Department).order_by(desc(Department.id)):<br> print(emp) 倒序排列<br>
导入 from sqlalchemy import text<br>
dep = db.session.query(Department).from_statement(text('select * from department where name=:n')).params(n='财务部').one() 用SQL语句查询<br>
导入 from sqlalchemy import func<br>
dep = db.session.query(func.count(Employee.gender),Employee.gender).group_by(Employee.gender).all()<br>
dep<br>[(0, None), (131, '女'), (387, '男')]<br>
返回一个列表
emp = db.session.query(Department).all()<br>
emp = db.session.query(Department)<br>
emp.filter(Department.id > 3).all()<br>
emp.filter(Department.id > 3).all()[0]<br>
first() / one() 查询单个
dep = db.session.query(Department).filter(Department.id==3).first()<br>
dep = db.session.query(Department).filter(Department.id==3333).first()<br>
dep = db.session.query(Department).filter(Department.id==3333).one_or_none()<br>
dep = db.session.query(Department).filter(Department.id==3).one()<br>
scalar() 查询一列
dep = db.session.query(Department.id).filter(Department.name=='财务部').scalar()<br>
count
dep = db.session.query(Employee).filter(Employee.name.startswith('刘')).count()<br>
