4、MySQL数据库开发四

发表于： 2019年6月25日 2023年1月30日
分类： MySQL数据库开发

外键约束
一对多
创建表结构
新增数据
relationship
一对多
新增数据(正向)
新增数据(反向)
数据查询(正向)
数据查询(反向)
多对多
数据新增
数据查询(正向)
数据查询(反向)
数据删除
连表查询
数据懒加载
测试懒加载
总结

TOC

外键约束

    在此之前，学习的都是单表之间的CURD，SqlAlchemy将表中的一条一条数据映射成一个一个实体，所以使用起来也非常简单，直接使用Python的语法就能够完成基本的增删改查工作；
    在关系型数据库中，还有较为复杂的一些表结构，就是我们所谓的外键，创建外键约束可以让表与表之间的关系更加紧密，外键又分很多种类型，他们分别是，一对一、一对多和多对多，那么对于CURD的语法难度也就上升了一个层次，但这也是不可避免的，我们在项目开发中，是绝对会有这种场景的，并且使用量非常之大；
    而SqlAlchemy同样支持外键，在定义字段时，只需要在Column中给定一个ForeignKey的类来实现表与表之间的关系建立，从而实现一对一、一对多和多对多，但是需要知道的是，这个ForeignKey在Column中的位置，还必须位于type_参数之后；

column:指定外键字段，即，需要建立外键关系的字段；
name:此外键在当前数据库中的字段名；
ondelete:及联操作，表示，当关联表发生变化时，关系表中关联的那条数据需要采取的动作，有如下几种选项；
    RESTRICT：父表数据被删除，会阻止删除；
    NO ACTION：在MySQL中，同RESTRICT；
    CASCADE：级联删除；
    SET NULL：父表删除，子表数据会跟着删除；

一对多

    一对多，顾名思义就是说，一张表与另一张表使用外键的方式将两张表建立关系，它是两个表之间的关系，在日常开发中使用也非常的频繁，比如，一个系统有用户和角色的概念，那么一般来讲，我们在设计表结构时，会将用户和角色进行拆分，拆分成两个独立的表，那么因为用户和角色一般都是需要进行绑定的，所以我们一般会在用户表中，建立一个与角色表的关联；

创建表结构

    一对多的表结构创建起来也非常简单，我们只需要在一张表中使用ForeignKey与另一张表进行关联即可，这里需要注意的是，ondelete和onupdate两个参数的设置，一般来讲都是CASCADE；

from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+pymysql://cce:caichangen@47.104.197.46/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = declarative_base()

class Users(BaseModel):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, comment="员工ID")
    name = Column(String(14), nullable=False, comment="员工名称")
    gender = Column(Enum("M", "F"), nullable=False, comment="员工性别")
    age = Column(String(12), nullable=False, comment="员工年龄")
    role_id = Column(Integer,ForeignKey("roles.id",ondelete="CASCADE"),comment="所属角色ID") # 创建外建关系
    # 因为Python是自上而下执行代码的，所以这里ForeignKey的column字段就以字符串的方式表示关联的外键字段

    def __repr__(self):
        return "<%s %s %s>" % (__class__.__name__, self.id, self.name)

class Roles(BaseModel):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True, comment="角色ID")
    name = Column(String(14), nullable=False, comment="角色名称")

    def __repr__(self):
        return "<%s %s>" % (__class__.__name__, self.name)

if __name__ == '__main__':
    BaseModel.metadata.drop_all(engine)
    BaseModel.metadata.create_all(engine)

新增数据

    这里的数据新增分为两步，第一步将角色表创建一条数据，然后将这条数据所属的id给拿到进行新增用户表数据，使用角色ID与用户进行关联，实现一对多关联；

# 创建角色
role1=Roles(name="管理员")
session.add(role1)
session.commit()
# 创建用户，并绑定角色
role_id=session.query(Roles).filter(Roles.name=="管理员").first()
user1=Users(name="cce",gender="M",age=26,role_id=role_id.id)
session.add(user1)
session.commit()
session.close()

relationship

    relationship，键名知意关系，它的主要作用是为了我们在一对一、一对多和多对多的表结构中借助面向对象的思想进行更加高效的方式进行数据的增删改查，在日常开发中基本可以说是必用功能之一；

# relationship参数：
table：需要关联的表，表示需要与哪个表进行关联，与ForeignKey的表明一致，尽量使用字符串的方式表示；
backref：反向CURD字段名，在一对一，和一对多的情况下使用，表示在关联表中查询当前表所使用的字段名；
uselist：如果为False，不使用列表，而使用标量值；
order_by：指定排序字段；
secondary：用来指明中间的关系表，构建额外多对多关系的表；
cascade：
    save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中，这种行为就是save-update属性影响的；
    delete：表示当删除某一个模型中的数据的时候，同时也删掉使用relationship和他关联的数据；
    delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，并且还需要在子模型中的relationship中，增加一个single_parent=True的参数；
    merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作；
    expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据；
    all：是对save-update，merge，refresh-expire，expunge，delete几种的填写；

    relationship也是建立多个表之间的关系，但是需要知道的是它和ForeignKey并不冲突，两者还必须共存，ForeignKey是在数据库层面进行关联，而relationship则是在ORM层面进行关联，relationship的建立并不会影响表结构的变化，所以说白了，它主要是为程序员来提供的，方便程序员能够快速进行数据库开发，如下就是；

