SQLAlchemy sorting in python

This article mainly introduces a pitfall of SQLAlchemy sorting in python. It provides detailed sample code. if you need it, you can refer to it. if you are interested, let's study it together. Preface

SQLAlchemy is an ORM framework in the Python programming language. it is built on database APIs and uses relational object ING for database operations. In short, it converts objects to SQL, then, use the data API to execute the SQL statement and obtain the execution result. Recently, SQLAlchemy sorting has encountered a pitfall, so I want to summarize it and share it with more friends. let's take a look at it.

Code

query = db_session.query(UserVideo.vid,         UserVideo.uid,         UserVideo.v_width,         UserVideo.v_height,         UserVideo.create_time,         UserVideo.cover,         UserVideo.source_url,         UserVideo.v_type,         UserVideo.category,         User.username,         User.sex,         UserExtraInfo.avatar,         UserExtraInfo.watermark)  query = query.filter(UserVideo.status == 1,        User.uid == UserVideo.uid,        UserExtraInfo.uid == UserVideo.uid)   query = query.filter(UserVideo.status == 1)  query = query.order_by(-UserVideo.vid)  query = query.limit(20).all()

Code

query = db_session.query(UserVideo.vid,         UserVideo.uid,         UserVideo.v_width,         UserVideo.v_height,         UserVideo.create_time,         UserVideo.cover,         UserVideo.source_url,         UserVideo.v_type,         UserVideo.category,         User.username,         User.sex,         UserExtraInfo.avatar,         UserExtraInfo.watermark)  query = query.filter(UserVideo.status == 1,        User.uid == UserVideo.uid,        UserExtraInfo.uid == UserVideo.uid) # .order_by(UserVideo.vid.desc()).limit(20).all()  query = query.filter(UserVideo.status == 1)  query = query.order_by(UserVideo.vid.desc())  query = query.limit(20).all()

Yes, you are not mistaken. it is the horizontal bar that slows down the speed. Changedesc() Function speed can be increased by 10 times

The following is a sqlalchemy high-performance random fetch of several pieces of data.

query = db_session.query(UserVideo.vid,          UserVideo.uid,          UserVideo.v_width,          UserVideo.v_height,          UserVideo.create_time,          UserVideo.cover,          UserVideo.source_url,          UserVideo.v_type,          UserVideo.category,          User.username,          User.sex,          UserExtraInfo.avatar,          UserExtraInfo.watermark)   query = query.filter(UserVideo.status == 1, User.uid == UserVideo.uid, UserExtraInfo.uid == UserVideo.uid)rvid = db_session.query(func.round(random.random() * func.max(UserVideo.vid)).label('rvid')).subquery()query = query.filter(UserVideo.category == category)query_tail = queryquery_tail = query_tail.join(rvid, UserVideo.vid > rvid.c.rvid).limit(20).all()

For more articles about SQLAlchemy sorting in python, refer to PHP!