9.5. 模型定义

9.5. 模型定义
上一页	第 9 章 Sqlalchemy	下一页

导入数据类型

		
from sqlalchemy import Column, Integer, String, Float, DECIMAL, Boolean, Enum, Date, DateTime, Time
from sqlalchemy.dialects.mysql import LONGTEXT

9.5.1. 定义字段

主键

				
id = Column(Integer, primary_key=True, autoincrement=True)

			
price = Column(Float)
# 总共有 20 位,  保留 5 位小说
price = Column(DECIMAL(20, 5))
is_delete = Column(Boolean)
create_time = Column(DateTime)
content = Column(LONGTEXT)

指定字段名

				
name = Column('fullname', String(60))

枚举字段

				
gender = Column(Enum('男', '女'))

默认值

				
name = Column(String(20), default=None, nullable=False, comment="姓名")

9.5.1.1. server_default

default 默认是是 sqlalchemy 产生的，我们需要让数据库完成默认值的填充，就需要使用 server_default

				
class PictureBookHasPicture(Base):
    __tablename__ = "picture_book_has_picture"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="主键")
    picture_book_id = Column(
        Integer,
        ForeignKey("picture_book.id", ondelete="CASCADE", onupdate="CASCADE"),
        nullable=False,
        comment="绘本ID",
    )
    picture_id = Column(
        BIGINT, ForeignKey("picture.id"), nullable=False, comment="图片ID"
    )
    ctime = Column(DateTime, server_default=text("now()"), comment="创建时间")

ON UPDATE

				
class PictureBookHasPicture(Base):
    __tablename__ = "picture_book_has_picture"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="主键")
    picture_book_id = Column(
        Integer,
        ForeignKey("picture_book.id", ondelete="CASCADE", onupdate="CASCADE"),
        nullable=False,
        comment="绘本ID",
    )
    picture_id = Column(
        BIGINT, ForeignKey("picture.id"), nullable=False, comment="图片ID"
    )
    ctime = Column(DateTime, server_default=text("now()"), comment="创建时间")
    mtime = Column(
        DateTime,
        server_default=text("NULL ON UPDATE CURRENT_TIMESTAMP"),
        comment="更新时间",
    )

输出结果

				
CREATE TABLE `picture_book_has_picture` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `picture_book_id` int NOT NULL COMMENT '绘本ID',
  `picture_id` bigint NOT NULL COMMENT '图片ID',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `picture_book_id` (`picture_book_id`),
  KEY `picture_id` (`picture_id`),
  CONSTRAINT `picture_book_has_picture_ibfk_1` FOREIGN KEY (`picture_book_id`) REFERENCES `picture_book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `picture_book_has_picture_ibfk_2` FOREIGN KEY (`picture_id`) REFERENCES `picture` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

9.5.1.2. 给表加注释

给表增加注释 __table_args__ = {"comment": "安卓设备表"}

				
class Android(Base):
    __tablename__ = "android"
    __table_args__ = {"comment": "安卓设备表"}
    android_id = Column(String(16), primary_key=True, comment="安卓设备ID")
    sn = Column(String(18), nullable=False, unique=True, comment="序列号")
    version = Column(String(16), nullable=False, unique=False, comment="软件版本")
    model = Column(String(16), nullable=False, unique=False, comment="产品型号")
    mac = Column(String(48), nullable=True, unique=True, comment="MAC地址")

9.5.1.3. 修改记录的时候触发更新

onupdate 修改记录的时候触发更新

			
update_time = Column(DateTime, onupdate=datetime.now())

9.5.2. 主键 Primary Key

9.5.2.1. 复合主键 Composite Primary Key(也有叫 Compound Primary Key)

				
class MemberHasAndroid(Base):
    __tablename__ = "member_has_android"
    __table_args__ = (
        PrimaryKeyConstraint("member_id", "android_id"),
        {"comment": "会员设备"},
    )
    member_id = Column(Integer, ForeignKey("member.id"), nullable=False, comment="安卓ID")
    android_id = Column(
        String(16), ForeignKey("android.android_id"), nullable=False, comment="安卓ID"
    )

				
CREATE TABLE `member_has_android` (
  `member_id` int NOT NULL COMMENT '安卓ID',
  `android_id` varchar(16) COLLATE utf8mb4_general_ci NOT NULL COMMENT '安卓ID',
  PRIMARY KEY (`member_id`,`android_id`),
  KEY `android_id` (`android_id`),
  CONSTRAINT `member_has_android_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`),
  CONSTRAINT `member_has_android_ibfk_2` FOREIGN KEY (`android_id`) REFERENCES `android` (`android_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='会员设备'

