mysql子句在Python3中的使用(sqlalchemy)

最新推荐文章于 2023-03-03 22:19:26 发布
最新推荐文章于 2023-03-03 22:19:26 发布
阅读量304 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql sqlalchemy 子句使用
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_32617703/article/details/103581209
版权

目录

起步

#!/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)

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),
     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()

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)[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()
 ).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()
爱喝水的qdy
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
python的else子句使用指南
09-21
Python中的`else`子句是一个独特且强大的语法结构，它不仅限于常规的`if`语句，还可以与`for`和`while`循环以及`try/except`异常处理结合使用。这种用法使得代码更加简洁，逻辑更加清晰，提高了可读性。 1. **配合`...
python能替代sql_python – SQLAlchemy使用IN子句进行原始SQL参数替换
weixin_35958479的博客
12-30 856
这是一种不寻常的格式,仅由一些DBAPI支持,因为它将一个元组的元素作为单独的SQL表达式,包括它在表达式之间呈现逗号等,所以这样的语句就像执行(“select * from table where value in ％s“,(somelist,))在数据库级别扩展为select * from表,其中(1,2,3)中的值.SQLAlchemy不期待这种格式 – 它已经对输入参数进行了一些检查,因为...
Python中利用SQLAlchemy模块操作MySQL数据库
ikkyphoenix的专栏
07-21 2511
Python中基于SQLAlchemy模块操作MySQL数据库
SQLAlchemy 框架高级查询操作and_ ,or_,in_ notin_, desc,asc,
chenmixuexi_的博客
10-11 1万+
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String # 1.创建数据库引擎，链接数据库 engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy_s14?charset=utf8')
Python 进阶（四）：数据库操作之 MySQL
apex_eixl的博客
03-03 288
MySQL 是目前使用最广泛的数据库之一，它有着良好的性能，能够跨平台，支持分布式，能够承受高并发。。Python 大致有如下 5 种方式操作 MySQL。MySQL-python 也称 MySQLdb，基于 C 库开发，曾经是一个十分流行的 MySQL 驱动，具有出色的性能，但其早已停更，仅支持 Python2，不支持 Python3，现在基本不推荐使用了，取而代之的是它的衍生版。
sqlalchemy in查询优化_SQLAlchemy(3)
weixin_39620151的博客
11-26 1119
目录`SQLAlchemy` 1.简介 2.什么是`ORM` 3.开启`MySQL`的查询日志 4.`SQLAlchemy`与数据库的逻辑对应 5.连接数据库 6.创建会话`Session` 7.创建映射(`Model`) 1.`Column`的数据类型 2.`Column`列级别约束 3.表级别约束 8.单表操作 1.表的操作-增(`insert...
简单介绍MySQL中GROUP BY子句的使用
09-10
在MySQL中，`GROUP BY`子句是用来对数据进行分组的一个重要工具，它允许我们按照一个或多个列的值将数据集分割成多个逻辑部分，以便我们可以对每个部分进行聚合函数（如COUNT, SUM, AVG, MAX, MIN）的计算。在数据库...
python mysql中in参数化说明
09-08
在使用Python连接MySQL数据库时，我们通常需要编写SQL查询，其中可能包含动态的值列表，比如`IN`子句。`IN`子句用于查询满足指定一组条件的记录。 ### 第一种方法：字符串拼接 尽管可以简单地通过字符串拼接来构造`...
使用 Python 操作 MySQL，这篇文章别错过~
静觅
02-04 424
“ 阅读本文大概需要 14 分钟。 ”1. 前言在爬虫、自动化、数据分析、软件测试、Web 等日常操作中，除JSON、YAML、XML 外，还有一些数据经常会用到，比如：Mysql、Sq...
Python和MySQL对比（1）：用Pandas 实现MySQL语法效果
明而决之
12-25 1650
本文主要介绍 MySQL 中的关键字：SELECT、AS、WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT等的查询语句，如何使用pandas实现，同时二者又有什么区别。
Python如何用filter函数筛选数据
09-17
主要介绍了Python如何用filter函数筛选数据,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
Sqlalchemy IN条件查询
热门推荐
PacMan的专栏
09-20 3万+
如果你的表比较小的话，可以对每个id单独查询，然后组成一个list： [Shoe.query.filter_by(id=id).one() for id in my_list_of_ids] 如果你的表很大，上述查询方法会很慢。此时建议使用in条件的查询 shoes = Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all() [next(s fo...
Python SQLAlchemy基本操作和常用技巧（包含大量实例,非常好）
sinat_15104757的博客
02-09 1万+
首先说下，由于最新的 0.8 版还是开发版本，因此我使用的是 0.79 版，API 也许会有些不同。 因为我是搭配 MySQL InnoDB 使用，所以使用其他数据库的也不能完全照搬本文。 接着就从安装开始介绍吧，以 Debian/Ubuntu 为例（请确保有管理员权限）： 1.MySQL 复制代码代码如下: apt-get install mysql-server a
sqlalchemy 初步认识
zhyh1435589631的专栏
05-31 3436
1. 前言1.1 ORM 模型亦即对象关系映射， ie， 将关系数据库中的业务数据用对象的形式表示出来， 并通过面向对象的方式将这些对象组织起来， 最终在应用程序中创建一个“虚拟对象数据库”1.2 SQLAlchemy 架构稳定， 高性能 1.3 说明这里仅对 ORM层的操作做一些记录， core层未涉及2. 基本操作2.1 创建数据库引擎用下面这段代码可以创建一个 sqlite 的数据库， ps
SQLAlchemy教程（四）一对多
qq_38949193的博客
09-27 1008
创建一对多的表结构 relationship的作用： relationship不会在数据库创建任何东西，作用在代码层面建立一个表之间的关系加快我们代码编写效率。 第一个参数是要关联的表，注意：这里的字符串写的是类名，不是tablename！！！ 第二个参数是反向关联的属性，意思是Hobby可通过这个属性查询Person表的数据 上面说得可能不是很清楚，看完下面的示例，应该会有更好的理...
Python操作Mysql数据库
qq_44657899的博客
03-19 1854
文章目录安装PyMySQL模块连接数据库并执行语句插入多条数据查询数据删除数据 安装PyMySQL模块 下载该模块： pip3 install PyMySQL 连接数据库并执行语句 配置文件，这里写在一个文件也可以。 dbconfig.py import pymysql dbconfig = {"host": "localhost", "port": 3306, "user": "root", "password": "yu123", "db": "cy", "charset": 'utf8',
python mysql_python操作mysql之只看这篇就够了
weixin_39925098的博客
11-21 303
满满的都是写好拿来就可以测试的代码:image.png代码下载下来, 左手鸡翅右手鼠标点鼠标运行, 5分钟学完, 岂不美哉!初始化准备安装pymysql 包sudo pip3 install PyMysql然后在mysql里创建数据库名称为 my_test, 用户名/密码也是 my_test , 并创建 Product 表如下:DROP TABLE IF EXISTS `Product`;/*!4...
Python连接MySQL并使用fetchall()方法过滤特殊字符
python数据库编程 python进阶学习
02-12 1026
这篇文章主要介绍了Python连接MySQL的方法并讲解了如何使用fetchall()方法过滤特殊字符,示例环境为Ubuntu操作系统,需要的朋友可以参考下 来一个简单的例子，看Python如何操作数据库，相比Java的JDBC来说，确实非常简单，省去了很多复杂的重复工作，只关心数据的获取与操作。 准备工作 需要有相应的环境和模块： Ubuntu 14.04 64bit Python 2.7.6 ...
mysql filter操作filter_django+mysql中的get和filter操作
weixin_35531735的博客
01-28 306
django1.11+python+mysql数据库情景：用django框架的网站用户管理部分，找回密码应用验证用户名和邮箱(将邮箱作为类密保的验证信息使用)的方法来修改用户信息的密码段数据，即验证数据库中：用户名存在且验证邮箱信息正确进行密码修改。而验证用户信息可以使用get，filter操作，如下：数据库user表内容:class user(models.Model):username = m...
mysql中group by子句的使用
最新发布
05-13
在MySQL中，GROUP BY子句用于将结果集按照一个或多个列进行分组，并对每个组计算聚合函数的值，如SUM、COUNT、AVG等。 GROUP BY子句的基本语法如下： ``` SELECT column1, column2, ... FROM table_name WHERE ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值