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

Question

Let's say I'm making a game with items in it (think of Minecraft, CS: GO, LoL and Dota, etc.). The game can have huge amounts of the same item with minor differences in details, such as condition / durability or the amount of ammunition left 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 fact that the name is always the same), and I want it to be very easy to create new item classes, I thought that make name an attribute of the class:

 class Item: name = 'unnamed item' 

Now I just 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 classproperties ) with SQLAlchemy anyway? Say I want to preserve the durability of an element (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 

Durability can be easily accomplished:

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

But what about the class attribute name and class_id class class_id ?

In fact, I have a lot more inheritance trees, and each class has several attributes / properties, as well as more instance attributes.

UPDATE: In my post about tables, it was not clear to me. I want to have only one table for elements where class_id used as the primary key. This is how I built 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 in which Item subclasses have their own instance attributes. For example, Pistol is the only class in the inheritance hierarchy with the ammo attribute. By presenting this in a database, you can save space by creating a table for the parent class containing a column for each of the common attributes, and save the attributes related to the subclass in a separate table for each of the subclasses. SQLAlchemy supports this out of the box and calls it related table inheritance (because you need to join the tables to collect both common attributes and attributes that are subclassed). In the answer by Ilja Everilä and my previous answer, it was assumed that joining the inherited table was a transition method.

As it turned out, the actual Markus Meskanen code is slightly different. Subclasses do not have specific instance attributes; they all have only the level attribute. In addition, Marcus noted 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 each time.

SQLAlchemy offers support for this too, and it is called unidirectional table inheritance . It works even if subclasses have certain attributes. This is slightly less efficient, because each row should store all possible attributes, even if it belongs to an element of another subclass.

Here is a slightly modified version of solution 1 from my previous answer (originally copied from Ilja answer ). This version ("Solution 1B") uses unidirectional table inheritance, so all items are stored in one 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, several things stand out. The durability and ammo attributes ammo moved to the base class Item , so each instance of Item or one of its subclasses now has both durability and ammo . The Sword and Pistol subclasses have lost their __tablename__ , as well as all their column attributes. This tells SQLAlchemy that Sword and Pistol do not have their own tables; in other words, we want to use unidirectional inheritance. The column attribute Item.type and business __mapper_args__ still exist; they provide information for SQLAlchemy to determine if any 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 a disambiguator.

Now Markus has also commented that he does not want to set up subclasses to create a database mapping with a single table overlay. Marcus wants to start with the existing class hierarchy without a database mapping, and then immediately create a full inheritance database mapping from the table by simply editing the base class. This would mean that adding __mapper_args__ to the Sword and Pistol subclasses, as in solution 1B above, is out of the question. Indeed, if the mismatch can be calculated “automatically”, this will save a lot of templates, 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 gives the same result as solution 1B, except that the value of the disambiguator (also the type column) is computed from the class, and is not an arbitrarily selected row. Here it is just the class name ( cls.__name__ ). Instead, we could choose the full name ( cls.class_id ) or even the custom name attribute ( cls.name ) if you can guarantee that each subclass overrides name . It doesn’t really matter what you think of as a disambiguator if there is a one-to-one mapping between the value and the class.

Answer 2

Quoting official documentation :

When our class is built, Declarative replaces all Column objects with special Python accessories known as descriptors; ...

Besides what the matching process does with our class, the class remains otherwise basically a regular Python class, with which we can define any number of common attributes and methods that our application needs.

It follows from this that adding class attributes, methods, etc. perhaps. There are certain reserved names, namely __tablename__ , __table__ , metadata and __mapper_args__ (not an exhaustive list).

In terms of inheritance, SQLAlchemy offers three forms : single table , concrete, and related table inheritance .

Implementing a simplified example using joint 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 queries:

 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' 

Answer 3

Answer Ilja Everilä is already the best option. Although it does not literally save the class_id value inside the table, note that any two instances of the same class always have the same class_id value. Knowing the class is enough to compute class_id for any given element. In the sample code that Ilya provided, the type column ensures that the class is always known, and the class property class_id takes care of the rest. That way, class_id is still displayed in the table, if indirectly.

I repeat the example of Elijah from his original answer here, if he decides to change it in his post. Call it "solution 1".

 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', } 

Ilya hinted at a solution in his last comment on the question using @declared_attr , which would literally save class_id inside the table, but I think it would be less elegant. Everything that you buy represents the same information in a slightly different way, due to the fact that your code becomes more complex. See for yourself ("solution 2"):

 class Item(Base): name = 'unnamed item' @classproperty def class_id_(cls): # note the trailing underscore! return '.'.join((cls.__module__, cls.__qualname__)) __tablename__ = 'item' id = Column(Integer, primary_key=True) class_id = Column(String(50)) # note: NO trailing underscore! @declared_attr # the trick def __mapper_args__(cls): return { 'polymorphic_identity': cls.class_id_, 'polymorphic_on': class_id } class Sword(Item): name = 'Sword' __tablename__ = 'sword' id = Column(Integer, ForeignKey('item.id'), primary_key=True) durability = Column(Integer, default=100) @declared_attr def __mapper_args__(cls): return { 'polymorphic_identity': cls.class_id_, } class Pistol(Item): name = 'Pistol' __tablename__ = 'pistol' id = Column(Integer, ForeignKey('item.id'), primary_key=True) ammo = Column(Integer, default=10) @declared_attr def __mapper_args__(cls): return { 'polymorphic_identity': cls.class_id_, } 

There is an additional danger in this approach, which I will discuss below.

In my opinion, it would be more elegant to make the code simpler. This can be achieved starting with solution 1, and then merging the name and type properties, since they are redundant ("solution 3"):

 class Item(Base): @classproperty def class_id(cls): return '.'.join((cls.__module__, cls.__qualname__)) __tablename__ = 'item' id = Column(Integer, primary_key=True) name = Column(String(50)) # formerly known as type __mapper_args__ = { 'polymorphic_identity': 'unnamed item', 'polymorphic_on': name, } class Sword(Item): __tablename__ = 'sword' id = Column(Integer, ForeignKey('item.id'), primary_key=True) durability = Column(Integer, default=100) __mapper_args__ = { 'polymorphic_identity': 'Sword', } class Pistol(Item): __tablename__ = 'pistol' id = Column(Integer, ForeignKey('item.id'), primary_key=True) ammo = Column(Integer, default=10) __mapper_args__ = { 'polymorphic_identity': 'Pistol', } 

All three solutions discussed so far give you the same requested behavior on the Python side (assuming you ignore the type attribute). For example, a Pistol instance returns 'yourmodule.Pistol' as its class_id and 'Pistol' as its name in each solution. Also in each solution, if you add a new element class to the hierarchy, say Key , all its instances automatically report that their class_id will be 'yourmodule.Key' , and you can set their common name once at the class level.

There are some subtle differences on the SQL side regarding the name and value of a column that ambiguously separates element classes. In solution 1, the column is called type , and its value is arbitrarily selected for each class. In solution 2, the column name is class_id , and its value is equal to the class property, which depends on the class name. In solution 3, the name name and its value are equal to the name property of the class, which can be independently changed from the class name. However, since all of these different ways of eliminating the element class ambiguity can be displayed to each other with each other, they contain the same information.

I already mentioned that there is a problem that solution 2 eliminates the element class ambiguity. Suppose you decide to rename the Pistol class to Gun . Gun.class_id_ (with a final underscore) and Gun.__mapper_args__['polymorphic_identity'] will automatically change to 'yourmodule.Gun' . However, the class_id column in your database (displayed in Gun.class_id without end underscore) will contain 'yourmodule.Pistol' . The database migration tool may not be smart enough to realize that these values ​​need to be updated. If you are not careful, your class_id will be corrupted, and SQLAlchemy will most likely throw exceptions because you cannot find the appropriate classes for your elements.

You could avoid this problem by using an arbitrary value as a disamigrator, as in solution 1, and save the class_id in a separate column using the @declared_attr magic (or a similar indirect route), as in solution 2. However, at this point you really need to ask myself, why class_id should be in the database table. Does this really justify the complexity of your code?

Take the home message : you can map simple class attributes as well as computed class properties using SQLAlchemy, even in the face of inheritance, as shown in the solutions. This does not necessarily mean that you should do it. Start with your ultimate goals and find the easiest way to achieve these goals. Just make your decision more complex if it solves the real problem.