Automating sqlalchemy declarative model creation

Question

Let’s say we have several sqlalchemy models for catalogues:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy.orm import relationship
Base = declarative_base()

class Plane(Base):
    __tablename__ = 'Plane'
    plane_id = Column(Integer, primary_key=True)

class Car(Base):
    __tablename__ = 'Car'
    car_id = Column(Integer, primary_key=True)

Now for import/export purposes we want to relate these to external ids. So for Plane we would write:

class PlaneID(Base):
    issuer = Column(String(32), primary_key=True)
    external_id = Column(String(16), primary_key=True)

    plane_id = Column(Integer, ForeignKey(Plane.plane_id))
    plane = relationship(Plane, backref='external_ids')

A CarID model would be defined in exactly the same way.

What are possibilities to automate this process?

Maybe we could use a mixin, factory, decorator or meta class. How would we generate the dynamically named Columns then? It would be good to be able to add more Columns to the generated models as needed. For example:

class CarID(ExternalID):
    valid_from = Column(Date)

bodo · Accepted Answer · 2017-01-03 20:56:05Z

1

You can subclass DeclarativeMeta - the metaclass used in declarative_base function:

from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

class ExternalObject(DeclarativeMeta):
    def __new__(mcs, name, bases, attributes):
        if 'issuer' not in attributes:
            attributes['issuer'] = Column(String(32), primary_key=True)
        if 'external_id' not in attributes:
            attributes['external_id'] = Column(String(16), primary_key=True)
        if name[-2:] == 'ID':
            ext_cls_name = name[:-2]
            attr_rel = ext_cls_name.lower()
            attr_id = '%s_id' % attr_rel
            if attr_rel in attributes or attr_id in attributes:
                # Some code here in case 'car' or 'car_id' attribute is defined in new class
                pass
            attributes[attr_id] = Column(Integer, ForeignKey('%s.%s' % (ext_cls_name, attr_id)))
            attributes[attr_rel] = relationship(ext_cls_name, backref='external_ids')
        new_cls = super().__new__(mcs, name, bases, attributes)
        return new_cls

ExternalID = declarative_base(metaclass=ExternalObject)

After that you can create subclass from ExternalID and add another attributes like you did for CarID.

edited Jan 3, 2017 at 20:56

bodo

1,15116 silver badges34 bronze badges

answered Dec 28, 2016 at 12:41

Sergey Shubin

3,2684 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bodo Over a year ago

So then I would have to base all my models on ExternalID, wuldn’t I?

Collectives™ on Stack Overflow

Automating sqlalchemy declarative model creation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related