Getting random row through SQLAlchemy

cnu picture cnu · Sep 13, 2008 · Viewed 32.2k times · Source

How do I select one or more random rows from a table using SQLAlchemy?

Answer

Łukasz picture Łukasz · Sep 13, 2008

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:

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

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

Bear in mind that at least in PostgreSQL, selecting random record has severe perfomance issues; here is good article about it.