I have a MySQL query like this:
UPDATE mytable SET is_active=false
WHERE created < DATE_SUB(NOW(), INTERVAL `interval` second)
How can I express it using flask-sqlalchemy's ORM (i.e. via the MyTable model)?
Add a comment
|
1 Answer
This may not be the most elegant solution, but it seems to be working for me:
Base = declarative_base()
class Account(Base):
__tablename__ = "so62234199"
id = sa.Column(sa.Integer, primary_key=True)
created = sa.Column(sa.DateTime)
interval = sa.Column(sa.Integer)
is_active = sa.Column(sa.Boolean)
def __repr__(self):
return f"<Account(id={self.id}, created='{self.created}')>"
Session = sessionmaker(bind=engine)
session = Session()
account_table = list(Account.metadata.tables.values())[0]
upd = (
account_table.update()
.values(is_active=False)
.where(
Account.created
< sa.func.date_sub(
sa.func.now(),
sa.text(
" ".join(
["INTERVAL", str(Account.interval.compile()), "SECOND"]
)
),
)
)
)
with engine.connect() as conn:
conn.execute(upd)
The SQL statement generated is
INFO sqlalchemy.engine.Engine UPDATE so62234199 SET is_active=%s WHERE so62234199.created < date_sub(now(), INTERVAL so62234199.interval SECOND)
INFO sqlalchemy.engine.Engine (0,)