Shuffling SQLAlchemy results?

Question

The code in question:

 random_items = random.shuffle(Item.query.all())[20:30]

It's in a Flask/SQLAlchemy app.Item is the model.

That's what I'm doing right now and it works but I foresee a disaster when I start using a real dataset.

How do I do this properly?

Answer 1

I'm not entirely sure of your concern. Is it that you'll be returning a large dataset, so large lists will be manipulated in memory?

If so, you can do it within the SQL statement if you're not overly worried about portability; i.e., if you are using MySQL, you can do:

from sqlalchemy.sql.expression import func
Item.query.order_by(func.rand()).offset(20).limit(10).all()

Or, in PostgreSQL:

from sqlalchemy.sql.expression import func
Item.query.order_by(func.random()).offset(20).limit(10).all()

Other databases have similar mechanisms, so the function called would depend on what you're targeting. And of course, if you are trying to write a general purpose app that can run on any of SQLAlchemy's backends, you might need to stick with the example provided.