一对多

    对于relationship创建一对多，就有一个要求，就是在多的一方使用ForeignKey，而relationship可以在任意一方，一般来讲，都会将relationship放在一的一方，并且在relationship里面加入backref实现反向操作，如下，一个角色下面有多个用户；

from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+pymysql://cce:caichangen@47.104.197.46/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = declarative_base()

class Roles(BaseModel):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True, comment="角色ID")
    name = Column(String(14), nullable=False, comment="角色名称")
    user_external=relationship("Users",backref="role_external")
    # 正向字段名为role_external，反向字段名为user_external，有ForeignKey为正向

    def __repr__(self):
        return "<%s %s>" % (__class__.__name__, self.name)

class Users(BaseModel):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, comment="员工ID")
    name = Column(String(14), nullable=False, comment="员工名称")
    gender = Column(Enum("M", "F"), nullable=False, comment="员工性别")
    age = Column(String(12), nullable=False, comment="员工年龄")
    role_id = Column(Integer,ForeignKey("roles.id",ondelete="CASCADE"),comment="所属角色ID") # 创建外建关系

    def __repr__(self):
        return "<%s %s %s>" % (__class__.__name__, self.id, self.name)

if __name__ == '__main__':
    BaseModel.metadata.drop_all(engine)
    BaseModel.metadata.create_all(engine)

注意：需要知道的是，多的一方需要建立ForeignKey，而relationship可以在任意一方，一般relationship是在一的一方；

新增数据(正向)

    可以看到，当我们加入了relationship之后，表结构是没有任何变化的，所以说，它仅仅是ORM层面建立的一种关系，那么在这种关系建立之后，以往新增数据的语法也变得更加的简洁，我们可以直接通过当前表通过relationship建立的关系去创建外键表，非常方便；
    那么正向是什么意思呢，relationship是有正向和反向这么一说的，正向就是使用relationship所在的表去查询关联表，而反向就是使用关联表来查询relationship所在字段的表；
    那么可以看到下面，我们在当前表的情况下，我们直接省略了外键字段，这是主要是relationship给我们提供功能，会自动推断这个外键字段，我们只需要给定relationship所在字段就行了，它会自动将relationship这个字段里面的关联字段赋予给外键字段，用下面的例子换句话说，就说relationship会自动将新建的这个Roles实体的id值，赋予到Users这个实体中；

role=Roles(name="管理员") # 创建多的一方，角色
user1=Users(name="cce",gender="M",age=26) # 创建一的一方，用户
user2=Users(name="csw",gender="M",age=40) # 创建一的一方，用户
role.user_external.extend([user1,user2]) # role.user_external是一个空元祖，所以这里直接使用元组的方式添加即可
session.add(role)
session.commit()
session.close()

新增数据(反向)

    上面说过relationship给我们提供了强大的关系表操作接口，给我们在较为复杂的表结构场景下提供了非常大的便利，同时它不仅提供了正向操作(relationship所在的一方)，也给我们提供了反向操作(关联方)，反向操作就是在关联表能够直接操作ForeignKey所在的表；

role=Roles(name="管理员") # 创建多的一方，角色
user1=Users(name="cce",gender="M",age=26,role_external=role) # 创建一的一方，用户，并指定角色
user2=Users(name="csw",gender="M",age=40,role_external=role) # 创建一的一方，用户，并指定角色
session.add_all([user1,user2])
session.commit()
session.close()

数据查询(正向)

    删除数据也一样，当我们使用正向查询时，我们首先会拿到一个user实体，这个用户实体里面有一个，字段为role_external，它只指向一个role实体，我们直接取这个实体就能拿到这个用户所属的角色信息；
    换句话说，当我们想要正向查询user表中的第一条数据的所属组时，在relationship的特性下，我们只需要查询出user第一条数据即可，ORM会将他们包装成一个实体，然后这个实体因为relationship的加持，会加入一个role_external字段，它代表的就是这个用户实体，所属的角色实体；

role=session.query(Roles).first()
print(role.name) # 管理员
print(role.user_external) # [<Users 1 cce>, <Users 2 csw>]
session.close()

