fastapi项目 05-数据库一对一,一对多
表之间一对一关系,父表类中通过 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