CODEWITHSUNDEEP
pythonrandomsqlalchemy
lea
lea

Reputation: 53

How to set random seed in sqlalchemy.sql.expression.func.random() in python?

I would like to set a random seed when shuffling a sqlalchmy query result using order_by to get always the same query result: 

my_query.order_by(func.random())

I could not find how to do it in the documentation. I tried it with 

my_query.order_by(func.random(random_seed=1))
my_query.order_by(func.random(random_state=1))

but both didn't work. Thank you in advance for any idea/hint!

All best, Lea

Upvotes: 5

Views: 1665

Answers (1)

RazerM
RazerM

Reputation: 5482

The SQLAlchemy func object just renders functions in SQL.

>>> from sqlalchemy import func
>>> print(func.random())
random()

In some cases (including this one), it will render a different underlying function on different database backends, e.g. on MySQL:

>>> from sqlalchemy.dialects import mysql
>>> print(func.random().compile(dialect=mysql.dialect()))
rand()

This means that the database you're using is what produces the random number, and so you must call a database specific function to set the seed, e.g. on PostgreSQL you use setseed, which is called in the same way as other SQL functions:

>>> func.setseed(0.123...)

Upvotes: 2

Related Questions