连接数据库准备
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:1234@127.0.0.1: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()