How to create a string sequence in SQLAlchemy

Question

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:

1. INV00001
2. INV00002
3. INV00003

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.

Answer 1

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"""))