通过SQLAlchemy获取随机行

Question

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

- cnu

Answer 1

这非常是一个与数据库相关的问题。

我知道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 中，选择随机记录会有严重的性能问题；这里是一篇关于此的好文章。

- Łukasz

---

与Postgres相同，SQLite也支持以下语法：select.order_by(func.random()).limit(n)。意思是按随机顺序选择n条数据。 - mechanical_meat

如果您正在使用声明性模型：session.query(MyModel).order_by(func.rand()).first - trinth

谢谢@trinth，当我在结尾加上括号时它起作用了：session.query(MyModel).order_by(func.rand()).first()。 - Kent Munthe Caspersen

为什么要使用.rand和.random？SQLAlchemy不应该为我们处理这些特定于数据库的怪癖吗？ - rr-

自SQLAlchemy v0.4版本以来，func.random()是一个通用函数，可以编译为数据库的随机实现。 - RazerM

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

timeit在我的Macbook上针对具有300行的PostgreSQL表进行了10,000次运行的结果:

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可以使用索引。

- Jeff Widman

---

选取样本怎么样？就像 random.sample() 做的那样？这里有什么优化的方法吗？ - hamidfzm

打开一个新问题并链接到它，我会尝试回答。如果可能，请指定SQL的基础版本，因为这也会影响答案。 - Jeff Widman

这不是在使用 flask-sqlalchemy 吗？ - MattSom

Answer 3

如果您正在使用ORM并且表格规模不大（或者您已经缓存了行数），并且希望它独立于数据库，那么真正简单的方法是：

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

这有点作弊，但这就是你使用 ORM 的原因。

- David Raznick

---

rand = random.randrange(0, session.query(Table).count()) - James Brady

在选择其中一个之前，您可以选择并创建所有对象。 - Serge K.

random.choice(session.query(Table)) 怎么样？ - Solomon Ucko

Answer 4

有一种简单的方法可以获取与数据库无关的随机行。只需使用 .offset() ，无需检索所有行：

使用 .offset() 可以轻松地从数据库中获取一个随机行，而且这种方法与具体的数据库类型无关。

import random
query = DBSession.query(Table)
rowCount = int(query.count())
randomRow = query.offset(int(rowCount*random.random())).first()

如果您想要几行数据，那么您可以多次运行此操作，并确保每行不与上一行相同。

- GuySoft

---

在我的情况下，在大约500k行的设置中工作得很好。 - Mario

现在 Oracle 数据库已经达到了 1100 万行... 情况不太好了 :-) 线性下降，但是... 我必须找到其他解决方案。 - Mario

我还没有进行任何工作台测试，但我将查询更改为：query.offset(random.randint(0, rowCount - 1)).limit(1).first()，现在它可以正常工作了。不过我的数据库与你的相比还是很小的。 - Jayme Tosi Neto

@Jayme：你可以使用 query.offset(random.randrange(rowCount)).limit(1).first()。 - jfs

@Jayme，另外，为什么在使用.first()之前要使用.limit(1)呢？这似乎是多余的。也许，query.offset(random.randrange(row_count)).first()就足够了。 - jfs

Answer 5

一些SQL DBMS，例如Microsoft SQL Server、DB2和PostgreSQL，已经实现了SQL:2003的TABLESAMPLE子句。支持被添加到了SQLAlchemy 1.1版本中。它允许使用不同的抽样方法返回表的样本 - 标准要求SYSTEM和BERNOULLI，它们返回所需的表的近似百分比。

在SQLAlchemy中，FromClause.tablesample() 和 tablesample() 用于生成一个TableSample构造：

# Approx. 1%, using SYSTEM method
sample1 = mytable.tablesample(1)

# Approx. 1%, using BERNOULLI method
sample2 = mytable.tablesample(func.bernoulli(1))

当与映射类一起使用时，有一个小问题：必须给生成的TableSample对象起别名才能用于查询模型对象：

sample = aliased(MyModel, tablesample(MyModel, 1))
res = session.query(sample).all()

---

由于许多答案都包含性能基准，我将在此处包括一些简单的测试。使用一个包含约一百万行和一个整数列的简单PostgreSQL表格，选择（大约）1％的样本：

In [24]: %%timeit
    ...: foo.select().\
    ...:     order_by(func.random()).\
    ...:     limit(select([func.round(func.count() * 0.01)]).
    ...:           select_from(foo).
    ...:           as_scalar()).\
    ...:     execute().\
    ...:     fetchall()
    ...: 
