How to use SQLAlchemy with class attributes (and properties)?

Question

Say I'm making a game with items in it (think about Minecraft, CS:GO weapons, LoL and Dota items, etc.). There can be huge amounts of the same item in the game with minor detail differences like the condition/durability or the amount of ammo remaining in the item:

player1.give_item(Sword(name='Sword', durability=50))
player2.give_item(Sword(name='Sword', durability=80))
player2.give_item(Pistol(name='Pistol', ammo=12))

But since I don't want to name my swords and pistols every time (due to the name always being the same), and I want it to be extremely easy for one to create new item classes, I figured I'd make name a class attribute:

class Item:
    name = 'unnamed item'

Now I simply subclass this:

class Sword(Item):
    name = 'Sword'

    def __init__(self, durability=100):
        self.durability = durability

class Pistol(Item):
    name = 'Pistol'

    def __init__(self, ammo=10):
        self.ammo = ammo

And we have working classes:

>>> sword = Sword(30)
>>> print(sword.name, sword.durability, sep=', ') 
Sword, 30

But is there a way to use these class attributes (and sometimes even classproperties) with SQLAlchemy in one way or another? Say, I want to store an item's durability (instance attribute) and name (class attribute) with its class_id (class property) as the primary key:

class Item:
    name = 'unnamed item'

    @ClassProperty  # see the classproperty link above
    def class_id(cls):
        return cls.__module__ + '.' + cls.__qualname__

class Sword(Item):
    name = 'Sword'

    def __init__(self, durability=100):
        self.durability = durability

The durability can easily be done with:

class Sword(Item):
    durability = Column(Integer)

But how about the name class attribute and class_id class property?

In reality I have much much larger inheritance tree and each class has multiple attributes/properties as well as more instance attributes.

UPDATE: I was unclear in my post about the tables. I only want to have one table for the items, where the class_id is used as the primary key. This is how I'd construct the table with metadata:

items = Table('items', metadata,
    Column('steamid', String(21), ForeignKey('players.steamid'), primary_key=True),
    Column('class_id', String(50), primary_key=True),
    Column('name', String(50)),
    Column('other_data', String(100)),  # This is __RARELY__ used for something like durability, so I don't need separate table for everything
)

Answer 1

This is my second answer, based on single table inheritance.

The question contains an example where Item subclasses have their own specific instance attributes. For example, Pistol is the only class in the inheritance hierarchy that has an ammo attribute. When representing this in a database, you can save space by creating a table for the parent class that contains a column for each of the common attributes, and storing attributes that are specific to a subclass in a separate table for each of the subclasses. SQLAlchemy supports this out of the box and calls it joined table inheritance (because you need to join tables in order to collect both the common attributes and the attributes that are particular to a subclass). The answer by Ilja Everilä and my previous answer both assumed that joined table inheritance was the way to go.

As it turns out, Markus Meskanen's actual code is a bit different. The subclasses do not have particular instance attributes, they all just have a level attribute in common. Also, Markus commented that he wants all subclasses to be stored in the same table. A possible advantage of using a single table is that you can add and remove subclasses without causing major changes to the database schema every time.

SQLAlchemy offers support for this too, and it is called single table inheritance. It even works if the subclasses do have particular attributes. It is just a bit less efficient, because every row has to store every possible attribute even when it belongs to an item of a different subclass.

Here is a slightly altered version of solution 1 from my previous answer (originally copied from Ilja's answer). This version ("solution 1B") uses single table inheritance, so all items are stored in the same table.

class Item(Base):
    name = 'unnamed item'

    @classproperty
    def class_id(cls):
        return '.'.join((cls.__module__, cls.__qualname__))

    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))
    durability = Column(Integer, default=100)
    ammo = Column(Integer, default=10)

    __mapper_args__ = {
        'polymorphic_identity': 'item',
        'polymorphic_on': type
    }


class Sword(Item):
    name = 'Sword'

    __mapper_args__ = {
        'polymorphic_identity': 'sword',
    }


class Pistol(Item):
    name = 'Pistol'

    __mapper_args__ = {
        'polymorphic_identity': 'pistol',
    }

