如何在SQLAlchemy中创建字符串格式的自增发票编号序列

当然可以实现，以下是两种可靠的方案，分别从数据库层面和应用层面解决问题：

方案一：数据库序列+触发器（推荐，高并发场景更可靠） #

利用数据库的整数序列生成自增数字，再通过触发器将其格式化为INV+5位补零数字的字符串。这种方式由数据库保证原子性，避免并发下的编号重复问题。

以PostgreSQL为例的数据库脚本： #

-- 创建自增整数序列
CREATE SEQUENCE invoice_number_seq START WITH 1 INCREMENT BY 1;

-- 定义生成发票编号的触发器函数
CREATE OR REPLACE FUNCTION generate_invoice_number()
RETURNS TRIGGER AS $$
BEGIN
    -- 将序列值格式化为INV开头的5位补零字符串
    NEW.invoice_number := 'INV' || LPAD(NEXTVAL('invoice_number_seq')::TEXT, 5, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为Invoice表绑定插入前触发器
CREATE TRIGGER trigger_generate_invoice_number
BEFORE INSERT ON invoice
FOR EACH ROW EXECUTE FUNCTION generate_invoice_number();

SQLAlchemy模型定义： #

from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Invoice(Base):
    __tablename__ = 'invoice'
    
    id = Column(Integer, primary_key=True)
    invoice_number = Column(String(8), unique=True, nullable=False)
    # 添加你的其他业务字段，比如amount, issue_date等

插入数据时无需手动设置invoice_number，触发器会自动生成格式正确的编号。

方案二：SQLAlchemy事件监听（应用层面控制） #

如果不想依赖数据库触发器，可以通过SQLAlchemy的before_insert事件钩子，在数据插入前生成格式化编号。需要注意并发场景下的原子性问题，建议通过数据库原生序列或自增字段保证编号唯一。

代码示例： #

from sqlalchemy import Column, String, Integer, Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.event import listens_for

Base = declarative_base()

# 定义整数序列
invoice_seq = Sequence('invoice_number_seq', start=1)

class Invoice(Base):
    __tablename__ = 'invoice'
    
    id = Column(Integer, primary_key=True)
    invoice_number = Column(String(8), unique=True, nullable=False)
    # 其他业务字段...

@listens_for(Invoice, 'before_insert')
def set_invoice_number(mapper, connection, target):
    # 原子性获取下一个序列值
    next_num = connection.execute(invoice_seq).scalar()
    # 格式化为INV+5位补零数字
    target.invoice_number = f'INV{next_num:05d}'

注意事项： #

• 若使用MySQL（无原生Sequence），可以用一个带auto_increment的辅助整数字段替代序列，在before_insert中获取该字段的下一个值（需结合数据库的原子操作避免并发冲突）。
• 无论哪种方案，都要给invoice_number字段添加unique约束，防止重复编号。
• 若已有历史数据，需先将序列的起始值调整为现有最大编号的数字部分+1（比如现有最大编号是INV00123，序列起始值设为124）。

内容的提问来源于stack exchange，提问作者Vitalij Gurin