Getting random row through SQLAlchemy

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

Solution 1:

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.

Solution 2:

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.

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

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

Solution 3:

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

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

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.