When we compare this to the original solution 1, a few things stand out. The durability and ammo attributes have moved to the Item base class, so every instance of Item or one of its subclasses now has both a durability and an ammo. The Sword and Pistol subclasses have lost their __tablename__s as well as all of their column attributes. This is telling SQLAlchemy that Sword and Pistol do not have associated tables of their own; in other words, that we want to use single table inheritance. The Item.type column attribute and the __mapper_args__ business are still there; these provide the information for SQLAlchemy to determine whether any given row in the item table belongs to the Item, Sword or Pistol class. This is what I mean when I say that the type column is the disambiguator.

Now, Markus also commented he does not want to customize the subclasses in order to create a database mapping with single table inheritance. Markus wants to start with an existing class hierarchy with no database mapping and then create the entire single table inheritance database mapping at once by just editing the base class. That would mean that adding __mapper_args__ to the Sword and Pistol subclasses, like in solution 1B above, is out of the question. Indeed, if the disambiguator can be computed "automagically", this saves a lot of boilerplate, especially if there are many subclasses.

This can be done, using @declared_attr. Enter solution 4:

class Item(Base):
    name = 'unnamed item'

    @classproperty
    def class_id(cls):
        return '.'.join((cls.__module__, cls.__qualname__))

    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))
    durability = Column(Integer, default=100)
    ammo = Column(Integer, default=10)

    @declared_attr
    def __mapper_args__(cls):
        if cls == Item:
            return {
                'polymorphic_identity': cls.__name__,
                'polymorphic_on': type,
            }
        else:
            return {
                'polymorphic_identity': cls.__name__,
            }


class Sword(Item):
    name = 'Sword'


class Pistol(Item):
    name = 'Pistol'

This yields the same result as solution 1B, except that the value of the disambiguator (still the type column) is computed from the class instead of being an arbitrarily chosen string. Here, it is simply the name of the class (cls.__name__). We could have chosen the fully qualified name instead (cls.class_id) or even the custom name attribute (cls.name), if you can guarantee that every subclass overrides name. It does not really matter what you take as the value of the disambiguator, as long as there is a one-to-one mapping between the value and the class.

Answer 2

Quoting the official documentation:

When our class is constructed, Declarative replaces all the Column objects with special Python accessors known as descriptors; ...

Outside of what the mapping process does to our class, the class remains otherwise mostly a normal Python class, to which we can define any number of ordinary attributes and methods needed by our application.

From that it should be clear that adding class attributes, methods etc. is possible. There are certain reserved names though, namely __tablename__, __table__, metadata and __mapper_args__ (not an exhaustive list).

As for inheritance, SQLAlchemy offers three forms: single table, concrete and joined table inheritance.

Implementing your simplified example using joined table inheritance:

class Item(Base):
    name = 'unnamed item'

    @classproperty
    def class_id(cls):
        return '.'.join((cls.__module__, cls.__qualname__))

    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity': 'item',
        'polymorphic_on': type
    }


class Sword(Item):
    name = 'Sword'

    __tablename__ = 'sword'
    id = Column(Integer, ForeignKey('item.id'), primary_key=True)
    durability = Column(Integer, default=100)

    __mapper_args__ = {
        'polymorphic_identity': 'sword',
    }


class Pistol(Item):
    name = 'Pistol'

    __tablename__ = 'pistol'
    id = Column(Integer, ForeignKey('item.id'), primary_key=True)
    ammo = Column(Integer, default=10)

    __mapper_args__ = {
        'polymorphic_identity': 'pistol',
    }

Adding items and querying:

In [11]: session.add(Pistol())

In [12]: session.add(Pistol())

In [13]: session.add(Sword())

In [14]: session.add(Sword())

In [15]: session.add(Sword(durability=50))

In [16]: session.commit()

In [17]: session.query(Item).all()
Out[17]: 
[<__main__.Pistol at 0x7fce3fd706d8>,
 <__main__.Pistol at 0x7fce3fd70748>,
 <__main__.Sword at 0x7fce3fd709b0>,
 <__main__.Sword at 0x7fce3fd70a20>,
 <__main__.Sword at 0x7fce3fd70a90>]

In [18]: _[-1].durability
Out[18]: 50

In [19]: item =session.query(Item).first()

In [20]: item.name
Out[20]: 'Pistol'

In [21]: item.class_id
Out[21]: '__main__.Pistol'