一. 一對多， 一對一，多對多

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

1. 一對多

一對多使用的很多了，略
一對一只需要在一對多關(guān)系基礎(chǔ)上使用uselist=False參數(shù)

2. 多對多

參考文檔: https://www.cnblogs.com/wuheng-123/p/9706047.html

域名解析為負載均衡器與主機多對多的關(guān)系:

...

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,組合主鍵唯一，防止內(nèi)容一樣
)

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')
  # 多對多關(guān)聯(lián)關(guān)系,注意此處與上面host_ip的不同
  host_ip2 = relationship(Host_Ip, backref='dns_record2',secondary='dnsrecord_hostip')  # secondary,中間表

注意：host_ip是一對多關(guān)聯(lián)關(guān)系，而hpst_ip2是多對多的關(guān)聯(lián)關(guān)系
host_ip2的關(guān)聯(lián)關(guān)系表示，當解析到負載均衡器時，負載均衡器綁定的后端主機關(guān)聯(lián)關(guān)系

多對多.png

二. sqlalchemy之分組查詢

參考文檔: http://m.itdecent.cn/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

輸出結(jié)果:

207 2
223 2
225 2
227 2
229 2
253 2