307 ms ± 5.72 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [25]: %timeit foo.tablesample(1).select().execute().fetchall()
6.36 ms ± 188 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [26]: %timeit foo.tablesample(func.bernoulli(1)).select().execute().fetchall()
19.8 ms ± 381 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

在匆忙使用SYSTEM抽样方法之前，应该知道它是对页面进行抽样，而不是单个元组，因此对于小表可能不适用，并且如果表聚集，则可能不会产生随机结果。

---

如果使用的方言不允许将样本百分比/行数和种子作为参数传递，并且驱动程序不内联值，则如果这些值是静态的，则可以将它们作为文字SQL文本传递，或者使用自定义SQLA编译器扩展内联它们：

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import TableSample

@compiles(TableSample)
def visit_tablesample(tablesample, self, asfrom=False, **kw):
    """ Compile `TableSample` with values inlined.
    """
    kw_literal_binds = {**kw, "literal_binds": True}
    text = "%s TABLESAMPLE %s" % (
        self.visit_alias(tablesample, asfrom=True, **kw),
        tablesample._get_method()._compiler_dispatch(self, **kw_literal_binds),
    )

    if tablesample.seed is not None:
        text += " REPEATABLE (%s)" % (
            tablesample.seed._compiler_dispatch(self, **kw_literal_binds)
        )

    return text

from sqlalchemy import table, literal, text

# Static percentage
print(table("tbl").tablesample(text("5 PERCENT")))
# Compiler inlined values
print(table("tbl").tablesample(5, seed=literal(42)))

- Ilja Everilä

---

有人能否在Azure SQL数据库中实现这个功能？我正在尝试，但是遇到了“ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses. (497) (SQLExecDirectW); [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')”的错误提示。 - Robert Franklin

好像是一个有趣的边缘情况，实现没有涵盖到；看起来不支持将百分比或种子作为参数传递。Azure方言需要在查询编译器中内联参数。我今天稍后可以提供一个例子。 - Ilja Everilä

@robertfranklin 我希望这些添加能够帮助你让它正常工作。 - Ilja Everilä

Answer 6

这是我的用于随机选择表格行的函数：

from sqlalchemy.sql.expression import func

def random_find_rows(sample_num):
    if not sample_num:
        return []

    session = DBSession()
    return session.query(Table).order_by(func.random()).limit(sample_num).all()

- Charles Wang

Answer 7

这是我使用的解决方案：

from random import randint

rows_query = session.query(Table)                # get all rows
if rows_query.count() > 0:                       # make sure there's at least 1 row
    rand_index = randint(0,rows_query.count()-1) # get random index to rows 
    rand_row   = rows_query.all()[rand_index]    # use random index to get random row

- ChickenFeet

---

这在大表上会非常慢。你需要获取每一行，然后再对其进行切片。 - Matthew

哇，是的，这不太好。如果有一个查询可以获取表记录计数，那将是更好的方法。这是在一个带有小型数据库的 Web 应用程序上完成的，现在已经不再与该公司合作了，所以我无法做太多事情。 - ChickenFeet

Answer 8

这个解决方案将选择一行随机数据

这个解决方案需要主键被命名为id，如果还没有被命名为id的话，应该进行命名：

import random
max_model_id = YourModel.query.order_by(YourModel.id.desc())[0].id
random_id = random.randrange(0,max_model_id)
random_row = YourModel.query.get(random_id)
print random_row

- med116

---

当您的ID存在间隙时，这种方法会失败。 - erickrf

Answer 9

有几种通过SQL的方式，取决于使用的数据源。

（我认为无论如何都可以使用SQLAlchemy）

mysql：

SELECT colum FROM table
ORDER BY RAND()
LIMIT 1

PostgreSQL：

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

MSSQL：

SELECT TOP 1 column FROM table
ORDER BY NEWID()

IBM DB2：

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle：

SELECT column FROM
(SELECT column FROM table
ORDER BY dbms_random.value)
WHERE rownum = 1

然而我不知道有任何标准的方法

- Fire Lancer

---

是的，我知道如何在SQL中完成这个任务（我在 http://beta.stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql#19568 中回答了这个问题），但我正在寻找一个特定于SQLAlchemy的解决方案。 - cnu