介绍
学习关系型数据库的三种常见关系,一对一,一对多,多对多
一对一
class User(Base):
"""用户表"""
__tablename__ = "user"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
email: Mapped[str] = mapped_column(String(100))
username: Mapped[str] = mapped_column(String(100), unique=True,index= True)
password: Mapped[str] = mapped_column(String(200) )
class UserExtension(Base):
"""用户扩展表"""
__tablename__ = "user_extension"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
user_id: Mapped[int] = mapped_column(Integer, ForeignKey("user.id"))
phone: Mapped[str] = mapped_column(String(20))
User: Mapped[User] = relationship(back_populates="user_extension")# 反向关系
一对多
from sqlalchemy import Integer, String, Text, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from . import Base
from .user import User
class Article(Base):
__tablename__ = "article"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)#主键自增
title: Mapped[str] = mapped_column(String(100))
content: Mapped[str] = mapped_column(Text)
author_id: Mapped[int] = mapped_column(Integer, ForeignKey("user.id")) #外键
author: Mapped[User] = relationship(back_populates="articles")
# user.py
articles: Mapped[List["Article"]] = relationship(back_populates="author")
多对多
需要一个中间表
# article类
tags: Mapped[List["Tag"]] = relationship("Tag",back_populates="articles",secondary="article_tag")
class Tag(Base):
__tablename__ = "tag"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
name: Mapped[str] = mapped_column(String(100))
articles: Mapped[List[Article]] = relationship("Article",back_populates="tags",secondary="article_tag")
class ArticleTag(Base):
__tablename__ = "article_tag"
article_id: Mapped[int] = mapped_column(Integer, ForeignKey("article.id"),primary_key= True)
tag_id: Mapped[int] = mapped_column(Integer, ForeignKey("tag.id"),primary_key= True)
