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_idhave 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)
ColumnOperator that produces SQL concatenation operator
It works in the sense of automatic PK creation in desired order and for querying by the
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
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
I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.