blocks|key|1551879|text|这在很大程度上是数据库特有的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1551880|我知道PostgreSQL、SQLite、MySQL和Oracle能够按随机函数排序，因此您可以在SQLAlchemy中使用以下命令：|1551881|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|code-block|syntax|javascript|1551882|接下来，您需要根据需要的记录数量限制查询(例如，使用.limit())。|offset|length|style|CODE|1551883|请记住，至少在PostgreSQL中，选择随机记录有严重的性能问题；here就是一篇很好的文章。|1551884|entityMap|0|LINK|mutability|MUTABLE|url|http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/^0|0|0|0|Q|8|0|Y|4|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@$K|12|L|13|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@$K|15|L|16|1|17]]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

This is very much a database-specific issue.

I know that PostgreSQL, SQLite, MySQL, and Oracle have the ability to order by a random function, so you can use this in SQLAlchemy:

<pre><code>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
</code></pre>

Next, you need to limit the query by the number of records you need (for example using <code>.limit()</code>).

Bear in mind that at least in PostgreSQL, selecting random record has severe perfomance issues; <a href="http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/" rel="noreferrer">here</a> is good article about it.

blocks|key|1766943|text|如果您正在使用orm，并且表并不大(或者您缓存了它的行数)，并且您希望它独立于数据库，那么真正简单的方法是。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1766944|import+random
rand+=+random.randrange(0,+session.query(Table).count())+
row+=+session.query(Table)[rand]|code-block|syntax|javascript|1766945|这是稍微作弊，但这就是你使用orm的原因。|1766946|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you are using the orm and the table is not big (or you have its amount of rows cached) and you want it to be database independent the really simple approach is.

<pre><code>import random
rand = random.randrange(0, session.query(Table).count()) 
row = session.query(Table)[rand]
</code></pre>

This is cheating slightly but thats why you use an orm.

blocks|key|1766991|text|有一种简单的方法可以拉取一个与数据库无关的随机行。只要使用.offset()即可。不需要拉取所有行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1766992|import+random
query+=+DBSession.query(Table)
rowCount+=+int(query.count())
randomRow+=+query.offset(int(rowCount*random.random())).first()|code-block|syntax|javascript|1766993|其中Table是您的表(或者您可以在其中放置任何查询)。如果您想要几行，那么您可以多次运行此命令，并确保每一行都不同于前一行。|1766994|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

There is a simple way to pull a random row that IS database independent.
Just use .offset() . No need to pull all rows:

<pre class="lang-python prettyprint-override"><code>import random
query = DBSession.query(Table)
rowCount = int(query.count())
randomRow = query.offset(int(rowCount*random.random())).first()
</code></pre>

Where Table is your table (or you could put any query there).
If you want a few rows, then you can just run this multiple times, and make sure that each row is not identical to the previous.

blocks|key|1552081|text|这里有四种不同的变体，从最慢到最快排序。底部的timeit结果：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1552082|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()|code-block|syntax|javascript|1552083|在我的Macbook上，对一个有300行的PostgreSQL表运行10,000个timeit结果：|1552084|simple_random():+
++++90.09954111799925
load_only_random():
++++65.94714171699889
order_by_random():
++++23.17819356000109
optimized_random():
++++19.87806927999918|1552085|您可以很容易地看到，使用func.random()比将所有结果返回到Python的random.choice()快得多。|1552086|此外，随着表大小的增加，order_by_random()的性能将显著降低，因为ORDER+BY需要全表扫描，而optimized_random()中的COUNT可以使用索引。|1552087|entityMap^0|N|6|0|0|15|6|0|0|C|D|15|F|0|C|H|14|8|1K|I|24|5|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|17|8|@$9|18|A|19|B|C]|$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|T|$]]

Here's four different variations, ordered from slowest to fastest. <code>timeit</code> results at the bottom:

<pre><code>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()
</code></pre>

<code>timeit</code> results for 10,000 runs on my Macbook against a PostgreSQL table with 300 rows:

<pre><code>simple_random(): 
 90.09954111799925
load_only_random():
 65.94714171699889
order_by_random():
 23.17819356000109
optimized_random():
 19.87806927999918
</code></pre>

You can easily see that using <code>func.random()</code> is far faster than returning all results to Python's <code>random.choice()</code>. 

Additionally, as the size of the table increases, the performance of <code>order_by_random()</code> will degrade significantly because an <code>ORDER BY</code> requires a full table scan versus the <code>COUNT</code> in <code>optimized_random()</code> can use an index.

blocks|key|1552117|text|这是我使用的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1552118|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|code-block|syntax|javascript|1552119|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is the solution I use:

<pre><code>from random import randint

rows_query = session.query(Table) # get all rows
if rows_query.count() &gt; 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
</code></pre>

blocks|key|1767017|text|此解决方案将选择单个随机行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1767018|此解决方案要求主键命名为id，如果尚未命名，则应该命名为id：|1767019|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|code-block|syntax|javascript|1767020|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

<h2>this solution will select a single random row</h2>

This solution requires that the primary key is named id, it should be if its not already: 

<pre><code>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
</code></pre>

blocks|key|1552173|text|使用这个最简单的方法从数据库中选择一个随机问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1552174|#first+import+the+random+module
import+random

#then+choose+what+ever+Model+you+want+inside+random.choise()+method
get_questions+=+random.choice(Question.query.all())|code-block|syntax|javascript|1552175|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use this simplest method
this example on choosing a random question from the database:-

<pre><code>#first import the random module
import random

#then choose what ever Model you want inside random.choise() method
get_questions = random.choice(Question.query.all())
</code></pre>

blocks|key|1766839|text|通过SQL有几种方法，这取决于所使用的数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1766840|(我认为SQLAlchemy无论如何都可以使用所有这些)|1766841|mysql：|1766842|SELECT+colum+FROM+table
ORDER+BY+RAND()
LIMIT+1|code-block|syntax|javascript|1766843|PostgreSQL：|1766844|SELECT+column+FROM+table
ORDER+BY+RANDOM()
LIMIT+1|1766845|MSSQL：|1766846|SELECT+TOP+1+column+FROM+table
ORDER+BY+NEWID()|1766847|IBM+DB2：|1766848|SELECT+column,+RAND()+as+IDX
FROM+table
ORDER+BY+IDX+FETCH+FIRST+1+ROWS+ONLY|1766849|Oracle：|1766850|SELECT+column+FROM
(SELECT+column+FROM+table
ORDER+BY+dbms_random.value)
WHERE+rownum+=+1|1766851|但是我不知道有什么标准的方法|1766852|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1B|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1D|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|H|7|1F|8|@]|9|@]|A|$I|J]]|$1|10|3|11|5|6|7|1G|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|13|$]]

Theres a couple of ways through SQL, depending on which data base is being used.

(I think SQLAlchemy can use all these anyways)

mysql:

<pre><code>SELECT colum FROM table
ORDER BY RAND()
LIMIT 1
</code></pre>

PostgreSQL:

<pre><code>SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
</code></pre>

MSSQL:

<pre><code>SELECT TOP 1 column FROM table
ORDER BY NEWID()
</code></pre>

IBM DB2:

<pre><code>SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
</code></pre>

Oracle:

<pre><code>SELECT column FROM
(SELECT column FROM table
ORDER BY dbms_random.value)
WHERE rownum = 1
</code></pre>

However I don't know of any standard way

问通过SQLAlchemy获取随机行
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过SQLAlchemy获取随机行EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过SQLAlchemy获取随机行
EN