Mark a single row in a table in SQLAlchemy

Imagine a table with two columns, id (Integer) and mark (Boolean). This table can开发者_运维问答 have an arbitrary number of rows, but exactly one of this rows should have the column mark set to True.

If I modify the database to mark another entry's mark to True, then the system should first un-mark the previous entry and then mark the requested entry.

How would you approach this in Python/SQLAlchemy?

The two comments above both have truth to them. A trigger is a good way to do this, and also the "many false, one true" pattern suggests that perhaps a different table can be used to reference the "true" row, or even that the entire "true" row might be elsewhere. The usual model here is that your table is storing versioned information and "True" represents the current "version". I normally either have the "current" version referenced from a parent record, or I use a separate table called "history" for all the "non-current" rows.

Anyway let's see the quickest way to do exactly what you ask in SQLAlchemy. We'll do pretty much what an INSERT/UPDATE trigger would do, via ORM events:

from sqlalchemy import Column, Integer, Boolean, create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
Base = declarative_base()

class Widget(Base):
    __tablename__ = 'widget'
    id = Column(Integer, primary_key=True)
    is_current_widget = Column(Boolean, default=False,
                        nullable=False)

@event.listens_for(Widget, "after_insert")
@event.listens_for(Widget, "after_update")
def _check_current(mapper, connection, target):
    if target.is_current_widget:
        connection.execute(
            Widget.__table__.
                update().
                values(is_current_widget=False).
                where(Widget.id!=target.id)
        )

e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)

s = Session(e)

w1, w2, w3, w4, w5 = [Widget() for i in xrange(5)]
s.add_all([w1, w2, w3, w4, w5])
s.commit()

# note each call to commit() expires
# the values on all the Widgets so that
# is_current_widget is refreshed.

w2.is_current_widget = True
s.commit()

assert w2.is_current_widget
assert not w5.is_current_widget

w4.is_current_widget = True
s.commit()

assert not w2.is_current_widget
assert not w5.is_current_widget
assert w4.is_current_widget

# test the after_insert event

w6 = Widget(is_current_widget=True)
s.add(w6)
s.commit()

assert w6.is_current_widget
assert not w5.is_current_widget
assert not w4.is_current_widget