SQLAlchemy not writing changes to database

Question

I've been having a few issues with SQLAlchemy not writing changes, or rather the changes are lost when commit() is called.

I currently have a database of Cards, Decks (a collection of cards) and Users:

class Card(Base):
    id = Column(Integer, primary_key=True)
    # and a bunch of unrelated stuff

class Deck(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    owner_id = Column(Integer, ForeignKey('users.id'))
    cards = Column(PickleType)

class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    decks = relationship('Deck')
    active_deck_id = Column(Integer)
    deck = None

Note: Deck.cards is a dict() with the format {Card.id : No. of cards}

So when a User is loaded, the following happens:

def load_user(client, user):
    # bunch of unrelated stuff
    client.user = user
    if client.user.active_deck_id is not None:
        client.user.deck = db.session.query(db.Deck).get(client.user.active_deck_id)

And the function to add a Card to a Deck is as follows:

def add(args):
    # bunch of unrelated stuff
    card_name = ' '.join(args)
    s_card = db.session.query(db.Card).filter_by(name=card_name).first()
    if s_card is None:
        # errors & return
    if s_card.id in client.user.deck.cards:
        client.user.deck.cards[s_card.id] += 1
    else:
        client.user.deck.cards[s_card.id] = 1
    db.session.commit()

From what I can tell the add() function works correctly, however when the commit() is called the changes are lost.

I get the feeling that User.deck should be a relationship, however the problem with that is there is already User.decks.

Is there something fundamentally wrong with my approach to User.deck that I don't know?

Any help would be greatly appreciated!

Answer 1

The reason why your code doesn't work is because SQLAlchemy doesn't know that your cards dict has changed, so it won't issue the update.

You'll want to set up a MutableDict for it:

cards = Column(MutableDict.as_mutable(PickleType))

As for loading in User.deck manually, why would you do something that the ORM can do for you automatically? So what if you already have a User.decks? Why does that prevent you from have a User.active_deck or User.deck relationship?