Generating string primary keys with sequence in SQLAlchemy 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".