@Column updatable and/or insertable

[Arcadiy Ivanov]

In JPA in addition to nullable supported by SQLAlchemy the specification supports attributes updatable and insertable, allowing to have non-updatable and non-insertable columns that never appear in UPDATE or INSERT statements respectively: http://docs.oracle.com/javaee/7/api/javax/persistence/Column.html

Reading documentation I can't figure out whether this capability, at least for updatable columns, already exists or the only thing you can do is to specify a default updatable value somehow.

It's essential for us to make sure that some columns once inserted are never updated, or are never inserted or updated, or never inserted.

[Mike Bayer]

So the thing about "nullable" in SQLA vs. JPA is that "nullable" is part of the Core SQL aspect of things, e.g. it refers only to things that actually exist in a relational DB column alone. So "updatable" and "insertable" wouldn't fit very well at that level since there is no DDL which corresponds to this; that is, the SQLA Column does not mirror a JPA attribute specification.

Right now, the only way to specify ORM configurational-level aspects of column-persisted attributes is to use a function around the Column we usually see in declarative, which right now are the "deferred()" and "column_property()" constructs. These both directly generate the ColumnProperty "attribute" which normally is created implicitly when we use a Column in declarative.

concretely, to add these options directly in this way looks like:

class Foo(Base):
     attr = column_property(Column(Integer, nullable=False), updatable=False)

which is something we can do but is kind of ugly.

In practical terms, the approaches to intercepting unwanted updates/inserts are generally just using a @validates rule:

class Foo(Base):
    @validates('some_attr')
    def _no_mutate(self, key, value):
        raise TypeError("attribute is immutable")

so the main thing a new "updatable" / "insertable" feature would need the following:

1. what does "updatable" / "insertable" provide that using @validates does not?

2. when would a user want to use "updatable" / "insertable" and when would they want to use @validates?

[Arcadiy Ivanov]

1. I think the example here does not represent what I'm looking for. I don't want the TypeError to be thrown, quite the opposite, I want the column to be disregarded for all update purposes and I want an update to succeed.
2. When persistence is orchestrated via Flask-restless or a similar framework the object is persisted directly with very little developer's involvement. Adding validators/decorators etc is extremely counter-productive.

While you're right that nullable is a schema-directed attribute, the insertable and updatable would work as described: when constructing a SQL statement the column is summarily ignored for respective operations (INSERT and UPDATE) when such attributes are False.

[Arcadiy Ivanov]

To extract the desired behavior from JPA spec:

insertable (Optional) Whether the column is included in SQL INSERT statements generated by the persistence provider.

updatable (Optional) Whether the column is included in SQL UPDATE statements generated by the persistence provider.

I suppose I can even add a patch for your consideration directly. Should I submit a PR here or in GitHub?

[Mike Bayer]

I want the column to be disregarded for all update purposes and I want an update to succeed.

columns can be ignored entirely by adding them to exclude_properties, but that has the effect of them not being mapped at all, e.g. they don't come back in reads either.

changes on columns can be reset as well using event handlers.

When persistence is orchestrated via Flask-restless or a similar framework the object is persisted directly with very little developer's involvement.

well that's why those are frameworks, they are providing out-of-the-box patterns suited to particular use cases.

Adding validators/decorators etc is extremely counter-productive.

I'm sure you don't mean "counter-productive", since that would indicate that by taking the effort of adding validators and decorators you cause the the opposite of the desired effect to occur. maybe you mean, "counter-intuitive", though that doesn't really fit here because again it would indicate that one would expect the opposite. I'm guessing you just mean, "it's too much work". But this does not solve the issue of, once an API has three ways to do the same thing, decorators and events that allow you to make anything happen, vs. arbitrary flags that are hardwired to a very specific subset of those use cases, you have to explain to users when they'd use one and when they'd use another, especially for a very infrequent use case like this (if it were more frequent, I've had heard about this one at least once in the past ten years).

the insertable and updatable would work as described:

they cannot work as described on a Column; if I create a Table with a Column and I add "insertable" and "updatable" elements, then I emit table.create(), there is no mechanism, other than having create() go off and generate triggers, to prevent the modification of that column at the SQL level. "nullable" OTOH links directly to "NOT NULL", and also unlike the proposed updatable/insertable, it does raise an error rather than being silently ignored if the incoming data violates the rule.

as far as "insertable" and "updateable" on the column_property(), it's already surprising that the design here would be that these ignore end-user changes silently and just lose the data, that is almost certainly not what people usually expect. In the Python world, silently ignoring data is usually frowned upon, people want systems to complain loudly.

[Mike Bayer]

If the request here is only seeking a way to make this pattern possible, that is of course straightforward like most patterns with events. Here is a demonstration of one pretty direct way to go:

from sqlalchemy import event
from sqlalchemy.orm import mapper


@event.listens_for(mapper, "before_insert", raw=True)
def before_insert(mapper, connection, target):
    for col_attr in mapper.column_attrs:
        col = col_attr.columns[0]
        if not col.info.get('insertable', True):
            target.dict.pop(col_attr.key, None)


@event.listens_for(mapper, "before_update", raw=True)
def before_update(mapper, connection, target):
    for col_attr in mapper.column_attrs:
        col = col_attr.columns[0]
        if not col.info.get('updateable', True) and \
                col_attr.key in target.committed_state:
            target.dict[col_attr.key] = target.committed_state.pop(
                col_attr.key)


if __name__ == '__main__':
    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    class A(Base):
        __tablename__ = 'a'
        id = Column(Integer, primary_key=True)
        plain = Column(Integer)
        uninsertable = Column(
            Integer, info={"insertable": False}, server_default="7")
        unupdateable = Column(Integer, info={"updateable": False})

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

    s = Session(e)

    a1 = A(plain=5, unupdateable=10)
    a2 = A(plain=6, uninsertable=18, unupdateable=10)
    s.add_all([a1, a2])
    s.flush()
    assert a2.uninsertable == 7

    s.commit()

    assert set(a for a, in s.query(A.uninsertable)) == set([7])

    a1 = s.query(A).filter_by(plain=6).one()
    a1.unupdateable = 12
    s.flush()
    assert a1.unupdateable == 10
    s.commit()

    assert set(a for a, in s.query(A.unupdateable)) == set([10])

[Mike Bayer]

the above pattern can be made to work at the Core level also by intercepting the before_execute() event. It can be made to raise an error if those attributes have been modified, or silently ignore them as they do above. This is generally SQLAlchemy's philosophy, which is to favor providing special behaviors through pluggability over implementing flags wired to specific use cases.

[Mike Bayer]

i've added the above recipe to the wiki at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UpdatableInsertable. Thanks for your input!