SQLAlchemy result shuffling?

Question

The code in question:

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

This is in a Flask / SQLAlchemy application. Item is a model.

What I'm doing right now and it works, but I can foresee a disaster when I start using a real data set.

How to do it right?

Answer 1

I am not entirely sure of your concern. Does this mean that you will return a large data set, so will large lists be processed in memory?

If so, you can do it in the SQL statement if you are not too worried about portability; that is, if you use MySQL, you can do:

 Item.query.order_by(func.rand()).offset(20).limit(10).all() 

Or, in PostgreSQL:

 Item.query.order_by(func.random()).offset(20).limit(10).all() 

Other databases have similar mechanisms, so the function you call will depend on what you are aiming for. And, of course, if you are trying to write a general-purpose application that can run on any of the SQLAlchemy components, you might have to stick with the above example.