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)
SELECT colum FROM table ORDER BY RAND() LIMIT 1
SELECT column FROM table ORDER BY RANDOM() LIMIT 1
SELECT TOP 1 column FROM table ORDER BY NEWID()
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY
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