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