## **获取单个数据** 获取单个数据的方法包括： ~~~ // 取出主键为1的数据 peter = User.query.filter_by(id=1).first() peter.email // 同上但是查询一个不存在的用户名返回None missing = User.query.filter_by(username='missing').first() missing is None ~~~ ## **主键查询** ~~~ User.query.get(1) ~~~ ## **获取数量** ~~~ User.query.count() ~~~ ## **获取所有数据** ~~~ User.query.filter().all() // 返回: [<User u'admin'>, <User u'guest'>] ~~~ ## **排序** ~~~ User.query.order_by(User.username).all() ~~~ ## **限制数量** ~~~ User.query.limit(1).all() ~~~ ## **模糊查询** ~~~ User.query.filter(User.name.like('xxx%')) ~~~ ## **in查询** ~~~ query.filter(Tags.id.in_([5,6])) ~~~ ## **not in查询** ~~~ query.filter(~Tags.id.in_([1,5,6])) ~~~ ## **and查询** ~~~ from sqlchemy import and_ query.filter(and_(Tags.name == 'Python',Tags.id==2)) 或者 query.filter(Tags.name == 'Python', Tags.id == 2) ~~~ ## **or查询** ~~~ from sqlalchemy import or_ query.filter(or_(Tags.name == 'Python', Tags.name == 'Flask')) ~~~ ## **原生查询** ~~~ from sqlalchemy import text query.filter(text('id>=:value1 and id <:value2')).params(value1=2,value2=5) ~~~ ## **完整sql语句查询** ~~~ from sqlalchemy import text query.from_statement(text("select * from tags where id=:value")).params(value=1) ~~~ ## **原生查询数据数量** ~~~ db.session.execute("SELECT COUNT(*) FROM table").scalar() ~~~ ## **group by统计数据** ~~~ from sqlalchemy import func db.session.query(Model).with_entities(Model.field, func.count(Model.field)).group_by('field').all() ~~~