数据存储 | MySQL —— ORM框架的使用（python：sqlalchemy库）

目录

1.参考博客

2.实战代码

---

1.参考博客

https://www.cnblogs.com/wupeiqi/articles/5713330.html

2.实战代码

# ----------------------------------------- ORM框架测试
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import and_, or_
from sqlalchemy.sql import func


engine = create_engine("mysql+pymysql://maxin:111@192.168.116.133:3306/t1", max_overflow=5)
Base = declarative_base()


# - 定义表结构
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(32), nullable=False, default='')
    extra = Column(String(16), nullable=True, default='')
    number = Column(Integer, nullable=False, default=0)

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uiq_id_name'),
        Index('idx_name_extra', 'name', 'extra')
    )


class Favor(Base):
    __tablename__ = 'favors'
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(32), nullable=False, default='')
    extra = Column(String(16), nullable=True, default='')
    number = Column(Integer, nullable=False, default=0)

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uiq_id_name'),
        Index('idx_name_extra', 'name', 'extra'),
    )


def init_db():
    Base.metadata.create_all(engine)


def drop_db():
    Base.metadata.drop_all(engine)


# - 连接数据库
Session = sessionmaker(bind=engine)
sess = Session()


# - [建表]
drop_db()
init_db()


# ============================================== 增
obj = User(name="maxin0", extra="male", number=0)

sess.add(obj)
sess.add_all([
    User(name="maxin1", extra='male', number=1),
    User(name="maxin2", extra='male', number=2),
])
sess.commit()

# ============================================== 查
# filter()：内部为表达式
# filter_by():内部传入的是等式类型的字典
# filter(,): 内部默认为and操作
res = sess.query(User).all()
res = sess.query(User.name, User.extra).all()
# - 条件查询
res = sess.query(User).filter_by(number=0).all()
res = sess.query(User.id, User.name, User.extra).filter(User.id > 2).all()
res = sess.query(User.id, User.name, User.extra).filter(User.id.between(2, 3), User.name == 'maxin1').all()
res = sess.query(User.id, User.name, User.extra).filter(User.id.in_([2, 3, 4]), User.name == 'maxin1').all()
res = sess.query(User.id, User.name, User.extra).filter(User.id.notin_([2, 3, 4]), User.name == 'maxin1').all()
res = sess.query(User.id, User.name, User.extra).filter(User.id.in_(sess.query(User.id).filter_by(name='maxin1'))).all()
res = sess.query(User.id, User.name, User.extra).filter(and_(User.id > 2, User.number < 2)).all()
res = sess.query(User.id, User.name, User.extra).filter(or_(User.id > 2, User.number < 2)).all()
res = sess.query(User.id, User.name, User.extra).filter(or_(User.id > 2, and_(User.id > 3, User.name == 'maxin1'))).all()

# - 通配符查询
res = sess.query(User).filter(User.name.like('%maxin%')).all()
res = sess.query(User.name).filter(User.name.like('%maxin%')).all()
res = sess.query(User.name).filter(~User.name.like('%maxin1%')).all()

# - 限制查询
res = sess.query(User)[0:2]
res = sess.query(User.id, User.name)[0:2]
res = sess.query(User).filter(User.id > 2).all()[0:1]
res = sess.query(User.id, User.name).filter(User.id > 2).all()[0:1]

# - 排序查询
res = sess.query(User).order_by(User.number.desc()).all()
res = sess.query(User.id, User.name, User.number).order_by(User.number.desc()).all()
res = sess.query(User.id, User.name, User.number).filter(User.id > 1).order_by(User.number.desc()).all()
res = sess.query(User.id, User.name, User.number).order_by(User.number.desc(), User.id.asc()).all()

# - 分组/聚合查询
res = sess.query(User.number).group_by(User.number).all()
res = sess.query(
    User.number,
    func.count(1),
    func.sum(User.id),
    func.max(User.id),
    func.min(User.id),
).group_by(User.number).all()
res = sess.query(
    User.number,
    func.count(1),
    func.sum(User.id),
    func.max(User.id),
    func.min(User.id),
).group_by(User.number).having(func.min(User.id) > 2).all()

# - 连表查询
res = sess.query(User, Favor).all()  # 笛卡尔积
res = sess.query(User, Favor).filter(User.id == Favor.id).all()  # 内连接
res = sess.query(User).join(Favor).all()  # 内连接
res = sess.query(User).join(Favor, isouter=True).all()  # 左连接
res = sess.query(Favor).join(User, isouter=True).all()  # 右连接

# - union组合
res1 = sess.query(User.name).filter(User.id < 4)
res2 = sess.query(User.name).filter(User.id < 4)
res3 = res1.union(res2).all()  # 去重组合
res4 = res1.union_all(res2).all()  # 不去重组合
print(res3)
print(res4)

# - 嵌套查询
# - select * from (select * from A) as B;
res1 = sess.query(User).filter(User.id < 4).subquery()
res = sess.query(res1).all()
print(res)
# - select id, (select number from A where A.id=B.id) from B
res = sess.query(User.id, User.name, sess.query(Favor.number).filter(Favor.id==User.id).as_scalar()).all()
print(res)
# - select id from A where A.id in (select id from B)
res = sess.query(Favor.id).filter(Favor.id.in_(sess.query(User.id).subquery())).all()
print(res)

# ============================================== 删[先查再删]
sess.query(User).filter(User.id < 2).delete()
sess.commit()
res = sess.query(User.id).all()
print(res)


# =============================================== 改[先查再改]
# - 统一更新
sess.query(User).filter(User.id < 4).update({'name': '你好'})
sess.query(User).filter(User.id < 4).update({User.name: 'haha'})

# - 分别更新
# synchronize_session: 调整更新的类型
# synchronize_session=False：字符类型
# synchronize_session='evalute'：数字类型
sess.query(User).filter(User.id < 4).update({"name": User.name + 'x'}, synchronize_session=False)
sess.query(User).filter(User.id < 4).update({User.name: User.name + 'x'}, synchronize_session=False)
sess.query(User).filter(User.id < 4).update({"number": User.number + 1}, synchronize_session="evaluate")
sess.commit()
res = sess.query(User.id, User.name, User.number).all()
print(res)


# =============================================== 关系
# 1.导入必要的库文件
from sqlalchemy.orm import relationship

# 2.在外键所在列中添加关系
class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    nm = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))

    # relationship(): 创建一个关系
    # 'Favor': 关系对应的类(表)
    # 'backref=': 给外键参照的类(表)用的关系名，相当于Favor_content
    Favor_content = relationship('Favor', backref='xxoo')

# 3.实战代码
# 3.1.查询每个人的姓名及其对应的爱好名称(需要left join 连表)
# # 3.1.1.传统方法
# person_list = sess.query(Person.nm, Favor.name).join(Favor, isouter=True).all()
# for row in person_list:
#     print(row.nm, row.name)
# 3.1.1.关系方法
person_list = sess.query(Person)
for row in person_list:
    print(person_list.nm, person_list.Favor_content.name)
# 3.2.查询每个爱好对应的人名
# # 3.2.1.传统方法
# favor_list = sess.query(Favor)
# for row in favor_list:
#     print(row.id,(sess.query(Favor).filter(Favor.id==row.id).all()))
# 3.2.2
favor_list = sess.query(Favor)
for row in favor_list:
    print(row.id, row.xxoo.name)