sqlalchemy表关联查询

一. 一对多，一对一，多对多

参考文档: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many 或者=>
https://www.cnblogs.com/jasonwang-2016/p/5980237.html

1. 一对多

一对多使用的很多了，略
一对一只需要在一对多关系基础上使用uselist=False参数

2. 多对多

参考文档: https://www.cnblogs.com/wuheng-123/p/9706047.html

域名解析为负载均衡器与主机多对多的关系:

...

DnsRecord_HostIp = Table(
  'dnsrecord_hostip',
  Base.metadata,
  Column('dns_record_id',Integer, ForeignKey('dns_record.id'), primary_key=True),
  Column('host_ip_id',String(32), ForeignKey('host_ip.ID'), primary_key=True),
  #两字段primary_key都等于True,组合主键唯一，防止内容一样
)

class Host_Ip(Base):
  __tablename__ = 'host_ip'
  ID = Column(String(32),nullable=False, unique=True, primary_key=True)
  name = Column(String(32))
  ipv4_external = Column(String(20), index=True)
  ipv4_internal = Column(String(20), nullable=False)
  vpc_id = Column(String(32), ForeignKey('vpc.ID'), nullable=False)
  vpc = relationship(Vpc, backref='host_ip')

class Dns_Record(Base):
  __tablename__ = 'dns_record'
  id = Column(Integer, primary_key=True, autoincrement=True)
  domain_id = Column(Integer, ForeignKey('domain.id'), nullable=False)
  domain = relationship(Domain, backref='dns_record')
  project_name = Column(String(25), server_default='默认项目')
  type = Column(String(6), nullable=False)
  value = Column(String(32), nullable=False)
  TTL = Column(SmallInteger, server_default='600', nullable=False)
  # 以下也可在程序中运行得到
  loadbalancer_id = Column(String(32), ForeignKey('loadbalancer.ID'))
  loadbalancer = relationship(Loadbalancer, backref='dns_record')
  host_ip_id = Column(String(32), ForeignKey('host_ip.ID'))
  host_ip = relationship(Host_Ip, backref='dns_record')
  # 多对多关联关系,注意此处与上面host_ip的不同
  host_ip2 = relationship(Host_Ip, backref='dns_record2',secondary='dnsrecord_hostip')  # secondary,中间表

注意：host_ip是一对多关联关系，而hpst_ip2是多对多的关联关系
host_ip2的关联关系表示，当解析到负载均衡器时，负载均衡器绑定的后端主机关联关系

多对多.png

二. sqlalchemy之分组查询

参考文档: https://www.jianshu.com/p/0bab8f7cfdfd

查询dns解析记录表中解析多余1条的记录:
SQL语法:
select domain_id,count(*) from dns_record group by domain_id HAVING count(*)>1
sqlalchemy中如下:

from sqlalchemy import func

......
  # 分组查询测试
  def tt(self):
    #group_list = self.session.query(Dns_Record.domain_id, func.count(Dns_Record.domain_id)).group_by(Dns_Record.domain_id).all() 

    nums = func.count('*').label('c')    #显示名'c'
    group_list = self.session.query(Dns_Record.domain_id, nums).group_by(Dns_Record.domain_id).having(nums>1).all() 
    for v in group_list:
      print(v.domain_id, v.c)       #是v.c，不是v.nums

输出结果:

sqlalchemy表关联查询

一. 一对多， 一对一，多对多

1. 一对多

2. 多对多

二. sqlalchemy之分组查询

一. 一对多，一对一，多对多