Saving complex objects in SQLAlchemy

Question

I'm new to SQLAlchemy and I'm trying to create a new item that includes a list of several sub-items (simple One-to-Many relation) using Flask-RESTful and Flask-SQLAlchemy. I'm trying to create both the item and sub-items simultaneously and I'm not clear on how SQLAlchemy is supposed to work.

class ItemModel(Model):
    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)
    name = Column(String(80))
    sub_items = relationship('SubItemModel')

class SubItemModel(db.Model):
    __tablename__ = 'sub_items'

    id = Column(Integer, primary_key=True)
    item_id = Column(Integer, ForeignKey('items.id'))
    name = Column(String(80))
    item = relationship('ItemModel')

I want to add an item along with several sub_items (through a POST route), but I'm having trouble wrapping my head around which objects to create first and how much SQLAlchemy will do automatically. I got something working by creating an item with no sub_items, creating the sub_items, and then re-saving the item with the sub_items. But this seems pretty clunky, particularly in cases where either the item or some of the sub_items might already exist.

My intuition is just to do something like this:

item = ItemModel(
    name="item1",
    sub_items=[{name: "subitem1"},{name: "subitem2"}])

session.add(self)
session.commit()

But it's not working (I'm getting errors about unhashable types), and it seems ...too simple, somehow. Like I should define the sub_item objects separately. But since they depend on the item_id I'm not sure how to do this.

I'm sure this has been answered before or explained in a simple tutorial somewhere but I haven't been able to find anything simple enough for me to understand. I'm hoping someone can walk me through the basics. (which parts are supposed to be magical and which parts do I still have to code manually...)

Thanks.

Answer 1

The "many" side of any SQLAlchemy relationship behaves like a standard Python list. You should be creating the SubItemModel objects directly and appending them to ItemModel:

item = ItemModel(name='item1')
subitem1 = SubItemModel(name='subitem1')
subitem2 = SubItemModel(name='subitem2')
item.sub_items.append(subitem1)
item.sub_items.append(subitem2)

or, to append multiple items at once, you can use the standard list extend method:

item = ItemModel(name='item1')
subitem1 = SubItemModel(name='subitem1')
subitem2 = SubItemModel(name='subitem2')
item.sub_items.extend([subitem1, subitem2])

You can, if you want, create the subitems directly when you're adding them:

item = ItemModel(name='item1')
item.sub_items.extend([SubItemModel(name='subitem1'), SubItemModel(name='subitem2')])

Whichever option you choose, you should be adding your created item object to the session, which will automatically include the new child records you've created:

session.add(item)
session.commit()

Voila, your item and subitems should all be inserted into the DB at once.