3
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.