Feature Request: Extensible automap classes

[Oliver Wilkerson]

Currently, automapped classes cannot be extended and used without error.

Example:

Base = automap_base()
engine = create_engine(DATABASE_URL)
Base.prepare(engine, reflect=True)

ItemModelBase = Base.classes.item

class DumbMixin:
    def some_helpful_method(self):
        print(self.id)


class Item(DumbMixin, ItemModelBase):
    def lifetime(self):
        return self.updated - self.created


item = Item()
item.name

######################################################################
# Error stacktrace:
Traceback (most recent call last):
File "sqlalchemy/sql/elements.py", line 676, in __getattr__
return getattr(self.comparator, key)
AttributeError: 'Comparator' object has no attribute '_supports_population'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "sqlalchemy/orm/attributes.py", line 185, in __getattr__
return getattr(self.comparator, key)
File "sqlalchemy/util/langhelpers.py", line 840, in __getattr__
return self._fallback_getattr(key)
File "sqlalchemy/orm/properties.py", line 267, in _fallback_getattr
return getattr(self.__clause_element__(), key)
File "sqlalchemy/sql/elements.py", line 682, in __getattr__
key)
AttributeError: Neither 'AnnotatedColumn' object nor 'Comparator' object has an attribute '_supports_population'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
                            File "sqlalchemy/orm/attributes.py", line 234, in __get__
if self._supports_population and self.key in dict_:
    File "sqlalchemy/orm/attributes.py", line 193, in __getattr__
key)
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with item.name has an attribute '_supports_population'

It would be great if we could have our cake and eat it too. A healthy mix of automapped and declarative bases means I can use alembic to manage my migrations, and just have the classes update without having to maintain essentially two schema definitions.

If this is already doable and I'm just missing something, I'd love to be enlightened.

Edit: Our hacky-as-a-quasar fix:

class BestOfBothWorldsMixin:
    __abstract__ = True

    # TODO: redact snark
    def __new__(cls, *args, **kwargs):
        columns = []
        for column_name, definition in cls.__baseclass__._sa_class_manager.mapper.columns.items():
            setattr(cls, column_name, None)
            columns.append(column_name)

        for key, value in kwargs.items():
            if key in columns:
                setattr(cls, key, value)
            else:
                raise AttributeError(key)
        return super(BestOfBothWorldsMixin, cls).__new__(cls)


class Item(BestOfBothWorldsMixin, Base):
    __baseclass__ = Base.classes.items

[Mike Bayer]

per the documentation you create the classes first, then populate the attributes:

class DumbMixin:
    def some_helpful_method(self):
        print(self.id)


class Item(DumbMixin, Base):
    __tablename__ = 'item'

    def lifetime(self):
        return self.updated - self.created

Base.prepare(engine, reflect=True)

the pattern you are asking for is already possible as well, though it's not as good of an idea because to create a subclass of a mapped class means you are creating another mapping; when you query for objects of that type, in some cases you will get the "Base" version back, not the subclass, because there's no polymorphic loading. The issue in your example is that automap does not establish mappings until prepare() is called, which is not the usual declarative behavior. calling prepare() again allows your new class to be mapped as well (though there is a warning that might not be doing the right thing, but can likely be ignored):

Base.prepare(engine, reflect=True)

ItemModelBase = Base.classes.item


class DumbMixin:
    def some_helpful_method(self):
        print(self.id)


class Item(DumbMixin, ItemModelBase):
    def lifetime(self):
        return self.updated - self.created

Base.prepare()

the warning is:

SAWarning: This declarative base already contains a class with the same class name and module name as sqlalchemy.ext.automap.item, and will be replaced in the string-lookup table.

However, as mentioned before, this pattern is not very useful because without mapping it correctly, you won't always get your desired class back. If we have another table which refers to "item", it maps to the original class, not the subclass:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.automap import automap_base

Base = automap_base()
engine = create_engine("sqlite://")
engine.execute("""
    create table item (
        id integer primary key,
        name varchar(30),
        created integer,
        updated integer
    )
""")

engine.execute("""
    create table has_item (
        id integer primary key,
        item_id integer,
        foreign key (item_id) references item(id)
    )
""")

engine.execute("""
    insert into item (id, name, created, updated) values (1, 'asdf', 5, 10)
""")

engine.execute("""
    insert into has_item (id, item_id) values (1, 1)
""")

Base.prepare(engine, reflect=True)

ItemModelBase = Base.classes.item


class DumbMixin:
    def some_helpful_method(self):
        print(self.id)


class Item(DumbMixin, ItemModelBase):
    def lifetime(self):
        return self.updated - self.created

Base.prepare()

s = Session(engine)
obj = s.query(Item).first()

# works fine
print obj.lifetime()

related_obj = s.query(Base.classes.has_item).first()

# fails, 'item' object has no attribute 'lifetime'
related_obj.item.lifetime()

Therefore I'd prefer to keep things simple and support only the currently documented pattern that already handles this use case. automap is not intended to be an enterprise-capable design pattern, it's for simple things and trivial one-offs. For larger scale automation of models generating from schemas I'd look into sqlacodegen.

[Oliver Wilkerson]

We see the problems with it, especially relationship mapping. As previously said, we've always hated maintaining two schemas, so we were looking for cheats.

Thanks for your time and a helpful response.

[Mike Bayer]

OK, sorry automap is a little bit of an "extra". Like it's predecessor SQLSoup, it's fine if someone wanted to fork it off into something more substantial.