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.

Python free video tutorial

2.

Python object-oriented video

3.

Python Learning Manual

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!