sqlalchemy高级查询操作

连接数据库准备

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from models import Users,Person,Hobby,Boy,Girl
from sqlalchemy.sql import text

engine = create_engine("mysql+pymysql://root:[email protected]:3306/sqlalchemy01", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

1 filter_by(写条件)

ret = session.query(Users).filter_by(name='xxx').all()
print(ret)

2 and条件连接

ret = session.query(Users).filter(Users.id > 1, Users.name == 'xxx').all()
print(ret)

3 between条件连接

# SQL语句
# select * from users where user.id between 4 and 10 and name=xxx;

ret = session.query(Users).filter(Users.id.between(4, 10), Users.name == 'xxx')
print(ret)

4 in 条件

ret = session.query(Users).filter(Users.id.in_([1,4,5])).all()
print(ret)

5 ~ (非，除...外)

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
print(ret)

6 二次筛选

# SQL语句
# select * from users where id in (select id from users where name = xxx);

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='xxx'))).all()
print(ret)

7 and 和 or 条件

# 导入模块
from sqlalchemy import and_, or_
# or_包裹的都是or条件，and_包裹的都是and条件
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'xxx')).all()

ret = session.query(Users).filter(Users.id > 3, Users.name == 'xxx').all() # 根上面一样

# SQL语句
# select * from users where id<=2 or name =xxx;

ret = session.query(Users).filter(or_(Users.id <= 2, Users.name == 'xxx')).all()

# SQL语句
# select * from users where id <2 or (name=xxx and id>3) or extra !='';

ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'xxx', Users.id > 3),
        Users.extra != ""
    ))

8 like 查询

# SQL语句
# select * from users where name like l%;

ret = session.query(Users).filter(Users.name.like('l%')).all()
# 不以'l'开头
ret = session.query(Users).filter(~Users.name.like('l%')).all()

9 限制，用于分页，区间

ret = session.query(Users)[1:2]  # 去第一条到第二条  其实就是只取 第二条,从0开始

10 排序，根据name降序排列（从大到小）

ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.asc())
ret = session.query(Users).order_by(Users.name).all()

# 第一个条件重复后，再按第二个条件升序排
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

11 分组

# 导入模块
from sqlalchemy.sql import func

# select name from users group by name;  一旦分组,只能查询 分组字段和 聚合函数的字段
# 以用户名字分组（有重复名）
ret = session.query(Users.name).group_by(Users.name).all()

# 分组之后取最大id，id之和，最小id 和名字
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id),
    func.count(Users.id),
    Users.name
    ).group_by(Users.name).all()

# haviing筛选
# SQL语句
# select max(id),sum(id),min(id),count(id),name from users group by name where id >2 having min(id)>2;

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),    
    func.min(Users.id),
    func.count(Users.id),
    Users.name
    ).filter(Users.id>2).group_by(Users.name).having(func.min(Users.id) >2)

12 连表（默认用forinkey关联）

# SQL语句
# select * from person,hobby where user.hobby_id=hobby.id;

ret = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all()

# join表，默认是inner join
# SQL语句
# select * from person inner join hobby on person.hobby_id=hobby.id;

ret = session.query(Person).join(Hobby).all()

# isouter=True 外连，表示Person left join Hobby，没有右连接，反过来即可
# SQL语句
# select * from person left join hobby on person.hobby_id=hobby.id;

ret = session.query(Person).join(Hobby, isouter=True).all()
ret = session.query(Hobby).join(Person, isouter=True).all()  # 右连接

# 没有指定链表条件,默认以外键关联
# 自己指定on条件（连表条件）,第二个参数，支持on多个条件，用and_,同上
# SQL语句
# select * from Person left join hobby on person.id=hobby.id;  # sql 没有意义,只是书写案例
ret = session.query(Person).join(Hobby,Person.id==Hobby.id, isouter=True)

13 union和union all

# 组合（了解）UNION 操作符用于合并两个或多个 SELECT 语句的结果集
# union和union all的区别？ union会去除重复的行，还会对结果集进行排序
q1 = session.query(Boy.id,Boy.name).filter(Boy.id > 1)
q2 = session.query(Girl.id,Girl.name).filter(Girl.id < 10)
ret = q1.union(q2).all()

q1 = session.query(Boy.name).filter(Boy.id > 1)
q2 = session.query(Girl.name).filter(Girl.id < 10)
ret = q1.union_all(q2).all()
print(ret)

# 提交事务
session.commit()
# 关闭session
session.close()