前言
sqlalchemy是python编程语言下的一款orm框架，该框架建立在数据库api之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成sql，然后使用数据api执行sql并获取执行结果。最近在使用sqlalchemy排序遇到了一个坑，所以想着总结下来，分享给更多的朋友，下面来一起看看吧。
坑的代码
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()
不坑的代码
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 高性能随机取出若干条数据
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()
更多python中sqlalchemy排序的坑相关文章请关注php中文网！