How do you choose a(or some) random row(s) from the table using SQLAlchemy?

This really is greatly database specifc problem.

I understand that PostgreSQL and MySQL has abbility to order by random function, so This can be used in SQLAlchemy:

select.order_by(func.random()) # for PostgreSQL

select.order_by(func.rand()) # for MySQL

Next, be sure to limit query to amout of records You'll need (for instance using .limit()).

Keep in mind that a minimum of in PostgreSQL selending random record has severe perfomance issues, here is nice article about this.

If you work with the orm and also the table isn't large (or else you have its quantity of rows cached) and also you would like it to be database independent the truly simple approach is.

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

This really is cheating slightly but for this reason you utilize an orm.

An enhanced version of Lukasz's example, within the situation you have to choose multiple rows randomly:

import random

# you must first select all the values of the primary key field for the table.
# in some particular cases you can use xrange(session.query(Table).count()) instead
ids = session.query(Table.primary_key_field).all() 
ids_sample = random.sample(ids, 100)

rows = session.query(Table).filter(Table.primary_key_field.in_(ids_sample))

So, this publish is simply to indicate which you can use .in_ to choose multiple fields simultaneously.

I recommend you apply the Orm Table. SEe publish #1.

Theres a few ways through SQL, based on which database has been used.

(I believe SQLAlchemy may use each one of these anyways)

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

However I'm not sure associated with a standard way