数据查询(反向)

    反向查询，就是在多的一方进行查询，即ForeignKey所在的一方，这样会查询多个结果出来，得到的是一个列表，列表里面的元素就是Users的实体，同时这个实体里面也包含了当前Users实体对应的Role实体，这就是一对多的反向查询操作，稍微有点鸡肋，如下；

users = session.query(Users).filter(Users.role_id == 1).all()
for user in users:
    print(user.name,user.role_external) # 使用role_external拿到对应的Roles实体
# cce <Roles 管理员>
# csw <Roles 管理员>

    修改和删除就没什么好说的了，照常进行就行，但是要看清楚cascade，避免数据误删；

多对多

    多对多的表结构在日常开发也是非常常见的一种，他主要是为了将多个表，通过一个中间表进行关联，比如一篇文章，他可能有多个作者，那么一个作者一般也可能会有多篇文章，那么对于这种场景就非常适用多对多的表结构，如下，通过用户和角色来建立多对多关系，一个系统平台，一个用户可能有多个角色，一个角色一般又存在多个用户；
    角色和用户的多对多使用第三张表来实现，这第三张表需存在两个外键，第一个为用户的ID，第二个为角色ID，并且在主表里面加上relationship并且使用secondary指定多对多的表名，这样，在主表里面进行多对多查询就方便多了；

from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+pymysql://cce:caichangen@47.104.197.46/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = Base = declarative_base()


class Roles(BaseModel):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True, comment="角色ID")
    name = Column(String(64), nullable=False, comment="角色名称",unique=True)
    user = relationship("Users", secondary="role_user",backref="role")

class Users(BaseModel):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, comment="员工ID")
    name = Column(String(64), nullable=False, comment="员工名称")

class RoleUser(BaseModel):
    __tablename__ = "role_user"
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), comment="所属用户ID",primary_key=True)
    roles_id = Column(Integer, ForeignKey("roles.id", ondelete="CASCADE"), comment="所属角色ID",primary_key=True)

if __name__ == '__main__':
    BaseModel.metadata.drop_all(engine)
    BaseModel.metadata.create_all(engine)

数据新增

    对于多对多来讲，两方都是以列表来表示，所以多对多在进行添加数据时，直接使用列表的方式进行多对多关联即可；

# 创建用户
user1=Users(name="cce")
user2=Users(name="csw")
user3=Users(name="cfj")
# 创建角色
role1=Roles(name="管理员")
role2=Roles(name="操作员")
# 建立关系
user1.role=[role1,role2] # cce用户即是管理员也是操作员
user2.role.append(role1) # csw用户是管理员
user3.role.append(role2) # cfj用户是操作员
# 提交事务
session.add_all([user1,user2,user3])
session.commit()

数据查询(正向)

    对于正向的数据查询，也较为简单其实和一对多是一样的，因为对于查询来讲，查询的一方，不管是一对多还是多对多，一般都是一，所以直接拿到一方的实体，然后使用关联字段拿到关联实体即可；

roles=session.query(Roles).all()
for role in roles:
    print(role,role.user)
# 操作员 [cce, cfj]
# 管理员 [cce, csw]

数据查询(反向)

    对于反向查询吧，其实没啥好说的，就是反着查就行了，只不过反向查询对于多对多来讲，也是一个列表，因为两方都是多；

user=session.query(Users).get(1)
print(user,user.role) # cce [操作员, 管理员]

数据删除

    因为多对多，对应的关系表是一个列表，所以在删除数据的时候，我就直接调用列表的remove()方法即可，这里主要注意的是cascade规则是怎么样的；

user=session.query(Users).filter(Users.name=='cce').first()
print(user.role) # [操作员, 管理员]
role=session.query(Roles).filter(Roles.name=='管理员').first()
user.role.remove(role)
session.commit()
user=session.query(Users).filter(Users.name=='cce').first()
print(user.role) # [操作员]

连表查询

    对于连表查询，SqlAlchemy也为我们提供了相关的方法，即join()，JOIN有三种，LEFT JOIN、RIGHT JOIN和INNER JOIN，默认情况下我们使用的都是INNER JOIN内链接，只有进行连接的两个表中，都存在与连接标准相匹配的数据才会被保留下来，相当于两个表的交集。如果前后连接同一张表，也叫自连接；

# 写法一
print(session.query(Users,RoleUser).join(RoleUser).filter(Users.id==1))
# SELECT users.*,role_user.* FROM users INNER JOIN role_user ON users.id = role_user.user_id WHERE users.id = 1;
# 写法二
print(session.query(Users,RoleUser).join(RoleUser,Users.id==RoleUser.user_id).filter(Users.id==1))
# SELECT users.*,role_user.* FROM users INNER JOIN role_user ON users.id = role_user.user_id WHERE users.id = 1;
# 写法三
print(session.query(Users,RoleUser).join(RoleUser,(Users.id==RoleUser.user_id)&(Users.id==1)))
# SELECT users.*, role_user.* FROM users INNER JOIN role_user ON users.id = role_user.user_id AND users.id = 1;

