mercredi 2 décembre 2020

How to subclass a table by overriding the primary columns?

I would like to use SQLAlchemy to add some test fixtures in the DB - if-and-only-if the data is not already present in the DB. I was trying to create an API like: ProductsModel.ensure(name="Dettol") that will ensure that the data is present in the DB against the name column, where Products is defined like:

Products

class Products(Base):
    __tablename__ = 'products'
    idn = Column(Integer)
    name = Column(String, primary_key=True)

ProductsModel.ensure will take care of doing the unique-row check against the composite keys (name in this case) and decide if it has to be inserted or retrieved.


However, I can't modify the real Products table definition which is defined (elsewhere) with id as the primary key like this:

class Products(Base):
    __tablename__ = 'products'
    idn = Column(Integer, primary_key=True)
    name = Column(String)

So, is it possible for me to inherit the table, and modify the primary keys for my test-fixture use case?

I tried something like this:

class ProductsUnique(Products):
    __mapper_args__ = {
        'polymorphic_identity':'productsunique',
        'polymorphic_on':Products.idn # Hoping that I could override idn.
    }
    idn = Column(Integer)
    name = Column(String, primary_key=True)

But it says:

sqlalchemy.exc.ArgumentError: Column 'idn' on class <class '__main__.ProductsUnique'> conflicts with existing column 'products.idn'

I believe, setting polymorphic_on on the original table might resolve the issue; but the problem is that I can't do it there.

Is there a way to set this after the class has been defined? There seems to be an open issue similar to this in the SQLAlchemy repository.

PS: I am looking for something like Django's get_or_create API, but I would like to very clear about the natural keys for my table as well. I am not sure if there are existing solutions to solve this problem, so I thought I will write these API's myself.

Aucun commentaire:

Enregistrer un commentaire