Can an ORM column initiate a flash session in SQLAlchemy?
Question
Can resource access start a session in SQLAlchemy? My expectation would be, for example, for queries related to an object via column_property () or @hybrid_property to invoke an autoflush session, just like queries made with session.Query (). This does not seem to be the case.
In the simple example below, the account contains an Entry collection. It also provides a "balance" property built with the column_property () function, which provides a fetch query. New entries are only shown in the account balance if session.flush () is called explicitly.
This behavior seems suboptimal: users of the Account class need to sprinkle flush () calls throughout their code based on their knowledge of the internals of the balance implementation. If the implementation changes - for example, if there was a "balance" before, Python @property errors can be introduced, although the account interface is essentially identical. Is there any alternative?
Complete example
import sys
import sqlalchemy as sa
import sqlalchemy.sql
import sqlalchemy.orm
import sqlalchemy.ext.declarative
Base = sa.ext.declarative.declarative_base()
class Entry(Base):
__tablename__ = "entries"
id = sa.Column(sa.Integer, primary_key=True)
value = sa.Column(sa.Numeric, primary_key=True)
account_id = sa.Column(sa.Integer, sa.ForeignKey("accounts.id"))
account = sa.orm.relationship("Account", backref="entries")
class Account(Base):
__tablename__ = "accounts"
id = sa.Column(sa.Integer, primary_key=True)
balance = sa.orm.column_property(
sa.sql.select([sa.sql.func.sum(Entry.value)])
.where(Entry.account_id == id)
)
def example(database_url):
# connect to the database and prepare the schema
engine = sa.create_engine(database_url)
session = sa.orm.sessionmaker(bind=engine)()
Base.metadata.create_all(bind = engine)
# add an entry to an account
account = Account()
account.entries.append(Entry(value = 42))
session.add(account)
# and look for that entry in the balance
print "account.balance:", account.balance
assert account.balance == 42
if __name__ == "__main__":
example(sys.argv[1])
Observed output
$ python sa_column_property_example.py postgres:///za_test
account.balance: None
Traceback (most recent call last):
File "sa_column_property_example.py", line 46, in <module>
example(sys.argv[1])
File "sa_column_property_example.py", line 43, in example
assert account.balance == 42
AssertionError
Preferred exit
I would like to see "account.balance: 42" without adding an explicit session.flush () call.
source to share
the column_property parameter is only evaluated at the time of the request, that is, when you say the request (Account), and also when the attribute expires, that is, if you said session.expire ("account", ['balance']).
To make the attribute trigger the request every time, we use @property (some small mods here for a script to work with sqlite):
import sys
import sqlalchemy as sa
import sqlalchemy.sql
import sqlalchemy.orm
import sqlalchemy.ext.declarative
Base = sa.ext.declarative.declarative_base()
class Entry(Base):
__tablename__ = "entries"
id = sa.Column(sa.Integer, primary_key=True)
value = sa.Column(sa.Numeric)
account_id = sa.Column(sa.Integer, sa.ForeignKey("accounts.id"))
account = sa.orm.relationship("Account", backref="entries")
class Account(Base):
__tablename__ = "accounts"
id = sa.Column(sa.Integer, primary_key=True)
@property
def balance(self):
return sqlalchemy.orm.object_session(self).query(
sa.sql.func.sum(Entry.value)
).filter(Entry.account_id == self.id).scalar()
def example(database_url):
# connect to the database and prepare the schema
engine = sa.create_engine(database_url, echo=True)
session = sa.orm.sessionmaker(bind=engine)()
Base.metadata.create_all(bind = engine)
# add an entry to an account
account = Account()
account.entries.append(Entry(value = 42))
session.add(account)
# and look for that entry in the balance
print "account.balance:", account.balance
assert account.balance == 42
if __name__ == "__main__":
example("sqlite://")
Note that "flushing" by itself is not really something we should worry about; the autoflush function makes sure the flush is called every time a request () comes into the database to get the results, so it really makes sure that the request happens what we are looking for.
Another approach to this problem is the use of hybrids. I would recommend reading an overview of all three methods in SQL Expressions as Mapped Attributes , which lists the tradeoffs for each approach.
source to share