<p>How do I select one or more random rows from a table using SQLAlchemy? </p>

[{'answer_id': 60811, 'author': 'Fire Lancer', 'author_id': 6266, 'author_profile': 'https://Stackoverflow.com/users/6266', 'pm_score': -1, 'selected': False, 'text': "<p>Theres a couple of ways through SQL, depending on which data base is being used.</p>\n\n<p>(I think SQLAlchemy can use all these anyways)</p>\n\n<p>mysql:</p>\n\n<pre><code>SELECT colum FROM table\nORDER BY RAND()\nLIMIT 1\n</code></pre>\n\n<p>PostgreSQL:</p>\n\n<pre><code>SELECT column FROM table\nORDER BY RANDOM()\nLIMIT 1\n</code></pre>\n\n<p>MSSQL:</p>\n\n<pre><code>SELECT TOP 1 column FROM table\nORDER BY NEWID()\n</code></pre>\n\n<p>IBM DB2:</p>\n\n<pre><code>SELECT column, RAND() as IDX\nFROM table\nORDER BY IDX FETCH FIRST 1 ROWS ONLY\n</code></pre>\n\n<p>Oracle:</p>\n\n<pre><code>SELECT column FROM\n(SELECT column FROM table\nORDER BY dbms_random.value)\nWHERE rownum = 1\n</code></pre>\n\n<p>However I don't know of any standard way</p>\n"}, {'answer_id': 60815, 'author': 'Łukasz', 'author_id': 4999, 'author_profile': 'https://Stackoverflow.com/users/4999', 'pm_score': 8, 'selected': True, 'text': '<p>This is very much a database-specific issue.</p>\n\n<p>I know that PostgreSQL, SQLite, MySQL, and Oracle have the ability to order by a random function, so you can use this in SQLAlchemy:</p>\n\n<pre><code>from  sqlalchemy.sql.expression import func, select\n\nselect.order_by(func.random()) # for PostgreSQL, SQLite\n\nselect.order_by(func.rand()) # for MySQL\n\nselect.order_by(\'dbms_random.value\') # For Oracle\n</code></pre>\n\n<p>Next, you need to limit the query by the number of records you need (for example using <code>.limit()</code>).</p>\n\n<p>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.</p>\n'}, {'answer_id': 390676, 'author': 'David Raznick', 'author_id': 20842, 'author_profile': 'https://Stackoverflow.com/users/20842', 'pm_score': 5, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>import random\nrand = random.randrange(0, session.query(Table).count()) \nrow = session.query(Table)[rand]\n</code></pre>\n\n<p>This is cheating slightly but thats why you use an orm.</p>\n'}, {'answer_id': 14906244, 'author': 'GuySoft', 'author_id': 311268, 'author_profile': 'https://Stackoverflow.com/users/311268', 'pm_score': 5, 'selected': False, 'text': '<p>There is a simple way to pull a random row that IS database independent.\nJust use .offset() . No need to pull all rows:</p>\n\n<pre class="lang-python prettyprint-override"><code>import random\nquery = DBSession.query(Table)\nrowCount = int(query.count())\nrandomRow = query.offset(int(rowCount*random.random())).first()\n</code></pre>\n\n<p>Where Table is your table (or you could put any query there).\nIf 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.</p>\n'}, {'answer_id': 21242325, 'author': 'med116', 'author_id': 1784279, 'author_profile': 'https://Stackoverflow.com/users/1784279', 'pm_score': -1, 'selected': False, 'text': '<h2>this solution will select a single random row</h2>\n\n<p>This solution requires that the primary key is named id, it should be if its not already:   </p>\n\n<pre><code>import random\nmax_model_id = YourModel.query.order_by(YourModel.id.desc())[0].id\nrandom_id = random.randrange(0,max_model_id)\nrandom_row = YourModel.query.get(random_id)\nprint random_row\n</code></pre>\n'}, {'answer_id': 33583008, 'author': 'Jeff Widman', 'author_id': 770425, 'author_profile': 'https://Stackoverflow.com/users/770425', 'pm_score': 5, 'selected': False, 'text': "<p>Here's four different variations, ordered from slowest to fastest. <code>timeit</code> results at the bottom:</p>\n\n<pre><code>from sqlalchemy.sql import func\nfrom sqlalchemy.orm import load_only\n\ndef simple_random():\n    return random.choice(model_name.query.all())\n\ndef load_only_random():\n    return random.choice(model_name.query.options(load_only('id')).all())\n\ndef order_by_random():\n    return model_name.query.order_by(func.random()).first()\n\ndef optimized_random():\n    return model_name.query.options(load_only('id')).offset(\n            func.floor(\n                func.random() *\n                db.session.query(func.count(model_name.id))\n            )\n        ).limit(1).all()\n</code></pre>\n\n<p><code>timeit</code> results for 10,000 runs on my Macbook against a PostgreSQL table with 300 rows:</p>\n\n<pre><code>simple_random(): \n    90.09954111799925\nload_only_random():\n    65.94714171699889\norder_by_random():\n    23.17819356000109\noptimized_random():\n    19.87806927999918\n</code></pre>\n\n<p>You can easily see that using <code>func.random()</code> is far faster than returning all results to Python's <code>random.choice()</code>. </p>\n\n<p>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.</p>\n"}, {'answer_id': 42780139, 'author': 'ChickenFeet', 'author_id': 5387193, 'author_profile': 'https://Stackoverflow.com/users/5387193', 'pm_score': 0, 'selected': False, 'text': "<p>This is the solution I use:</p>\n\n<pre><code>from random import randint\n\nrows_query = session.query(Table)                # get all rows\nif rows_query.count() &gt; 0:                       # make sure there's at least 1 row\n    rand_index = randint(0,rows_query.count()-1) # get random index to rows \n    rand_row   = rows_query.all()[rand_index]    # use random index to get random row\n</code></pre>\n"}, {'answer_id': 50345203, 'author': 'Charles Wang', 'author_id': 3155630, 'author_profile': 'https://Stackoverflow.com/users/3155630', 'pm_score': 2, 'selected': False, 'text': '<p><strong>This is my function to select random row(s) of a table:</strong> </p>\n\n<pre><code>from sqlalchemy.sql.expression import func\n\ndef random_find_rows(sample_num):\n    if not sample_num:\n        return []\n\n    session = DBSession()\n    return session.query(Table).order_by(func.random()).limit(sample_num).all()\n</code></pre>\n'}, {'answer_id': 52692368, 'author': 'Ilja Everilä', 'author_id': 2681632, 'author_profile': 'https://Stackoverflow.com/users/2681632', 'pm_score': 3, 'selected': False, 'text': '<p>Some SQL DBMS, namely Microsoft SQL Server, DB2, and <a href="https://www.postgresql.org/docs/current/static/sql-select.html#SQL-FROM" rel="nofollow noreferrer">PostgreSQL</a> have implemented the SQL:2003 <code>TABLESAMPLE</code> clause. Support was added to SQLAlchemy <a href="https://bitbucket.org/zzzeek/sqlalchemy/issues/3718" rel="nofollow noreferrer">in version 1.1</a>. It allows returning a sample of a table using different sampling methods – the standard requires <code>SYSTEM</code> and <code>BERNOULLI</code>, which return a desired approximate percentage of a table.</p>\n<p>In SQLAlchemy <a href="https://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.FromClause.tablesample" rel="nofollow noreferrer"><code>FromClause.tablesample()</code></a> and <a href="https://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.tablesample" rel="nofollow noreferrer"><code>tablesample()</code></a> are used to produce a <a href="https://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.TableSample" rel="nofollow noreferrer"><code>TableSample</code></a> construct:</p>\n<pre><code># Approx. 1%, using SYSTEM method\nsample1 = mytable.tablesample(1)\n\n# Approx. 1%, using BERNOULLI method\nsample2 = mytable.tablesample(func.bernoulli(1))\n</code></pre>\n<p>There\'s a slight gotcha when used with mapped classes: the produced <code>TableSample</code> object must be aliased in order to be used to query model objects:</p>\n<pre><code>sample = aliased(MyModel, tablesample(MyModel, 1))\nres = session.query(sample).all()\n</code></pre>\n<hr />\n<p>Since many of the answers contain performance benchmarks, I\'ll include some simple tests here as well. Using a simple table in PostgreSQL with about a  million rows and a single integer column, select (approx.) 1% sample:</p>\n<pre><code>In [24]: %%timeit\n    ...: foo.select().\\\n    ...:     order_by(func.random()).\\\n    ...:     limit(select([func.round(func.count() * 0.01)]).\n    ...:           select_from(foo).\n    ...:           as_scalar()).\\\n    ...:     execute().\\\n    ...:     fetchall()\n    ...: \n307 ms ± 5.72 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n\nIn [25]: %timeit foo.tablesample(1).select().execute().fetchall()\n6.36 ms ± 188 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n\nIn [26]: %timeit foo.tablesample(func.bernoulli(1)).select().execute().fetchall()\n19.8 ms ± 381 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n</code></pre>\n<p>Before rushing to use <code>SYSTEM</code> sampling method one should know that it samples <em>pages</em>, not individual tuples, so it might not be suitable for small tables, for example, and may not produce as random results, if the table is clustered.</p>\n<hr />\n<p>If using a dialect that does not allow passing the sample percentage / number of rows and seed as parameters, and a driver that does not inline values, then either pass the values as literal SQL text <em>if they are static</em>, or inline them using a custom SQLA compiler extension:</p>\n<pre><code>from sqlalchemy.ext.compiler import compiles\nfrom sqlalchemy.sql import TableSample\n\n@compiles(TableSample)\ndef visit_tablesample(tablesample, self, asfrom=False, **kw):\n    &quot;&quot;&quot; Compile `TableSample` with values inlined.\n    &quot;&quot;&quot;\n    kw_literal_binds = {**kw, &quot;literal_binds&quot;: True}\n    text = &quot;%s TABLESAMPLE %s&quot; % (\n        self.visit_alias(tablesample, asfrom=True, **kw),\n        tablesample._get_method()._compiler_dispatch(self, **kw_literal_binds),\n    )\n\n    if tablesample.seed is not None:\n        text += &quot; REPEATABLE (%s)&quot; % (\n            tablesample.seed._compiler_dispatch(self, **kw_literal_binds)\n        )\n\n    return text\n\nfrom sqlalchemy import table, literal, text\n\n# Static percentage\nprint(table(&quot;tbl&quot;).tablesample(text(&quot;5 PERCENT&quot;)))\n# Compiler inlined values\nprint(table(&quot;tbl&quot;).tablesample(5, seed=literal(42)))\n</code></pre>\n'}, {'answer_id': 62321734, 'author': 'Anas', 'author_id': 12861001, 'author_profile': 'https://Stackoverflow.com/users/12861001', 'pm_score': -1, 'selected': False, 'text': '<p>Use this simplest method\nthis example on choosing a random question from the database:-</p>\n\n<pre><code>#first import the random module\nimport random\n\n#then choose what ever Model you want inside random.choise() method\nget_questions = random.choice(Question.query.all())\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/60805', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1448/']