526互联

fastapi项目 05-数据库一对一，一对多

发布时间 2024-01-03 23:06:49作者: dack_deng

表之间一对一关系，父表类中通过 relationship() 方法来引用子表的类集合，在子表类中通过 foreign key (外键)引用父表类。如下示例。

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 (外键)引用父表的参考字段

1. 一对一

"""一对一"""
# study_sqlalchemy01.py

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:12345678@127.0.0.1:3306/fastapi_v1'
    engine = create_engine(DB_URI)
    Base.metadata.create_all(engine)  # 将模型映射到数据库中

执行完上面的后，就将模型映射到数据库中了。

创建study_sqlalchemy02.py用来新增数据库中的数据。

# study_sqlalchemy02.py

from study_sqlalchemy01 import Card, CardDetail
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:12345678@127.0.0.1:3306/fastapi_v1')
Session = sessionmaker(bind=engine)  # 把当前的引擎绑定给这个会话
session = Session()  # 实例化
# 1. 新增数据
card = Card(card_id='1001', card_user='张三', 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()

可以看到，数据库中新增了2个数据，如图所示。

1.1 查询数据

查询数据，一般分为正向查询，通过主表对象，查询到副表。或者是通过副表查询主表的数据。

from study_sqlalchemy01 import Card, CardDetail
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:12345678@127.0.0.1:3306/fastapi_v1')
Session = sessionmaker(bind=engine)  # 把当前的引擎绑定给这个会话
session = Session()  # 实例化
""" 1. 新增数据
card = Card(card_id='1001', card_user='张三', 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()
"""

"""2. 查询数据"""
r1 = session.query(Card).filter_by(card_user = "张三").first()
print(r1)
# 正向查询
print(r1.card_detail)
print(r1.card_detail.mail)

# 反向查询,通过副表查询主表
# 先查关联表数据
r2 = session.query(CardDetail).filter_by(mail = '123@qq.com').first()
print(r2)
# 反向查询主表
print(r2.card)
print(r2.card.card_user)

打印的查询结果如下：

<study_sqlalchemy01.Card object at 0x106c09810>
<study_sqlalchemy01.CardDetail object at 0x106c0af20>
123@qq.com
<study_sqlalchemy01.CardDetail object at 0x106c0af20>
<study_sqlalchemy01.Card object at 0x106c09810>
张三

2. 一对多

同理需先创建数据表结构，再将模型映射到数据库中。与一对一关系主要区别是relationship不用设置 uselist=False参数。

# study_sqlalchemy03.py

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

DB_URI = 'mysql+pymysql://root:12345678@127.0.0.1:3306/fastapi_v1'
Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True, autoincrement=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, autoincrement=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)  # 将模型映射到数据库中


# study_sqlalchemy04.py

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from study_sqlalchemy03 import Parent, Child

DB_URI = 'mysql+pymysql://root:12345678@127.0.0.1:3306/fastapi_v1'
engine = create_engine(DB_URI)
Session = sessionmaker(bind=engine)
session = Session()

"""1. 添加数据"""
# parent = Parent(name='dack1', full_name='dack_deng')
# parent.children = [
#     Child(name='child_dack1', full_name='child_dack_deng')
# ]
# session.add(parent)
# session.commit()

"""2. 查询数据, 通过父类，查询子类（单向查询"""
parent = session.query(Parent).filter_by(name='dack1').first()
print(parent)
print(parent.name)
print(parent.children)
print(parent.children[0].name)

打印信息
<study_sqlalchemy03.Parent object at 0x101efb9a0>
dack1
[<study_sqlalchemy03.Child object at 0x101faf040>]
child_dack1