Briefly describe a common mistake in sorting in SQLAlchemy-Python Tutorial-php.cn

Briefly describe a common mistake in sorting in SQLAlchemy

Y2J

Release： 2017-05-08 16:23:59

Original

1311 people have browsed it

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()

Copy after login

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()

Copy after login

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(&#39;rvid&#39;)).subquery()
query = query.filter(UserVideo.category == category)
query_tail = query
query_tail = query_tail.join(rvid, UserVideo.vid > rvid.c.rvid).limit(20).all()

Copy after login

【Related recommendations】

Python free video tutorial

Python object-oriented video

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!