SQLAlchemy学习-9.一对多和多对一关系

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

# 拼接配置dialect + driver://username:passwor@host:port/database
DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'
Base = declarative_base()

class Parent(Base):  # 一
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    full_name = Column(String(64))
    # 在父表类中通过 relationship() 方法来引用子表的类集合
    children = relationship("Child")

class Child(Base):  # 多
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    full_name = Column(String(64))
    # 在子表类中通过 foreign key (外键)引用父表的参考字段
    parent_id = Column(Integer, ForeignKey('parent.id'))

if __name__ == '__main__':
    engine = create_engine(DB_URI)
    Base.metadata.create_all(engine)  # 将模型映射到数据库中

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/web')
# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)
# 实例化
session = Session()

# 添加数据
parent = Parent(name='yy', full_name='yoyo')
parent.children = [
    Child(name='c1', full_name='c1 full name')
]
session.add(parent)
session.commit()

# 如果parent 已经存在
parent = session.query(Parent).filter(Parent.name == 'yy')
children = [
    Child(name='c4', full_name='c4 full name', parent_id=parent.first().id),
    Child(name='c5', full_name='c5 full name', parent_id=parent.first().id),
]
session.add_all(children)
session.commit()

    parent = session.query(Parent).get(1)
    print(parent.children)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
# 拼接配置dialect + driver://username:passwor@host:port/database
DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'
Base = declarative_base()

class Parent(Base):  # 一
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    full_name = Column(String(64))
    # 父类的 relationship() 方法中使用 relationship.back_populates 参数
    children = relationship("Child", back_populates="parent", lazy="dynamic")

class Child(Base):  # 多
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    full_name = Column(String(64))
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", order_by='Parent.id', back_populates="children")
    # 子表类中附加一个 relationship() 方法

if __name__ == '__main__':
    engine = create_engine(DB_URI)
    Base.metadata.create_all(engine)  # 将模型映射到数据库中

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine


engine = create_engine('mysql+pymysql://root:123456@localhost:3306/web')
# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)
# 实例化
session = Session()

parent = session.query(Parent).get(1)

print(parent.children.all())
children = session.query(Child).get(1)
print(children.parent)