Get random rows from postgres more than number of rows

Question

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?

Have you checked that? Maybe there is some info about it https://stackoverflow.com/questions/60805/getting-random-row-through-sqlalchemy — IoaTzimas, Dec 28 '20 at 14:07
Is your question how to add more items to the table? Or how to rewrite the limit? — yvesonline, Dec 28 '20 at 14:07
@yvesonline how to rewrite limit... I have 100 table records. But I want to get 200 rows randomly repeated items. — Mehrdad Pedramfar, Dec 28 '20 at 14:11
You can always pull the rows with raw sql statement, as a list of tuples and apply some random_choice_with_replacement from common libraries (random, numpy, etc) — IoaTzimas, Dec 28 '20 at 14:13
@IoaTzimas I think you did not understand the question well. — Mehrdad Pedramfar, Dec 28 '20 at 14:15
You have 100 rows but you want 200 isn't it? Which means that some of them will be chosed more than once. Am I right? — IoaTzimas, Dec 28 '20 at 14:17
You can pull the rows, save them in a list and use `random.choices(yourlist, k=200)` — IoaTzimas, Dec 28 '20 at 14:19
I can do that, But the point is to implement this using only ORM functionality. — Mehrdad Pedramfar, Dec 28 '20 at 14:20
Actually limit comes from client side, and I do not know how much is it, whether more or less than 100. I'm looking for a solution to handle both cases. @IoaTzimas — Mehrdad Pedramfar, Dec 28 '20 at 14:22
Ok, then my suggestion is not good. (However result will be the same, if you apply the random choice of rows inside or outside ORM) — IoaTzimas, Dec 28 '20 at 14:22
If you create a temporary table with 2 rows and do a "cross join" against your 100-row table the result will be 200 rows. — Gord Thompson, Dec 28 '20 at 14:27

yvesonline · Answer 1 · 2020-12-28T14:20:30.617

1

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())])

edited Dec 28 '20 at 14:20

answered Dec 28 '20 at 14:18

yvesonline

4,609
2
21
32

Thanks for your answer. But the point is to implement this using only ORM functionality. – Mehrdad Pedramfar Dec 28 '20 at 14:20
1

"the point is to implement this using only ORM functionality" - Unless this is a homework assignment where the instructor is insisting on extra arbitrary requirements then don't get too hung up on being an ORM "purist". Just because you are using ORM doesn't necessarily mean that you have to use *only* ORM. – Gord Thompson Dec 28 '20 at 14:39

score 1 · Answer 2 · answered Aug 22 '21 at 10:12

1

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())

answered Aug 22 '21 at 10:12

snakecharmerb

47,570
11
100
153

Get random rows from postgres more than number of rows

2 Answers2