SQLAlchemy学习-4.一对一关系

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

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

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

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

Base = declarative_base()

class Card(Base):
    """银行卡基本信息"""
    __tablename__ = 'card'  # 数据库表名

    id = Column(Integer, primary_key=True, autoincrement=True)
    card_id = Column(String(30))
    card_user = Column(String(10))
    tel = Column(String(30))
    card_detail = relationship("CardDetail",
                               uselist=False,
                               backref='card')

class CardDetail(Base):
    """银行卡 详情信息"""
    __tablename__ = 'carddetail'  # 数据库表名

    id = Column(Integer, primary_key=True, autoincrement=True)
    mail = Column(String(30))
    city = Column(String(10))
    address = Column(String(30))
    card_id = Column(Integer, ForeignKey('card.id'))

if __name__ == '__main__':
    DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'
    engine = create_engine(DB_URI)
    Base.metadata.create_all(engine)  # 将模型映射到数据库中

from xuexi.a4 import Card, CardDetail
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()

card = Card(card_id='3211222001',
            card_user='yoyo',
            tel='10086')
session.add(card)
session.flush()  # flush方法会生成Primary Key 得到card  的 id
detail = CardDetail(mail='123@qq.com',
                    city='上海市',
                    address='徐汇区',
                    card_id=card.id)
session.add(detail)
session.commit()  # 提交

from xuexi.a4 import Card, CardDetail
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()

# 查询
# query()
r1 = session.query(Card).filter(Card.card_user == 'yoyo').first()
print(r1)

# 正向查询，主表查副表
print(r1.card_detail)
print(r1.card_detail.mail)

<Card(id=1, card_id=3211222001, card_user=yoyo ...)>
<CardDetail(id=1, mail=123@qq.com, city=上海市 ...)>
123@qq.com

# 先查关联表数据
r2 = session.query(CardDetail).filter(CardDetail.mail == '123@qq.com').first()
print(r2)
# 反向查询主表
print(r2.card)     # relationship通过backref='detail'属性关联到主表
print(r2.card.card_user)

<CardDetail(id=1, mail=123@qq.com, city=上海市 ...)>
<Card(id=1, card_id=3211222001, card_user=yoyo ...)>
yoyo