How to make an UUID PK generated by String field?

[jinserk]

Hi,

I don't know well about the database, but I'd like to make a table where the entries PK should be UUID, generated from its String field. The string can have varying length so it's not suitable to use it directly as an id. Or at least I need to use any other hash like md5 or sha1 since I want to make the id unique depending only on the string, not from the system or DNS. I'd like to make it id autogenerated once the string is valid from the pydantic's validator.

Is it possible in ormar? Thank you so much!

[collerek]

You can use signals for it.

If you register pre_save signal you can register a function that will generate uuid from string for you.
You can use uuid3 or uuid5 to generate md5 or sha1 out of string.

Pre_save is triggered before the save happens in db. Check signals docs section.

Something like this.

# model class is your model - one signal can work for several classes - pass a list in that case
@pre_save(ModelClass)
async def before_save(self, sender, instance, **kwargs):
        # string column is a column with your string, pk is a shortcut for primary key column
        instance.pk = uuid.uuid5(instance.string_column)

Don't know what exactly is your use case but if you want to ensure that string is stored in a database only once, you can still autogenerate uuids and simply add unique=True constraint on your string column. That way even if you pass a model with different pk but same string you will get an exception.

[collerek]

Ok but I assume you still change it in init or after initialization?

Cause if in init you can change the order and should work.

class Mol(ormar.Model):
    # fix namespace to generate always the same uuid from smiles
    _UUID_NAMESPACE: ClassVar[uuid.UUID] = uuid.UUID('e96f0bba-1c95-4111-90b4-b6d33688667d')

    class Meta(BaseMeta):
        tablename = "mols"

    id: str = ormar.UUID(primary_key=True, uuid_format='hex')
    smiles: str = ormar.String(nullable=False, max_length=256)

    def __init__(self, **kwargs):
        if kwargs.get("id", None) is None:  # set random id here if empty to avoid init problems
            kwargs['id'] = uuid.uuid4()  # alternatively use default, overwrite only if id is not already set
        super().__init__(**kwargs)  # here the validation happens so if the string changes self.smiles will be changed already
        if self.id is None:  # overwrite if not set (new instance, not coming from db) remove if use default
            self.id = uuid.uuid5(self._UUID_NAMESPACE, self.smiles)

[jinserk]

Thank you again! I guess it's better to cover the case when the field to be changed as:

class Mol(ormar.Model):
    # fixed namespace to generate always unique uuid from the smiles
    _UUID_NAMESPACE: ClassVar[uuid.UUID] = uuid.UUID('12345678-abcd-1234-abcd-123456789abc')

    class Meta(BaseMeta):
        tablename = "mols"

    smiles: str = ormar.String(nullable=False, unique=True, max_length=256)
    id: str = ormar.UUID(primary_key=True, index=True, uuid_format='hex')

    def __init__(self, smiles: str, *args, **kwargs):
        canon_smiles = utils.standardize_smiles(smiles)
        id = self.uuid(canon_smiles)
        super().__init__(id=id, smiles=canon_smiles)

    @root_validator()
    def make_canonical_smiles_and_uuid(cls, values):
        values['smiles'] = utils.standardize_smiles(values['smiles'])
        values['id'] = cls.uuid(values['smiles'])
        return values

    @classmethod
    def uuid(cls, smiles):
        return uuid.uuid5(cls._UUID_NAMESPACE, smiles)

Here I generated the id in the __init__ to avoid init error, and also used a root_validator of pydantic to cover the later change of smiles string. At lease I can cover the smile string update and resulting the id update. Of course there remains a risk to change id only, but in that case the root_validator will roll back the id update to maintain the consistency between the two fields. This is what I found as the best so far. I'm leaving this for the other's reference.

Thank you for all your help and discussion, @collerek!

[collerek]

Yeah that should work too in most cases.

Note that ormar also initialize model for you when you read data from database and it does so through __init__ call too.

So in your case you are recreating the id again also for the models that already have it and my if statements were targeted to avoid it.

Note also that you really should pass kwargs to super init as ormar pass special internal parameters that way (i.e. __pk_only__ flag for relation you didn't select or __exclude__ to handle fields that should be excluded but had default values when you select subset of field). When parent accept variable args and kwargs you should always pass them just in case.

Note that id can be in kwargs so you need to pop it kwargs.pop("id", None) of you implement it.

[jinserk]

Aha.. got it. With ormar we'd better to use kwargs instead of args then. Thank you for the valuable comment!

[jinserk]

I fixed my Mol model as follows:

class Mol(ormar.Model):
    # fixed namespace to generate always unique uuid from the smiles
    _UUID_NAMESPACE: ClassVar[uuid.UUID] = uuid.UUID('12345678-abcd-1234-abcd-123456789abc')

    class Meta(BaseMeta):
        tablename = "mols"

    id: str = ormar.UUID(primary_key=True, index=True, uuid_format='hex')
    smiles: str = ormar.String(nullable=False, unique=True, max_length=256)

    def __init__(self, **kwargs):
        kwargs['id'], kwargs['smiles'] = self.uuid(kwargs['smiles'])
        super().__init__(**kwargs)

    @root_validator()
    def make_canonical_smiles_and_uuid(cls, values):
        values['id'], values['smiles'] = cls.uuid(values['smiles'])
        return values

    @classmethod
    def uuid(cls, smiles):
        canon_smiles = utils.standardize_smiles(smiles)
        id = uuid.uuid5(cls._UUID_NAMESPACE, canon_smiles)
        return id, canon_smiles

But with this __init__ it complains there exists "unknown" args like:

Unknown field 'key' for model Mol

Is it intended in the design of ormar.Model? How to avoid this error?

EDIT: oops. it's my fault. I input additional keys in dict. Sorry for bothering you.

[jinserk]

Hmm.. I found that when I use Mol.objects.create without id, even though I have the routine to generate the missing id in Mol.__init__(), it gives me an error as:

self = <Connection(Thread-2, started 140397017454336)>

    def run(self) -> None:
        """
        Execute function calls on a separate thread.
     
        :meta private:
        """
        while True:
            # Continues running until all queue items are processed,
            # even after connection is closed (so we can finalize all
            # futures)
            try:
                future, function = self._tx.get(timeout=0.1)
            except Empty:
                if self._running:
                    continue
                break
            try:
                LOG.debug("executing %s", function)
>               result = function()
E               sqlite3.IntegrityError: NOT NULL constraint failed: mols.id

../../../.pyenv/versions/3.9.5/envs/kai/lib/python3.9/site-packages/aiosqlite/core.py:102: IntegrityError

It looks like aiosqlite access is earlier than the pydantics validator when I use create. This means, that I have to use

mol = Mol(smiles="...")
mol.save()

to use __init__()?

[collerek]

Yes, the result of init is not used with create, ormar sets default on fields with default from the fields dictionary.

That should probably be marked as a bug (not strictly one but a good practice would be to use init if user overwrite it as you do), can you create an issue for it please?

As a workaround yes you have to use save but it can be a one call like

await Mol(**kwargs).save()

The performance should be more or less the same with both methods.

[jinserk]

Of course, thank you so much @collerek!

[collerek]

Fixed in 0.10.12 (via #251)

[jinserk]

I tested and it works like charm! Thank you so much! :)