通过SQLAlchemy获取随机行

Question

我该如何使用SQLAlchemy从表中随机选择一行或多行数据呢？

Answer 1

如果你在使用ORM（对象关系映射）并且表格不大（或者你已经缓存了行数），而且你希望它能独立于数据库，那么一个非常简单的方法就是。

import random
rand = random.randrange(0, session.query(Table).count()) 
row = session.query(Table)[rand]

这样做有点偷懒，但这就是使用ORM的原因。

Answer 2

这里有四种不同的方式，从最慢到最快排列。下面是用 timeit 测试的结果：

from sqlalchemy.sql import func
from sqlalchemy.orm import load_only

def simple_random():
    return random.choice(model_name.query.all())

def load_only_random():
    return random.choice(model_name.query.options(load_only('id')).all())

def order_by_random():
    return model_name.query.order_by(func.random()).first()

def optimized_random():
    return model_name.query.options(load_only('id')).offset(
            func.floor(
                func.random() *
                db.session.query(func.count(model_name.id))
            )
        ).limit(1).all()

这是我在Macbook上对一个有300行的PostgreSQL表进行10,000次运行的 timeit 结果：

simple_random(): 
    90.09954111799925
load_only_random():
    65.94714171699889
order_by_random():
    23.17819356000109
optimized_random():
    19.87806927999918

你可以很明显地看到，使用 func.random() 比用Python的 random.choice() 返回所有结果要快得多。

另外，随着表的大小增加，order_by_random() 的性能会显著下降，因为 ORDER BY 需要对整个表进行扫描，而 optimized_random() 中的 COUNT 可以利用索引。

Answer 3

这个问题主要跟数据库有关。

我知道像PostgreSQL、SQLite、MySQL和Oracle这些数据库都可以通过随机函数来排序，所以你可以在SQLAlchemy中使用这个功能：

from  sqlalchemy.sql.expression import func, select

select.order_by(func.random()) # for PostgreSQL, SQLite

select.order_by(func.rand()) # for MySQL

select.order_by('dbms_random.value') # For Oracle

接下来，你需要限制查询的记录数量（比如使用.limit()）。

要注意的是，至少在PostgreSQL中，选择随机记录会有很大的性能问题；这里有一篇不错的文章讲解这个问题。

通过SQLAlchemy获取随机行

9 个回答

撰写回答