• SQLAlchemy的ORM


    表关系:

    表之间的关系存在三种:一对一、一对多、多对多。而SQLAlchemy中的ORM也可以模拟这三种关系。因为一对一其实在SQLAlchemy中底层是通过一对多的方式模拟的,所以先来看下一对多的关系:

    外键:

    在Mysql中,外键可以让表之间的关系更加紧密。而SQLAlchemy同样也支持外键。通过ForeignKey类来实现,并且可以指定表的外键约束。相关示例代码如下:

    class Article(Base):
        __tablename__ = 'article'
        id = Column(Integer,primary_key=True,autoincrement=True)
        title = Column(String(50),nullable=False)
        content = Column(Text,nullable=False)
        uid = Column(Integer,ForeignKey('user.id'))
    
        def __repr__(self):
            return "<Article(title:%s)>" % self.title
    
    class User(Base):
        __tablename__ = 'user'
        id = Column(Integer,primary_key=True,autoincrement=True)
        username = Column(String(50),nullable=False)
    

    外键约束有以下几项:

    1. RESTRICT:父表数据被删除,会阻止删除。默认就是这一项。
    2. NO ACTION:在MySQL中,同RESTRICT
    3. CASCADE:级联删除。
    4. SET NULL:父表数据被删除,子表数据会设置为NULL。

    一对多:

    拿之前的User表为例,假如现在要添加一个功能,要保存用户的邮箱帐号,并且邮箱帐号可以有多个,这时候就必须创建一个新的表,用来存储用户的邮箱,然后通过user.id来作为外键进行引用,先来看下邮箱表的实现:

        from sqlalchemy import ForeignKey
        from sqlalchemy.orm import relationship
    
        class Address(Base):
            __tablename__ = 'address'
            id = Column(Integer,primary_key=True)
            email_address = Column(String,nullable=False)
            # User表的外键,指定外键的时候,是使用的是数据库表的名称,而不是类名
            user_id = Column(Integer,ForeignKey('users.id'))
            # 在ORM层面绑定两者之间的关系,第一个参数是绑定的表的类名,
            # 第二个参数back_populates是通过User反向访问时的字段名称
            user = relationship('User',back_populates="addresses")
    
            def __repr__(self):
                return "<Address(email_address='%s')>" % self.email_address
    
        # 重新修改User表,添加了addresses字段,引用了Address表的主键
        class User(Base):
            __tablename__ = 'users'
            id = Column(Integer,primary_key=True)
            name = Column(String(50))
            fullname = Column(String(50))
            password = Column(String(100))
            # 在ORM层面绑定和`Address`表的关系
            addresses = relationship("Address",order_by=Address.id,back_populates="user")
    

    其中,在User表中添加的addresses字段,可以通过User.addresses来访问和这个user相关的所有address。在Address表中的user字段,可以通过Address.user来访问这个user。达到了双向绑定。表关系已经建立好以后,接下来就应该对其进行操作,先看以下代码:

      jack = User(name='jack',fullname='Jack Bean',password='gjffdd')
      jack.addresses = [Address(email_address='jack@google.com'),
                      Address(email_address='j25@yahoo.com')]
      session.add(jack)
      session.commit()
    

    首先,创建一个用户,然后对这个jack用户添加两个邮箱,最后再提交到数据库当中,可以看到这里操作Address并没有直接进行保存,而是先添加到用户里面,再保存。

    一对一:

    一对一其实就是一对多的特殊情况,从以上的一对多例子中不难发现,一对应的是User表,而多对应的是Address,也就是说一个User对象有多个Address。因此要将一对多转换成一对一,只要设置一个User对象对应一个Address对象即可,看以下示例:

      class User(Base):
        __tablename__ = 'users'
        id = Column(Integer,primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        password = Column(String(100))
        # 设置uselist关键字参数为False
        addresses = relationship("Address",back_populates='addresses',uselist=False)
      class Address(Base):
        __tablename__ = 'addresses'
        id = Column(Integer,primary_key=True)
        email_address = Column(String(50))
        user_id = Column(Integer,ForeignKey('users.id')
        user = relationship('Address',back_populates='user')
    

    从以上例子可以看到,只要在User表中的addresses字段上添加uselist=False就可以达到一对一的效果。设置了一对一的效果后,就不能添加多个邮箱到user.addresses字段了,只能添加一个:

    user.addresses = Address(email_address='ed@google.com')
    

    多对多:

    多对多需要一个中间表来作为连接,同理在sqlalchemy中的orm也需要一个中间表。假如现在有一个Teacher和一个Classes表,即老师和班级,一个老师可以教多个班级,一个班级有多个老师,是一种典型的多对多的关系,那么通过sqlalchemyORM的实现方式如下:

      association_table = Table('teacher_classes',Base.metadata,
      Column('teacher_id',Integer,ForeignKey('teacher.id')),
      Column('classes_id',Integer,ForeignKey('classes.id'))
      )
      class Teacher(Base):
        __tablename__ = 'teacher'
        id = Column(Integer,primary_key=True)
        tno = Column(String(10))
        name = Column(String(50))
        age = Column(Integer)
        classes = relationship('Classes',secondary=association_table,back_populates='teachers')
      class Classes(Base):
        __tablename__ = 'classes'
        id = Column(Integer,primary_key=True)
        cno = Column(String(10))
        name = Column(String(50))
        teachers = relationship('Teacher',secondary=association_table,back_populates='classes')
    

    要创建一个多对多的关系表,首先需要一个中间表,通过Table来创建一个中间表。上例中第一个参数teacher_classes代表的是中间表的表名,第二个参数是Base的元类,第三个和第四个参数就是要连接的两个表,其中Column第一个参数是表示的是连接表的外键名,第二个参数表示这个外键的类型,第三个参数表示要外键的表名和字段。
    创建完中间表以后,还需要在两个表中进行绑定,比如在Teacher中有一个classes属性,来绑定Classes表,并且通过secondary参数来连接中间表。同理,Classes表连接Teacher表也是如此。定义完类后,之后就是添加数据,请看以下示例:

      teacher1 = Teacher(tno='t1111',name='xiaotuo',age=10)
      teacher2 = Teacher(tno='t2222',name='datuo',age=10)
      classes1 = Classes(cno='c1111',name='english')
      classes2 = Classes(cno='c2222',name='math')
      teacher1.classes = [classes1,classes2]
      teacher2.classes = [classes1,classes2]
      classes1.teachers = [teacher1,teacher2]
      classes2.teachers = [teacher1,teacher2]
      session.add(teacher1)
      session.add(teacher2)
      session.add(classes1)
      session.add(classes2)
    

    ORM层面的CASCADE:

    如果将数据库的外键设置为RESTRICT,那么在ORM层面,删除了父表中的数据,那么从表中的数据将会NULL。如果不想要这种情况发生,那么应该将这个值的nullable=False

    SQLAlchemy,只要将一个数据添加到session中,和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢?其实是通过relationship的时候,有一个关键字参数cascade可以设置这些属性:

    1. save-update:默认选项。在添加一条数据的时候,会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
    2. delete:表示当删除某一个模型中的数据的时候,是否也删掉使用relationship和他关联的数据。
    3. delete-orphan:表示当对一个ORM对象解除了父表中的关联对象的时候,自己便会被删除掉。当然如果父表中的数据被删除,自己也会被删除。这个选项只能用在一对多上,不能用在多对多以及多对一上。并且还需要在子模型中的relationship中,增加一个single_parent=True的参数。
    4. merge:默认选项。当在使用session.merge,合并一个对象的时候,会将使用了relationship相关联的对象也进行merge操作。
    5. expunge:移除操作的时候,会将相关联的对象也进行移除。这个操作只是从session中移除,并不会真正的从数据库中删除。
    6. all:是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

    排序:

    1. order_by:可以指定根据这个表中的某个字段进行排序,如果在前面加了一个-,代表的是降序排序。
    2. 在模型定义的时候指定默认排序:有些时候,不想每次在查询的时候都指定排序的方式,可以在定义模型的时候就指定排序的方式。有以下两种方式:

      • relationship的order_by参数:在指定relationship的时候,传递order_by参数来指定排序的字段。
      • 在模型定义中,添加以下代码:

         __mapper_args__ = {
             "order_by": title
           }
        

        即可让文章使用标题来进行排序。

    3. 正向排序和反向排序:默认情况是从小到大,从前到后排序的,如果想要反向排序,可以调用排序的字段的desc方法。

    limit、offset和切片:

    1. limit:可以限制每次查询的时候只查询几条数据。
    2. offset:可以限制查找数据的时候过滤掉前面多少条。
    3. 切片:可以对Query对象使用切片操作,来获取想要的数据。slice(0,10)或 session.query(Article)[0:10]

    懒加载:

    在一对多,或者多对多的时候,如果想要获取多的这一部分的数据的时候,往往能通过一个属性就可以全部获取了。比如有一个作者,想要或者这个作者的所有文章,那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据,比如只想获取这个作者今天发表的文章,那么这时候我们可以给relationship传递一个lazy='dynamic',以后通过user.articles获取到的就不是一个列表,而是一个AppendQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。

    查询高级:

    group_by:

    根据某个字段进行分组。比如想要根据性别进行分组,来统计每个分组分别有多少人,那么可以使用以下代码来完成:

    session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
    

    having:

    having是对查找结果进一步过滤。比如只想要看未成年人的数量,那么可以首先对年龄进行分组统计人数,然后再对分组进行having过滤。示例代码如下:

    result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age >= 18).all

    join方法:

    join查询分为两种,一种是inner join,另一种是outer join。默认的是inner join,如果指定left join或者是right join则为outer join。如果想要查询User及其对应的Address,则可以通过以下方式来实现:

      for u,a in session.query(User,Address).filter(User.id==Address.user_id).all():
        print(u)
        print(a)
      # 输出结果:
      > <User (id=1,name='ed',fullname='Ed Jason',password='123456')>
      > <Address id=4,email=ed@google.com,user_id=1>

    通过普通方式的实现,也可以通过join的方式实现,更加简单:

      for u,a in session.query(User,Address).join(Address).all():
        print(u)
        print(a)
      # 输出结果:
      > <User (id=1,name='ed',fullname='Ed Jason',password='123456')>
      > <Address id=4,email=ed@google.com,user_id=1>
    

    当然,如果采用outerjoin,可以获取所有user,而不用在乎这个user是否有address对象,并且outerjoin默认为左外查询:

      for instance in session.query(User,Address).outerjoin(Address).all():
        print(instance)
      # 输出结果:
      (<User (id=1,name='ed',fullname='Ed Jason',password='123456')>, <Address id=4,email=ed@google.com,user_id=1>)
      (<User (id=2,name='xt',fullname='xiaotuo',password='123')>, None)
    

     
    我的:

        users = session.query(User.username,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()
        '''SELECT user.username AS user_username, count(article.id) AS count_1 
           FROM user INNER JOIN article ON user.id = article.uid GROUP BY user.id ORDER BY count(article.id) DESC'''

    Subquery 复杂查询:

    数据:

    查找和张三 年龄一样,城市一样的用户;

    sql:

    select user.username,user.age,user.city from user,(select user.age,user.city from user where user.username='张三') as zhang_san where user.age=zhang_san.age and user.city=zhang_san.city;
    stmp = session.query(User.age.label('age'),User.city.label('city')).filter(User.username=='张三').subquery()
        results = session.query(User.username,User.age,User.city).filter(User.age==stmp.c.age,User.city==stmp.c.city).all()
  • 相关阅读:
    Odoo13在Win10(专业版)中的配置
    我在博客园安家了
    2012笔记
    你给我好好发邮件行不行
    事务经典例子
    轻松实现SQL Server与Access、Excel数据表间的导入导出
    SQL大全
    小笔记
    性能优化
    程序中的异常和错误处理
  • 原文地址:https://www.cnblogs.com/donghaoblogs/p/10389692.html
Copyright © 2020-2023  润新知