Tukukkk

I need improvement on solution to the following problem:

Generating random string keys in SQA is relatively simple, something
like:

request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)

However, I need to to get request_id have format like
DIVISION_ABC_REQUEST_223 (this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.

(the backend DB is Postgres)

I found a solution, if a bit wasteful:

class WorkPackage(Base):

    __tablename__ = 'work_package'

    int_id = Column(Integer, primary_key=True)

    wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)

    data = Column(Unicode)



    @hybrid_property

    def wp_id(self):

        return self.wp_prefix + str(self.int_id)



    @wp_id.expression

    def wp_id(cls):

        return cls.wp_prefix.concat(cls.int_id)

wp_id.expression uses concat (a ColumnOperator that produces SQL concatenation operator ||).

It works in the sense of automatic PK creation in desired order and for querying by the wp_id attribute.

However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.

The obvious problem here is that wp_prefix is a column for a single reason: so that I could use its concat method.

I would like to change it in such way that the wp_prefix column is unnecessary (e.g. it could be SQL concatenation of a string and int_id column).

I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.

literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).

literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".