flask-sqlalchemy - change lazy in different situations

user_9192313 0
Viewed 1630

Take the example in document 

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    addresses = db.relationship('Address', lazy='dynamic',
        backref=db.backref('person', lazy='select'))

We can only set the lazy parameter to relationship when we build the models. But I found no document show how to change it after instantiated it.

In most of my situation like 

user = User.query.first()
addresses = user.addresses.filter(is_del=0).all()

which is One-to-Many or 

wallet = user.wallet

which is One-to-One Model, I just set the lazy to dynamic or select to let the model only get data what I need.

However recently I want to export the data in database using Admin Front-end page.

user_list = UserModel.query.all()
for x in user_list:
    item = {
        "ID": x.id if x.id else ''
    }
    if 'basic' in fields or is_all_field == 1:
        ex_item = {
            "Email": x.base.account,
            "Full Name": x.base.full_name,
            "Display Name": x.display_name if x.display_name else '',
            "Phone Number": x.base.phone_number if x.base.phone_number else '',
            "Status": status_map(x.is_sharer,x.is_block,x.status_id)
            "Register Time": x.create_date.strftime('%Y-%m-%d %H:%M:%S') if x.create_date else ''
        }
        item = {**item, **ex_item}
    if ......
    ........

If I continue to use select and dynamic as lazy. It will be very very slow cause every loop the parent query will access database every time when it use subquery.

I test the speed between select and joined using single field like "Full Name": x.base.full_name to export all user data. Select got 53s and joined got 0.02s.

Is there a way to change the lazy parameter based on not changing the original Model?

flask-sqlalchemy flask sqlalchemy
2 Answers
user_26954480

So, basically, if you are using lazy=' select', lazy load, and want to switch to joinedload to optimize your query, use the following:

from sqlalchemy.orm import joinedload  # import the joinedload 
user_list = db.session.query(UserModel).options(joinedload("person"))  # running a joinedload which will give you access to all attributes in parent and child class 
for x in user_list: 
    # now use x.account directly
user_131757080
Related