Python - SQLAlchemy - 新增表字段

背景简介

基于 Python SQLAlchemy + Alembic  新增表字段。

前置信息

• Python 3.11.13 【Conda - 创建 Python 环境】
• 原表已存在 【Python - 基于 SQLAlchemy + Alembic + PostgreSQL 建表】

详细信息

原表结构

• app/models/user_basic_info.py

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column
from app.core.database import Base
import uuid


class UserBasicInfo(Base):
    __tablename__ = "user_basic_info"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    password: Mapped[str] = mapped_column(String(255), nullable=False)
    primary_email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)

目标表结构

• 更新 app/models/user_basic_info.py

from sqlalchemy import String, Boolean, DateTime, func  
from sqlalchemy.orm import Mapped, mapped_column
from app.core.database import Base
import uuid
import datetime  


class UserBasicInfo(Base):
    __tablename__ = "user_basic_info"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    password: Mapped[str] = mapped_column(String(255), nullable=False)
    primary_email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default='1'  # 默认为 True (激活状态)
    )
    user_status: Mapped[str] = mapped_column(
        String(50), nullable=False, server_default='active'  # 默认状态为 'active'
    )
    create_date: Mapped[datetime.datetime] = mapped_column(
        DateTime, nullable=False, server_default=func.now()  # 默认为当前时间
    )

Alembic 表结构修改只需要修改 python 对应的 class 即可。

应用更新

• 在项目根目录下执行指令

alembic revision --autogenerate -m "add is_active, user_status, create_date to user_basic_info"

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'user_basic_info.is_active'
INFO  [alembic.autogenerate.compare] Detected added column 'user_basic_info.user_status'
INFO  [alembic.autogenerate.compare] Detected added column 'user_basic_info.create_date'
  Generating /home/myserver/pers/pers-website/backend/alembic/versions/e41ba5028a7e_add_is_active_user_status_create_date_.py ...  done

• 应用表更新

alembic upgrade head

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade ebbc6e3a391e -> e41ba5028a7e, add is_active, user_status, create_date to user_basic_info

• 查看表结构

                                  Table "public.user_basic_info"
    Column     |            Type             | Collation | Nullable |           Default           
---------------+-----------------------------+-----------+----------+-----------------------------
 id            | character varying(36)       |           | not null | 
 name          | character varying(100)      |           | not null | 
 password      | character varying(255)      |           | not null | 
 primary_email | character varying(255)      |           | not null | 
 is_active     | boolean                     |           | not null | true
 user_status   | character varying(50)       |           | not null | 'active'::character varying
 create_date   | timestamp without time zone |           | not null | now()
Indexes:
    "user_basic_info_pkey" PRIMARY KEY, btree (id)
    "user_basic_info_primary_email_key" UNIQUE CONSTRAINT, btree (primary_email)

以上便是本文的全部内容，感谢您的阅读，如遇到任何问题，欢迎在评论区留言讨论。