Home > Article > Backend Development > Briefly describe a common mistake in sorting in SQLAlchemy
This article mainly introduces a pitfall about SQLAlchemy sorting in python. The article gives detailed sample code. Friends who need it can refer to it. Friends who are interested can come and learn together.
Preface
SQLAlchemy is an ORM framework under the Python programming language, which is built on the database## On top of #API, use relationship object mapping to perform database operations. In short, it is: convert the object into SQL, and then use the data API to execute SQL and obtain the execution results. . Recently, I encountered a pitfall when using SQLAlchemy sorting, so I wanted to summarize it and share it with more friends. Let’s take a look below.
The pitfall 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()
The pitfall 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 read that right, it’s that horizontal bar , slow down the speed. Change to
desc() FunctionThe speed can be increased by 10 times
Attached below is a sqlalchemy high-performance random extraction 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 = query query_tail = query_tail.join(rvid, UserVideo.vid > rvid.c.rvid).limit(20).all()【Related recommendations】1. 2. 3.
The above is the detailed content of Briefly describe a common mistake in sorting in SQLAlchemy. For more information, please follow other related articles on the PHP Chinese website!