前言
SQLAlchemy是Python編程語言下的一款ORM框架，該框架建立在數據庫API之上，使用關系對象映射進行數據庫操作，簡言之便是：將對象轉換成SQL，然后使用數據API執行SQL并獲取執行結果。最近在使用SQLAlchemy排序遇到了一個坑，所以想著總結下來，分享給更多的朋友，下面來一起看看吧。
坑的代碼
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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() |
不坑的代碼
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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() |
對，你沒看錯，就是那個橫杠，拉慢速度。改成 desc() 函數速度能提高10倍
下面附上一個 sqlalchemy 高性能隨機取出若干條數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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() |
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