9.5.3. 外键

			
class Picture(Base):
    __tablename__ = "picture"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="唯一ID")
    android_id = Column(String(16), nullable=False, comment="安卓设备ID")
    session = Column(String(36), nullable=False, unique=True, comment="回话ID")
    prompt = Column(String(250), nullable=False, comment="提示词")
    thumbnail = Column(String(250), nullable=True, comment="缩图")
    original = Column(String(250), nullable=True, comment="原始图片")
    image = Column(String(250), nullable=True, comment="图片")
    story = Column(TEXT, nullable=True, comment="故事")
    share = Column(Boolean, nullable=False, default=True, comment="共享")
    like = Column(INTEGER, nullable=False, default=0, comment="点赞")
    ctime = Column(DateTime, default=datetime.now(), comment="创建时间")
    mtime = Column(DateTime, default=datetime.now(), comment="修改时间")


class PictureLike(Base):
    __tablename__ = "picture_like"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="唯一ID")
    android_id = Column(String(16), nullable=False, comment="安卓设备ID")
    picture_id = Column(
        BIGINT, ForeignKey("picture.id"), nullable=False, comment="图片ID"
    )
    ctime = Column(DateTime, default=datetime.now(), comment="创建时间")

9.5.3.1. ON DELETE 删除外键约束

				
RESTRICT：	若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。默认项
NO ACTION：	在MySQL中，同RESTRICT。
CASCADE：	级联删除。
SET NULL：	父表对应数据被删除，子表对应数据项会设置为NULL。

				
class PictureBook(Base):
    __tablename__ = "picture_book"
    id = Column(Integer, autoincrement=True, primary_key=True, comment="主键")
    title = Column(String(50), nullable=False, comment="绘本名称")
    author = Column(String(50), nullable=False, comment="作者")
    cover = Column(String(50), nullable=False, comment="封面")
    description = Column(String(250), nullable=False, comment="描述")
    copyright = Column(String(250), nullable=False, comment="版权")
    isbn = Column(String(13), nullable=True, comment="ISBN")
    publisher = Column(String(13), nullable=True, comment="出版社")
    editon = Column(String(13), nullable=True, comment="编辑")
    ctime = Column(DateTime, default=datetime.now(), comment="创建时间")
    mtime = Column(
        DateTime, default=datetime.now(), onupdate=datetime.now(), comment="修改时间"
    )


class PictureBookHasPicture(Base):
    __tablename__ = "picture_book_has_picture"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="主键")
    picture_book_id = Column(
        Integer, ForeignKey("picture_book.id", ondelete='CASCADE'), nullable=False, comment="绘本ID"
    )
    picture_id = Column(
        BIGINT, ForeignKey("picture.id"), nullable=False, comment="图片ID"
    )
    ctime = Column(DateTime, default=datetime.now(), comment="创建时间")
    mtime = Column(DateTime, default=datetime.now(), comment="修改时间")

输出结果

				
CREATE TABLE `picture_book_has_picture` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `picture_book_id` int NOT NULL COMMENT '绘本ID',
  `picture_id` bigint NOT NULL COMMENT '图片ID',
  `ctime` datetime DEFAULT NULL COMMENT '创建时间',
  `mtime` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `picture_book_id` (`picture_book_id`),
  KEY `picture_id` (`picture_id`),
  CONSTRAINT `picture_book_has_picture_ibfk_1` FOREIGN KEY (`picture_book_id`) REFERENCES `picture_book` (`id`) ON DELETE CASCADE,
  CONSTRAINT `picture_book_has_picture_ibfk_2` FOREIGN KEY (`picture_id`) REFERENCES `picture` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

9.5.3.2. ON UPDATE 更新外键约束

				
class PictureBookHasPicture(Base):
    __tablename__ = "picture_book_has_picture"
    id = Column(BIGINT, autoincrement=True, primary_key=True, comment="主键")
    picture_book_id = Column(
        Integer,
        ForeignKey("picture_book.id", ondelete="CASCADE", onupdate="CASCADE"),
        nullable=False,
        comment="绘本ID",
    )
    picture_id = Column(
        BIGINT, ForeignKey("picture.id"), nullable=False, comment="图片ID"
    )
    ctime = Column(DateTime, server_default=text("now()"), comment="创建时间")
    mtime = Column(
        DateTime, server_default=None, server_onupdate=text("now()"), comment="更新时间"
    )

				
CREATE TABLE `picture_book_has_picture` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `picture_book_id` int NOT NULL COMMENT '绘本ID',
  `picture_id` bigint NOT NULL COMMENT '图片ID',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `picture_book_id` (`picture_book_id`),
  KEY `picture_id` (`picture_id`),
  CONSTRAINT `picture_book_has_picture_ibfk_1` FOREIGN KEY (`picture_book_id`) REFERENCES `picture_book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `picture_book_has_picture_ibfk_2` FOREIGN KEY (`picture_id`) REFERENCES `picture` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

上一页	上一级	下一页
9.4. Session	起始页	9.6. 增删改