CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
ptou
ptou

Reputation: 323

SQLalchemy: onupdate autoincrement (server side)

In SQLalchemy, I use this declared attribute in a mixin:

@declared_attr
def updated_seq(cls): return db.Column(db.BigInteger(), server_default=0)

and I would like to do an onupdate where the sql server (and not python) auto-increments by 1 the value. How can it be done? Tx

I am for instance looking for the following sql equivalent statement during onupdate:

UPDATE updated_seq=updated_seq+1

Upvotes: 1

Views: 1228

Answers (3)

limasxgoesto0
limasxgoesto0

Reputation: 4793

Sounds like a job for triggers. First the mixin somewhere:

class Mixin(object):
    @declared_attr
    def updated_seq(cls):
        return Column(BigInteger, server_default='0')

Then on either a startup file or a script, wherever you prefer (I can't think of anything really elegant). Here is one solution in PSQL

# import Mixin
# import sqlalchemy engine    
for class_ in Mixin.__subclasses__:
    engine.execute('''
CREATE OR REPLACE FUNCTION %{tablename}s_increment() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_seq := OLD.updated_seq + 1 WHERE id = OLD.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER %{tablename}s_increment_trigger BEFORE UPDATE ON %{tablename}s
FOR EACH ROW EXECUTE PROCEDURE %{tablename}s_increment();
``` % {tablename: class_.__tablename__}

You may need to check my SQL on this, as it's been a while and this is untested. But regardless, it will create triggers in your database that detect and autoincrement that row. The downside is you'll need to manually delete any trigger that loses the updated_seq column

Edit: Looks like OP found a way easier solution than mine. Oops.

Upvotes: 0

ptou
ptou

Reputation: 323

Actually the correct is simply:

@declared_attr
def updated_seq(cls): return db.Column(db.BigInteger(), server_default=0, onupdate=text('updated_seq + 1'))

Upvotes: 2

jumbopap
jumbopap

Reputation: 4137

You can handle this situation with an event listener that increments updated_seq.

class Mixin(object):
    @declared_attr
    def updated_seq(cls):
        return Column(BigInteger, server_default='0')

class Person(Base, Mixin):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(Text)

@event.listens_for(Person, 'before_update')
def increment_seq(mapper, connection, target):
    target.updated_seq += 1

>>> p = Person(name="foo")
>>> session.add(p)
>>> session.commit()
>>> p = session.query(Person).first()
>>> p.updated_seq
0
>>> p.name = "bar"
>>> session.commit()
>>> p = session.query(Person).first()
>>> p.updated_seq
1

Upvotes: 0

Related Questions