我目前正在开发发票的API。我想创建一个发票表与invoiceNumber字段,将自动递增,但作为一个字符串。大概是这样的:
44u64gxh1#
使用Gordon Linoff中的SQL,我们可以定义一个列,默认值是一个整数序列和一个字符串前缀,如下所示:
import sqlalchemy as saengine = sa.create_engine('postgresql+psycopg2:///test', echo=True)tbl = sa.Table( 't77185472', sa.MetaData(), sa.Column( 'strseq', sa.String, server_default=sa.text( """('INV' || lpad(nextval('test_seq')::text, 6, '0'))""" ), ),)
import sqlalchemy as sa
engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)
tbl = sa.Table(
't77185472',
sa.MetaData(),
sa.Column(
'strseq',
sa.String,
server_default=sa.text(
"""('INV' || lpad(nextval('test_seq')::text, 6, '0'))"""
),
)
列定义取决于序列test_seq的存在。我们可以选择添加一对侦听器,以便在删除或创建表时删除和创建此序列,从而使它们保持同步。
test_seq
@sa.event.listens_for(tbl, 'after_drop')def receive_after_drop(target, connection, **kw): """Drop the sequence if `tbl` is dropped.""" connection.execute(sa.text("""DROP SEQUENCE IF EXISTS test_seq"""))@sa.event.listens_for(tbl, 'before_create')def receive_before_create(target, connection, **kw): """Create the sequence before `tbl` is created.""" connection.execute(sa.text("""CREATE SEQUENCE test_seq"""))
@sa.event.listens_for(tbl, 'after_drop')
def receive_after_drop(target, connection, **kw):
"""Drop the sequence if `tbl` is dropped."""
connection.execute(sa.text("""DROP SEQUENCE IF EXISTS test_seq"""))
@sa.event.listens_for(tbl, 'before_create')
def receive_before_create(target, connection, **kw):
"""Create the sequence before `tbl` is created."""
connection.execute(sa.text("""CREATE SEQUENCE test_seq"""))
1条答案
按热度按时间44u64gxh1#
使用Gordon Linoff中的SQL,我们可以定义一个列,默认值是一个整数序列和一个字符串前缀,如下所示:
列定义取决于序列
test_seq
的存在。我们可以选择添加一对侦听器,以便在删除或创建表时删除和创建此序列,从而使它们保持同步。