I am currently developing an API for invoices. And I want to create an Invoice table with invoiceNumber field that would be auto incrementing but as a string. Something like this:
Is it possible to do it somehow using SQLAlchemy?
I tried to use the Sequence function from SQLAlchemy, but I know it is designed only for integers.
Using the SQL in this answer from Gordon Linoff, we can define a column with a default that combines an integer sequence and a string prefix like this:
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'))"""
),
),
)
The column definition depends on the existence of the sequence test_seq. We can optionally add a pair of listeners to drop and create this sequence when the table is dropped or created to keep them in sync.
@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"""))
