graN graN - 1 year ago 166
MySQL Question

Optimizing hybrid_properties in SQLAlchemy

I have a piece of working code but it is very inefficient, instead of a single query with a join. I get one initial query, followed by one query per row in the response.

I have to following scenario:

class Job(Base, SerializeMixin, JobInterface):
__tablename__ = 'job_subjobs'

id = Column(Integer, primary_key=True, autoincrement=True)
group_id = Column(Integer, ForeignKey(""), nullable=False)

class Crash(Base, SerializeMixin):
__tablename__ = 'crashes'

id = Column(Integer, primary_key=True, autoincrement=True)
job_id = Column(Integer, ForeignKey("", ondelete='CASCADE'), nullable=False)

job = relationship('Job', backref='Crash')

def job_identifier(self):
return "{}:{}".format(self.job.group_id,

So given the above and I perform a query for all Crashes, It will perform one SELECT for all crashes. When I iterate and ask for job_identifier it will then do one separate SELECT for each crash.


Is there someway i can create a @hybrid_property referencing a different table and have it JOIN from the beginning and preload the expression?

I've experimented with @xxx.expression without success. If all else fails I can add another foreign key in Crash table, but I would like to avoid changing current data structure if possible.

Answer Source

ended up using:

jobs = relationship('Job', backref='Crash', lazy='joined')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download