python - SQLAlchemy 中关系定义的几个问题( 一对一 / 多对多 )

Question

2016/11/11

问题

官网关于这一部分的讲解, 不是很详细, 尤其是拿 Parent 和 Child 作为比喻, 感觉不是很形象, 我基于自己的理解,写了对应的的 demo, 但是不是很清楚:

1. 是否符合最佳实践

2. 是否漏掉了某些最佳实践?

我看书, 了解到, 实际生产中, 虽然外键可以降低开发成本, 但是许多情况下不推荐外键来关联, 另有其他方法, 不知这句话的内涵是什么?

相关代码

• 多对多

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import (Table, Column, Integer, String, Numeric, DateTime)
from sqlalchemy.orm import relationship
from sqlalchemy import ForeignKey

engine = create_engine('sqlite:///:memory:')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Many1(Base):
    __tablename__ = 'many1'
    id = Column(Integer, primary_key=True, autoincrement=True)
    attr1 = Column(String, nullable=False)

    many2 = relationship('Many2', back_populates='many1', secondary=many1_many2_table) # 1?? 定义正确?

class Many2(Base):
    __tablename__ = 'many2'
    id = Column(Integer, primary_key=True, autoincrement=True)
    attr2 = Column(String, nullable=False)

    many1 = relationship('Many1', back_populates='many2', secondary=many1_many2_table) # 2?? 定义正确?

many1_many2_table = Table('many1_many2', Base.metadata,
                          Column('many1_id', Integer, ForeignKey('many1.id')),
                          Column('many2_id', Integer, ForeignKey('many2.id')))                         
          
1. 查询 m1 身上所有的 Many2   ( m1 是 Many1 的实例 )
    a. return m1.many2  这种能写吗, 其实这种很直观! Elixir中能够这样写
    b. session.query(Many2).filter(Many2.many1 == m1).all()
    
2. 定义多对多, 需要额外定义一张关联表, 难道没有一劳永逸的办法吗? ( 生产数据库中可不止几张关系表, 都要手写? )

• 一对一

class One1(Base):
    __tablename__ = 'one1'
    id = Column(Integer, primary_key=True, autoincrement=True)
    attr1 = Column(String, nullable=False)
    
    one2 = relationship('One2', back_populates='one1', uselist=False) # 1?? 看官网文档, 只需要添加 uselist 这个参数即可

class One2(Base):
    __tablename__ = 'one2'
    id = Column(Integer, primary_key=True, autoincrement=True)
    attr2 = Column(String, nullable=False)
    
    one1_id = Column(Integer, ForeignKey('one1.id'))

Answer 1

Thanks for the invitation.
SQLAchemy’s ORM is indeed not as simple to use as Django.
For many-to-many relationships, it is indeed necessary to define the association table with the thread. When additional fields need to be added to the association table, Django also needs to define the Model that manages the table.

After defining the many-to-many relationship, querying is more convenient.
For example, query all Many2 objects corresponding to Many1 object m1:

1. You can write directly:

   return m1.many2 # Returns an array of Many2 objects

2. Or a very lengthy query like this:

   return session.query(Many2).filter(Many2.many1.contains(m1)).all()

3. Another method is to define the association table Class (instead of directly defining the management table), and then directly query the association table.

Use your many-to-many relationship definition example below:

if __name__ == '__main__':                                                                                                                      
    engine = create_engine('sqlite:///:memory:')                                                                                                    
    metadata = Base.metadata                                                                                                                        
    metadata.create_all(engine)                                                                                                                     
                                                                                                                                                      
    Session = sessionmaker(bind=engine)                                                                                                             
    session = Session()                                                                                                                             
    session.add_all([                                                                                                                               
        Many1(id=10, attr1='<10>', many2=[                                                                                                          
            Many2(id=20, attr2='<20>'),                                                                                                             
            Many2(id=40, attr2='<40>'),                                                                                                             
        ]),                                                                                                                                         
        Many1(id=11, attr1='<10>', many2=[                                                                                                          
            Many2(id=60, attr2='<60>'),                                                                                                             
            Many2(id=80, attr2='<80>'),                                                                                                             
        ]),                                                                                                                                         
        Many2(id=100, attr2='<100>', many1=[                                                                                                        
            Many1(id=12, attr1='<12>'),                                                                                                             
            Many1(id=13, attr1='<13>'),                                                                                                             
            Many1(id=14, attr1='<14>'),                                                                                                             
        ])                                                                                                                                          
    ])                                                                                                                                              
    session.commit()                                                                                                                                
    # 1. Nice query                                                                                                                                 
    print '####Nice query####'                                                                                                                      
    m1 = session.query(Many1).get(10)                                                                                                               
    for m2 in m1.many2:                                                                                                                             
        print m2.id, m2.attr2                                                                                                                       
    # 2. Bad query                                                                                                                                  
    print '####Bad query####'                                                                                                                       
    for m2 in session.query(Many2).filter(Many2.many1.contains(m1)).all():                                                                          
        print m2.id, m2.attr2

In addition, in your definition of 1 to 1, One2 lacks one relation:

one1 = relationship('One1', back_populates='one2')