目录
起步
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""表结构"""
import logging
from sqlalchemy import and_
from sqlalchemy import or_
from sqlalchemy import func
from sqlalchemy import text
from sqlalchemy import exists
from sqlalchemy import distinct
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:123@localhost:3306/sqlalchemy_db?charset=utf8mb4', max_overflow=10,
echo=True)
Base = declarative_base()
logging.captureWarnings(capture=True)
# 多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工
class Dep(Base):
__tablename__ = 'dep'
id = Column(Integer, primary_key=True, autoincrement=True)
dname = Column(String(64), nullable=False, index=True)
class Emp(Base):
__tablename__ = 'emp'
id = Column(Integer, primary_key=True, autoincrement=True)
ename = Column(String(32), nullable=False, index=True)
dep_id = Column(Integer, ForeignKey('dep.id'))
def init_db():
Base.metadata.create_all(bind=engine)
def drop_db():
Base.metadata.drop_all(bind=engine)
# drop_db()
# init_db()
Session = sessionmaker(bind=engine)
session = Session()
# session.add_all([
# Dep(dname='技术'),
# Dep(dname='销售'),
# Dep(dname='运营'),
# Dep(dname='人事')
# ])
#
# session.add_all([
# Emp(ename='林海峰', dep_id=1),
# Emp(ename='李杰', dep_id=1),
# Emp(ename='武沛齐', dep_id=1),
# Emp(ename='元昊', dep_id=2),
# Emp(ename='李钢弹', dep_id=3),
# Emp(ename='张二丫', dep_id=4),
# Emp(ename='李坦克', dep_id=2),
# Emp(ename='王大炮', dep_id=4),
# Emp(ename='牛榴弹', dep_id=3)
# ])
where子句
filter_by
# 01 filter_by
# filter_by只能传关键字参数,什么 = 什么
rows = session.query(Emp).filter_by(ename='林海峰')
rows = session.query(Emp).filter_by(ename='林海峰').all()
for each_line in rows:
print(each_line.dep_id, each_line.ename)
# 也可以传多个,逗号分隔,and关系
rows = session.query(Emp).filter_by(dep_id=1, ename='李杰').all()
for each_line in rows:
print(each_line.dep_id, each_line.ename)
filter
# 02 filer
# filter传的是表达式,
rows = session.query(Emp).filter(Emp.id > 0)
for each_line in rows:
print(each_line.dep_id, each_line.ename)
# 同样可以多个,逗号分隔,默认and关系
rows = session.query(Emp).filter(Emp.id > 0, Emp.ename == '林海峰').all()
for each_line in rows:
print(each_line.id, each_line.ename)
# 传参
rows = session.query(Emp).filter(text('Emp.id > :id')).params(id=21).all()
for each_line in rows:
print(each_line.id, each_line.ename)
操作符
between … and …
# 03. filter ---> between ... and ...
rows = session.query(Emp).filter(Emp.id.between(1, 3), Emp.ename == '林海峰').all()
rows = session.query(Emp).filter(Emp.id.between(cleft=1, cright=3)).all()
for each_line in rows:
print(each_line.id, each_line.ename)
in
# 04. filter ---> in
rows = session.query(Emp).filter(Emp.id.in_(other=[1, 3, 99, 101]), Emp.ename == '林海峰').all()
for each_line in rows:
print(each_line.id, each_line.ename)
# 04.1 in ()
rows = session.query(Emp).filter(Emp.id.in_(session.query(Emp.id)))
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
not
# 05. not
# ~代表取反,转换成sql就是not
rows = session.query(Emp).filter(~Emp.id.in_([100, 99, 2])).all()
for each_line in rows:
print(each_line.id, each_line.ename)
rows = session.query(Emp).filter(Emp.id.notin_([100, 99, 2])).all()
for each_line in rows:
print(each_line.id, each_line.ename)
and / or
# 06. and_ / or_
# 形式一: and
rows = session.query(Emp).filter(Emp.id > 0, Emp.ename == '林海峰').all()
for each_line in rows:
print(each_line.id, each_line.ename)
# 形式二: and_
# and_(Emp.id > 0, Emp.ename == '林海峰') 表示Emp.id > 0 和 Emp.ename == '林海峰'之间的关系是and
rows = session.query(Emp).filter(and_(Emp.id>0, Emp.ename == '林海峰')).all()
for each_line in rows:
print(each_line.id, each_line.ename)
# or_
# or_(Emp.id > 0, Emp.ename == '林海峰') 表示Emp.id > 1 和 Emp.ename == '林海峰'之间的关系是or
rows = session.query(Emp).filter(or_(Emp.id > 0, Emp.ename == '林海峰')).all()
for each_line in rows:
print(each_line.id, each_line.ename)
# or_(Emp.id > 2, and_(Emp.dep_id == 1, Emp.id >1))表示 emp.id > 2 or emp.dep_id == 1 and emp.id > 1
rows = session.query(Emp).filter(or_(Emp.id > 2, and_(Emp.dep_id == 1, Emp.id > 1)))
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
like和通配符
# 07. like 和 通配符 % _
rows = session.query(Emp).filter(Emp.ename.like(other='%海_%')).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
# 07.1 not like
rows = session.query(Emp).filter(Emp.ename.notlike(other='%海_%')).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
# 07.1 not like
rows = session.query(Emp).filter(~Emp.ename.like('%海_%')).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
正则regexp
仅用于MySQL查询
# 仅用于MySQL查询
rows = session.query(Emp).filter(Emp.ename.op(opstring='regexp')(r'^林.*'))
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
is / is not
# 08. is 和 is not --> 判断null
# is
rows = session.query(Emp).filter(Emp.ename.is_(other=None)).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
# is not
rows = session.query(Emp).filter(Emp.ename.isnot(other=None)).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
rows = session.query(Emp).filter(~Emp.ename.is_(other=None)).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
exists
# 09. exists
rows = session.query(Emp).filter(exists().where(1 == 2)).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
# not exists
rows = session.query(Emp).filter(~exists().where(1 == 2)).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
group by 子句
group_by()
rows = session.query(Emp.dep_id).group_by(Emp.dep_id).all()
for each_line in rows:
print(each_line.dep_id)
聚合函数
# 15. 聚合函数
# 不管最后显示几个,最终结果都是一个元组
# 可以为字段起一个别名,方便访问
rows = session.query(
func.max(Emp.dep_id).label(name='max'),
func.min(Emp.dep_id),
func.sum(Emp.dep_id),
func.avg(Emp.id),
func.count(1)
).group_by(Emp.dep_id).all()
print(rows)
print(rows[0].max)
for each_group in rows:
for item in each_group:
print(item, end=' ')
print()
having子句
having()
rows = session.query(Emp.dep_id, func.count(1)).group_by(Emp.dep_id).having(func.count(1) > 2).all()
for each_group in rows:
for item in each_group:
print(item, end=' ')
print()
order by 子句
order_by()
# order by子句
# 11. 排序asc / desc / asc, desc
rows = session.query(Emp).order_by(Emp.id.desc()).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
rows = session.query(Emp).order_by(Emp.dep_id.desc(), Emp.id.asc()).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
limit 子句
limit()
# limit子句
# 10. limit
row = session.query(Emp).limit(limit=1)
for each in row:
print(each.id, each.ename, each.dep_id)
row = session.query(Emp).limit(limit=2).offset(1)
for each in row:
print(each.id, each.ename, each.dep_id)
rows = session.query(Emp)[0:5]
rows = session.query(Emp)[-5:-1]
rows = session.query(Emp)[0:5:2]
rows = session.query(Emp)[0:5:-1] # 跟Python中有点不一样
rows = session.query(Emp)[0:5:-2]
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dep_id)
别名
label(name)
# 别名
# 12. label
rows = session.query(Emp.ename.label(name='employee_name'))
for each_line in rows:
print(each_line.employee_name)
# # ============================
# # 能用索引, 是因为query(Emp.ename),
# # 而不是query(Emp)
# # ============================
print(each_line[0])
子查询
# 子查询
# 13. 子查询
# 13.1 子查询当作一张表来用 ----> 临时表subquery
rows = session.query(session.query(Emp.id, Emp.ename).filter(Emp.id > 2).subquery()).all()
for each_line in rows:
print(each_line.id, each_line.ename)
print(each_line[0], each_line[1])
# 13.2 子查询当作in的范围用 ----> in (subquery)
rows = session.query(Emp.ename, Emp.dep_id).filter(Emp.dep_id.in_(
session.query(Dep.id).filter_by(dname='销售')
))
print(rows)
for each_line in rows:
print(each_line.ename, each_line.dep_id)
print(each_line[0], each_line[1])
# 13.3 子查询当作select后面的字段使用
# ==================================
# SELECT emp.ename AS emp_ename, (SELECT dep.dname
# FROM dep
# WHERE emp.dep_id = dep.id) AS anon_1
# FROM emp
# ==================================
rows = session.query(
Emp.ename,
session.query(Dep.dname).filter(Emp.dep_id == Dep.id).as_scalar()
# session.query(Dep.dname).filter(Emp.dep_id == Dep.id).as_scalar().label(name='abc') # 可以起别名
).all()
print(rows)
for each_line in rows:
for item in each_line:
print(item, end=' ')
print()
连表
# 17. 连表
# 17.1 笛卡尔积
rows = session.query(Emp, Dep).all()
print(rows) # 每一个元素都是一个元组,[0]是Emp, [1]是Dep
for each_line in rows:
print(
each_line[0].id,
each_line[0].ename,
each_line[0].dep_id,
each_line[1].id,
each_line[1].dname
)
# 17.2 where条件(其实就是inner join)
rows = session.query(Emp, Dep).filter(Emp.id == Dep.id).all()
for each_line in rows:
emp_rows = each_line[0]
dep_rows = each_line[1]
print(emp_rows.id, emp_rows.ename, emp_rows.dep_id, dep_rows.id, dep_rows.dname)
# 17.3 inner join
# join默认就是inner join,sqlalchemy会自动通过foreign key字段去寻找联系 (个人疑点:通过foreign key,如果我没有外键呢,难道不能连或者只能通过filter)
# 但是下面这一种形式,查询结果都是Emp表的数据,没什么意义
# rows = session.query(Emp).join(Dep).all()
# # rows = session.query(Dep).join(Emp).all()
# print(rows)
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# # print(each_line.id, each_line.dname)
# 我们来改进一下
# rows = session.query(Emp.id, Emp.ename, Dep.dname, Dep.id).join(Dep).all()
# # print(rows)
# for each_line in rows:
# # print(each_line.id, each_line.ename, each_line.dname)
# print(each_line[0], each_line[1], each_line[2], each_line[3])
# =======
# 如果两个表有相同的字段,用.来取,只会取到最后一个;
# 但是可以使用索引的方式 or 使用label别名
# =======
# 17.4 外联接之左连接 left join
# emp left outer join dep
rows = session.query(Emp.id, Emp.ename, Dep.dname).join(Dep, isouter=True).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dname)
# 17.5 外链接之右连接 right join
# dep left outer join emp ---> dep 左联 emp 不就是 emp right outer join dep
rows = session.query(Emp.id, Emp.ename, Dep.dname).join(Emp, isouter=True).all()
for each_line in rows:
print(each_line.id, each_line.ename, each_line.dname)
# ====================================================================================
union
# 18. 组合 union / union all
q1 = session.query(Emp.id, Emp.ename).filter(Emp.id > 0, Emp.id < 5)
q2 = session.query(Emp.id, Emp.ename).filter(
or_(
Emp.ename.like('%海%'),
Emp.ename.like('%昊%'),
)
)
res1 = q1.union(q2).all()
res2 = q1.union_all(q2).all()
#
print(res1)
print(res2)
for each_line in res1:
for item in each_line:
print(item, end=' ')
print()
for each_line in res1:
print(each_line.id, each_line.ename)
for each_line in res2:
print(each_line.id, each_line.ename)
# 字段的使用跟谁连谁有关
# 例如:
res1 = q2.union(q1)
res2 = q2.union_all(q1)
#
for each_line in res1:
print(each_line.dname, each_line.id)
for each_line in res2:
print(each_line.dname, each_line.id)
# ==========================================================================
distinct
# distinct
rows = session.query(Dep).distinct()
for each_line in rows:
print(each_line.id, each_line.dname)
rows = session.query(distinct(Dep.dname)) # 对于distinct, 对某个字段去重后, 只能[index]形式访问
for each_line in rows:
print(each_line[0])
count
# count
rows = session.query(Dep).count()
print(rows)
first
# 所有rows里面取第一行,如果为没有结果则为None,不会抛异常
row = session.query(Emp).first()
print(row.id, row.ename, row.dep_id)
one
one 返回的rows必须是只有一行,有多行或者没有数据都会抛异常
one_or_none
one_or_none 返回的rows必须是一行或者没有数据,有多行数据就会抛异常
scalar
scalar 返回的rows必须是一行或者没有数据,有多行数据就会抛异常,并且scalar只会取第一个字段
from_self
# from_self() 相当于是 session.query(Dep).select_from(Dep)
# =====================
# SELECT anon_1.dep_id AS anon_1_dep_id, anon_1.dep_dname AS anon_1_dep_dname
# FROM (SELECT dep.id AS dep_id, dep.dname AS dep_dname
# FROM dep) AS anon_1
# ========================
rows = session.query(Dep).from_self()
for each_line in rows:
print(each_line.id, each_line.dname)
整体代码
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""表结构"""
import logging
from sqlalchemy import and_
from sqlalchemy import or_
from sqlalchemy import func
from sqlalchemy import text
from sqlalchemy import exists
from sqlalchemy import distinct
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:123@localhost:3306/sqlalchemy_db?charset=utf8mb4', max_overflow=10,
echo=True)
Base = declarative_base()
logging.captureWarnings(capture=True)
# 多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工
class Dep(Base):
__tablename__ = 'dep'
id = Column(Integer, primary_key=True, autoincrement=True)
dname = Column(String(64), nullable=False, index=True)
class Emp(Base):
__tablename__ = 'emp'
id = Column(Integer, primary_key=True, autoincrement=True)
ename = Column(String(32), nullable=False, index=True)
dep_id = Column(Integer, ForeignKey('dep.id'))
def init_db():
Base.metadata.create_all(bind=engine)
def drop_db():
Base.metadata.drop_all(bind=engine)
# drop_db()
# init_db()
Session = sessionmaker(bind=engine)
session = Session()
# session.add_all([
# Dep(dname='技术'),
# Dep(dname='销售'),
# Dep(dname='运营'),
# Dep(dname='人事')
# ])
#
# session.add_all([
# Emp(ename='林海峰', dep_id=1),
# Emp(ename='李杰', dep_id=1),
# Emp(ename='武沛齐', dep_id=1),
# Emp(ename='元昊', dep_id=2),
# Emp(ename='李钢弹', dep_id=3),
# Emp(ename='张二丫', dep_id=4),
# Emp(ename='李坦克', dep_id=2),
# Emp(ename='王大炮', dep_id=4),
# Emp(ename='牛榴弹', dep_id=3)
# ])
# where子句
# 01 filter_by
# filter_by只能传关键字参数,什么 = 什么
# rows = session.query(Emp).filter_by(ename='林海峰')
# rows = session.query(Emp).filter_by(ename='林海峰').all()
# for each_line in rows:
# print(each_line.dep_id, each_line.ename)
# 也可以传多个,逗号分隔,and关系
# rows = session.query(Emp).filter_by(dep_id=1, ename='李杰').all()
# for each_line in rows:
# print(each_line.dep_id, each_line.ename)
# =======================================================================
# 02 filer
# filter传的是表达式,
# rows = session.query(Emp).filter(Emp.id > 0)
# for each_line in rows:
# print(each_line.dep_id, each_line.ename)
# 同样可以多个,逗号分隔,默认and关系
# rows = session.query(Emp).filter(Emp.id > 0, Emp.ename == '林海峰').all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# 传参
# rows = session.query(Emp).filter(text('Emp.id > :id')).params(id=21).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# ================================================================================
# where子句其它操作符
# 03. filter ---> between ... and ...
# rows = session.query(Emp).filter(Emp.id.between(1, 3), Emp.ename == '林海峰').all()
# rows = session.query(Emp).filter(Emp.id.between(cleft=1, cright=3)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# ==================================================================================
# 04. filter ---> in
# rows = session.query(Emp).filter(Emp.id.in_(other=[1, 3, 99, 101]), Emp.ename == '林海峰').all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# 04.1 in ()
# rows = session.query(Emp).filter(Emp.id.in_(session.query(Emp.id)))
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ====================================================================================
# 05. not
# ~代表取反,转换成sql就是not
# rows = session.query(Emp).filter(~Emp.id.in_([100, 99, 2])).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# rows = session.query(Emp).filter(Emp.id.notin_([100, 99, 2])).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# =======================================================================================
# 06. and_ / or_
# 形式一: and
# rows = session.query(Emp).filter(Emp.id > 0, Emp.ename == '林海峰').all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# 形式二: and_
# and_(Emp.id > 0, Emp.ename == '林海峰') 表示Emp.id > 0 和 Emp.ename == '林海峰'之间的关系是and
# rows = session.query(Emp).filter(and_(Emp.id>0, Emp.ename == '林海峰')).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# or_
# or_(Emp.id > 0, Emp.ename == '林海峰') 表示Emp.id > 1 和 Emp.ename == '林海峰'之间的关系是or
# rows = session.query(Emp).filter(or_(Emp.id > 0, Emp.ename == '林海峰')).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# or_(Emp.id > 2, and_(Emp.dep_id == 1, Emp.id >1))表示 emp.id > 2 or emp.dep_id == 1 and emp.id > 1
# rows = session.query(Emp).filter(or_(Emp.id > 2, and_(Emp.dep_id == 1, Emp.id > 1)))
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# =================================================================================================
# 07. like 和 通配符 % _
# rows = session.query(Emp).filter(Emp.ename.like(other='%海_%')).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# 07.1 not like
# rows = session.query(Emp).filter(Emp.ename.notlike(other='%海_%')).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# 07.1 not like
# rows = session.query(Emp).filter(~Emp.ename.like('%海_%')).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ====================================================================================================
# 08. is 和 is not --> 判断null
# is
# rows = session.query(Emp).filter(Emp.ename.is_(other=None)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# is not
# rows = session.query(Emp).filter(Emp.ename.isnot(other=None)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# rows = session.query(Emp).filter(~Emp.ename.is_(other=None)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ==================================================================================================
# 09. exists
# rows = session.query(Emp).filter(exists().where(1 == 2)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# not exists
# rows = session.query(Emp).filter(~exists().where(1 == 2)).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ===========================================================================================
# limit子句
# 10. limit
# row = session.query(Emp).limit(limit=1)
# for each in row:
# print(each.id, each.ename, each.dep_id)
# row = session.query(Emp).limit(limit=2).offset(1)
# for each in row:
# print(each.id, each.ename, each.dep_id)
# rows = session.query(Emp)[0:5]
# rows = session.query(Emp)[0:5:2]
# rows = session.query(Emp)[0:5:-1] # 跟Python中有点不一样
# rows = session.query(Emp)[0:5:-2]
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ==========================================================================================
# order by子句
# 11. 排序asc / desc / asc, desc
# rows = session.query(Emp).order_by(Emp.id.desc()).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# rows = session.query(Emp).order_by(Emp.dep_id.desc(), Emp.id.asc()).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# ==========================================================================================
# 别名
# 12. label
# rows = session.query(Emp.ename.label(name='employee_name'))
# for each_line in rows:
# print(each_line.employee_name)
# # ============================
# # 能用索引, 是因为query(Emp.ename),
# # 而不是query(Emp)
# # ============================
# # print(each_line[0])
# ============================================================================================
# 子查询
# 13. 子查询
# 13.1 子查询当作一张表来用 ----> 临时表subquery
# rows = session.query(session.query(Emp.id, Emp.ename).filter(Emp.id > 2).subquery()).all()
# for each_line in rows:
# print(each_line.id, each_line.ename)
# print(each_line[0], each_line[1])
# 13.2 子查询当作in的范围用 ----> in (subquery)
# rows = session.query(Emp.ename, Emp.dep_id).filter(Emp.dep_id.in_(
# session.query(Dep.id).filter_by(dname='销售')
# ))
# print(rows)
# for each_line in rows:
# print(each_line.ename, each_line.dep_id)
# print(each_line[0], each_line[1])
# 13.3 子查询当作select后面的字段使用
# ==================================
# SELECT emp.ename AS emp_ename, (SELECT dep.dname
# FROM dep
# WHERE emp.dep_id = dep.id) AS anon_1
# FROM emp
# ==================================
# rows = session.query(
# Emp.ename,
# session.query(Dep.dname).filter(Emp.dep_id == Dep.id).as_scalar()
# ).all()
# print(rows)
# for each_line in rows:
# for item in each_line:
# print(item, end=' ')
# print()
# ===================================================================================
# 14. group by 子句
# rows = session.query(Emp.dep_id).group_by(Emp.dep_id).all()
# for each_line in rows:
# print(each_line.dep_id)
# ===================================================================================
# 15. 聚合函数
# 不管最后显示几个,最终结果都是一个元组
# rows = session.query(
# func.max(Emp.dep_id),
# func.min(Emp.dep_id),
# func.sum(Emp.dep_id),
# func.avg(Emp.id),
# func.count(1)
# ).group_by(Emp.dep_id).all()
# print(rows)
# for each_group in rows:
# for item in each_group:
# print(item, end=' ')
# print()
# ===================================================================================
# 16. having 子句
# rows = session.query(Emp.dep_id, func.count(1)).group_by(Emp.dep_id).having(func.count(1) > 2).all()
# for each_group in rows:
# for item in each_group:
# print(item, end=' ')
# print()
# ======================================================================================
# 17. 连表
# 17.1 笛卡尔积
# rows = session.query(Emp, Dep).all()
# print(rows) # 每一个元素都是一个元组,[0]是Emp, [1]是Dep
# for each_line in rows:
# print(
# each_line[0].id,
# each_line[0].ename,
# each_line[0].dep_id,
# each_line[1].id,
# each_line[1].dname
# )
# 17.2 where条件(其实就是inner join)
# rows = session.query(Emp, Dep).filter(Emp.id == Dep.id).all()
# for each_line in rows:
# emp_rows = each_line[0]
# dep_rows = each_line[1]
# print(emp_rows.id, emp_rows.ename, emp_rows.dep_id, dep_rows.id, dep_rows.dname)
# 17.3 inner join
# join默认就是inner join,sqlalchemy会自动通过foreign key字段去寻找联系 (个人疑点:通过foreign key,如果我没有外键呢,难道不能连或者只能通过filter)
# 但是下面这一种形式,查询结果都是Emp表的数据,没什么意义
# rows = session.query(Emp).join(Dep).all()
# # rows = session.query(Dep).join(Emp).all()
# print(rows)
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dep_id)
# # print(each_line.id, each_line.dname)
# 我们来改进一下
# rows = session.query(Emp.id, Emp.ename, Dep.dname, Dep.id).join(Dep).all()
# # print(rows)
# for each_line in rows:
# # print(each_line.id, each_line.ename, each_line.dname)
# print(each_line[0], each_line[1], each_line[2], each_line[3])
# =======
# 如果两个表有相同的字段,用.来取,只会取到最后一个;
# 但是可以使用索引的方式 or 使用label别名
# =======
# 17.4 外联接之左连接 left join
# emp left outer join dep
# rows = session.query(Emp.id, Emp.ename, Dep.dname).join(Dep, isouter=True).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dname)
# 17.5 外链接之右连接 right join
# dep left outer join emp ---> dep 左联 emp 不就是 emp right outer join dep
# rows = session.query(Emp.id, Emp.ename, Dep.dname).join(Emp, isouter=True).all()
# for each_line in rows:
# print(each_line.id, each_line.ename, each_line.dname)
# ====================================================================================
# 18. 组合 union / union all
# q1 = session.query(Emp.id, Emp.ename).filter(Emp.id > 0, Emp.id < 5)
# q2 = session.query(Emp.id, Emp.ename).filter(
# or_(
# Emp.ename.like('%海%'),
# Emp.ename.like('%昊%'),
# )
# )
# res1 = q1.union(q2).all()
# res2 = q1.union_all(q2).all()
#
# print(res1)
# print(res2)
# for each_line in res1:
# for item in each_line:
# print(item, end=' ')
# print()
# for each_line in res1:
# print(each_line.id, each_line.ename)
#
# for each_line in res2:
# print(each_line.id, each_line.ename)
# 字段的使用跟谁连谁有关
# 例如:
# res1 = q2.union(q1)
# res2 = q2.union_all(q1)
#
# for each_line in res1:
# print(each_line.dname, each_line.id)
#
# for each_line in res2:
# print(each_line.dname, each_line.id)
# ==========================================================================
# distinct
# rows = session.query(Dep).distinct()
# for each_line in rows:
# print(each_line.id, each_line.dname)
# rows = session.query(distinct(Dep.dname)) # 对于distinct, 对某个字段去重后, 只能[index]形式访问
# for each_line in rows:
# print(each_line[0])
# =============================================================================
# count
# rows = session.query(Dep).count()
# print(rows)
# ==============================================================================
# 所有rows里面取第一行,如果为没有结果则为None,不会抛异常
# row = session.query(Emp).first()
# print(row.id, row.ename, row.dep_id)
# ===============================================================================
# first()
# one 返回的rows必须是只有一行,有多行或者没有数据都会抛异常
# one_or_none 返回的rows必须是一行或者没有数据,有多行数据就会抛异常
# scalar 返回的rows必须是一行或者没有数据,有多行数据就会抛异常,并且scalar只会取第一个字段
# =================================================================================
# from_self() 相当于是 session.query(Dep).select_from(Dep)
# =====================
# SELECT anon_1.dep_id AS anon_1_dep_id, anon_1.dep_dname AS anon_1_dep_dname
# FROM (SELECT dep.id AS dep_id, dep.dname AS dep_dname
# FROM dep) AS anon_1
# ========================
rows = session.query(Dep).from_self()
for each_line in rows:
print(each_line.id, each_line.dname)
session.commit() # 查询可以不用commit
session.close()