# [(<Users cce>, <RoleUser>), (<Users cce>, <RoleUser>)]
# [(<Users cce>, <RoleUser>), (<Users cce>, <RoleUser>)]
# [(<Users cce>, <RoleUser>), (<Users cce>, <RoleUser>)]

    可以看到INNER JOIN三种写法最后转换的SQL，都是一样的，所以这里主要看个人习惯，用哪一种都可以；

数据懒加载

    在一对多或者多对多的场景下，如果在获取多这一部分数据的时候，往往能通过一个relationship映射属性就可以直接获取全部关联数据，并且直接能拿到所有的数据，例如我们想查询一个角色下面有多少个用户，我们就可以直接通过这个角色下面的relationship映射字段拿到，当前角色的所有用户；
    但是在有的场景下，我们并不想直接拿到所有的用户，我们只想拿到部分数据，这就有问题，因为可能这个组下面有上千万人，我们直接拿到，那带来的问题就是灾难性的，所以，在这种场景下，我们就可以借助relationship的lazy参数来实现，该参数的之如果是dynamic，那么它就会返回一个AppenderQuery对象，既然是一个AppenderQuery对象，我们就可以直接用这个对象来查询，而不是直接拿到全部数据；

lazy参数：
    select：这个是默认选项，还是拿下面的user.article的例子来讲，如果没有访问user.article这个属性，那么sqlalchemy就不会从数据库中查找文章，一旦你访问了这个属性，那么sqlalchemy就会立马从数据库中查找所有文章，并把他们组成一个列表返回，这也是懒加载的一中表现；
    dynamic：其他特性和select差不多，就是返回的不是一个列表，而是一个AppenderQuery对象；

    下述模型，引用自上述一对多模型，在上述的模型下稍有改动；

from sqlalchemy import Column, String, Integer, Enum, DATE, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+pymysql://cce:caichangen@192.168.1.134/cce?charset=utf8", encoding='utf-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
BaseModel = declarative_base()

class Roles(BaseModel):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True, comment="角色ID")
    name = Column(String(14), nullable=False, comment="角色名称")
    user_external=relationship("Users",backref="role_external",lazy="dynamic") # 定义lazy属性

    def __repr__(self):
        return "<%s %s>" % (__class__.__name__, self.name)

class Users(BaseModel):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, comment="员工ID")
    name = Column(String(14), nullable=False, comment="员工名称")
    role_id = Column(Integer,ForeignKey("roles.id",ondelete="CASCADE"),comment="所属角色ID") # 创建外建关系

    def __repr__(self):
        return "<%s %s %s>" % (__class__.__name__, self.id, self.name)

if __name__ == '__main__':
    BaseModel.metadata.drop_all(engine)
    BaseModel.metadata.create_all(engine)

测试懒加载

    可以看到，当我们通过relationship拿到关联字段后，不再是一个对象列表，而是一个AppenderQuery对象，既然是一个AppenderQuery对象，我们就可以在此基础之上，做任何合法的查询操作，如下；

# 添加数据(引用上述一对多模型)
role=Roles(name="管理员") # 创建多的一方，角色
user1=Users(name="cce") # 创建一的一方，用户
user2=Users(name="csw") # 创建一的一方，用户
role.user_external.extend([user1,user2]) # role.user_external是一个空元祖，所以这里直接使用元组的方式添加即可
session.add(role)
session.commit()
# 测试
role=session.query(Roles).first()
print(type(role.user_external)) # <class 'sqlalchemy.orm.dynamic.AppenderQuery'>
# 测试查询
print(role.user_external.filter(Users.name=="cce").first()) # <Users 1 cce>
session.close()

总结

    relationship不仅给我们简化了CURD操作流程，还给我们提供了正向操作和反向操作，极大的给我们的项目开发提供了便利，同时这里再次说明，relationship只是构建在ORM层面的一种关系映射，它仅在ORM层面，主要是为了方便程序员进行负责的数据CURD工作，它在数据库层面来讲，是没有任何意义的；
    relationship只是一个ORM层面的关系映射，所以，它并不能代替ForeignKey，它的出现还必须配合ForeignKey才行，只有在有外键的情况下才能使用；
    那么同时relationship，在进行CURD时，我们不需要显式的去传递ForeignKey字段的值，我们只需要给定relationship对应的关联对象即可，relationship会给我们自动推断这个ForeignKey的值；

cce

142

外键约束

一对多

创建表结构

新增数据

relationship

一对多

新增数据(正向)

新增数据(反向)

数据查询(正向)

数据查询(反向)

多对多

数据新增

数据查询(正向)

数据查询(反向)

数据删除

连表查询

数据懒加载

测试懒加载

总结

cce

发表回复 取消回复

发表回复取消回复