sqlalchemy sequence

1 序列对象

1.1 创建连接

import sqlalchemy
uri = "postgresql://test:123456@127.0.0.1:5432/test"
engine = sqlalchemy.create_engine(
uri, pool_size = 80, max_overflow = 20, pool_recycle = 3600
)

1.2 创建序列

from sqlalchemy import Sequence
seq = Sequence('SEQ_01')
# 创建名为SEQ_01的序列
seq.create(bind=engine)
# 删除序列
seq.drop(bind=engine)

1.3 获取现有序列

获取序列列表

from sqlalchemy import inspect
insp = inspect(engine)
# list
insp.get_sequence_names()

获取序列对象，并操作

from sqlalchemy import Sequece, MetaData
metadata = MetaData(bind=engine)
seq = Sequence('seq_name', metadata=metadata)
# 取值
seq.execute()

2. 作为主键使用

用于构建主键，在建表时同时构建，插入记录时不需要赋值。

2.1 一般形式

from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy import Column, Sequence
from sqlalchemy.dialects.postgresql import Integer, VARCHAR
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    __table_args__ = ({'comment': '人员'})

    id= Column(Integer, Sequence('SEQ_01'), primary_key=True, comment='人员编号')
    name = Column(VARCHAR(50), nullable=False, comment='姓名')
    number= Column(VARCHAR(50), nullable=True, comment='号码')

# 创建表的同时创建序列
User.__table__.create(bind = engine)

2.2 在flask中使用

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = ({'comment': '人员'})
    
    id= Column(Integer, Sequence('SEQ_01'), primary_key=True, comment='人员编号')
    name = Column(VARCHAR(50), nullable=False, comment='姓名')
    number= Column(VARCHAR(50), nullable=True, comment='号码')

    def __init__(self, **kwargs):
        super().__init__(**kwargs)

2.3 构造字符串序列

序列本身是整数型，直接通过序列构造的id只能是整数形式。有时候需要在id中包含一些信息，或者与其它表的id作区分，因而直接用序列是不行的。以下基于原始序列构建形如‘C00001’字符串型序列。

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = ({'comment': '人员'})
    
    seq = Sequence('SEQ_01', metadata=db.Model.metadata)
    
    id= Column(VARCHAR(6), seq, primary_key=True, comment='人员编号')
    name = Column(VARCHAR(50), nullable=False, comment='姓名')
    number= Column(VARCHAR(50), nullable=True, comment='号码')

    def __init__(self, **kwargs):
        super().__init__(**kwargs)
        if not self.id:
            self.id = self.next_id()
            
    def next_id(self):
        with db.engine.connect() as conn:
            nextid = conn.execute(self.seq)
            res = 'C' + str(nextid).zfill(5)
        return res
new_record = User(name = 'muscle')
db.session.add(new_record )
db.session.commit()