CODEWITHSUNDEEP
pythonpostgresqlrandomsqlalchemy
Mehrdad Pedramfar
Mehrdad Pedramfar

Reputation: 11073

Get random rows from postgres more than number of rows

I'm using SQLALchemy and Postgresql.

Imagine I have a SQLAlchemy class Items,

There are 100 items in this table, I want to get for example 200 random rows(expected rows are not unique indeed).

for getting less than 100 easily I do:

items = session.query(Items)\
    .order_by(func.random())\
    .limit(80)\
    .all()

But how can I get more than 100 rows if only I have 100?

Upvotes: 1

Views: 158

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55759

For small-ish tables you could run two selects and select from their union:

q1 = session.query(Items)
q2 = session.query(Items)
cte = q1.union_all(q2).cte()
items_cte = orm.aliased(Items, cte)
items = session.query(items_cte).order_by(func.random())

Upvotes: 1

yvesonline
yvesonline

Reputation: 4837

I'd probably opt to duplicate entries in pure Python rather than SQLAlchemy, basically just looping and randomly picking from your result set, it's not especially elegant but will get the job done:

import random

limit = 200

items = session.query(Items).all()

for i in range(limit):
    print(items[random.randrange(items.count())])

Upvotes: 1

Related Questions