query random row from flask-sqlalchemy

Question

I want to randomly query a row's specific column (gamelink is what the column is called) from my sqlite db using flask-sqlalchemy (not sqlalchemy, flask-sqlalchemy).

Currently I am able to do this by querying everything....then using the standard library's random module to randomly select a item from the list.

from flask_sqlalchemy import SQLAlchemy
from flask import Flask, render_template
from sqlalchemy.sql.expression import func
import random

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mp3db.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
db.Model.metadata.reflect(db.engine)

class music(db.Model):
    __table__ = db.Model.metadata.tables['music']

@app.route('/vgmplayer')
def vgmplayer():
    musicdata = music.query.all()
    randommusicdata = random.choice(musicdata)
    print(randommusicdata.gamelink)
    return render_template("musicplayer.html")

However, I expect this db to be around 200,000-1,000,000 rows when its fully built up. I expect that function will cause a lot of time waste? Any way to do this faster?

I tried looking into this but alot of research comes about with the func.random() function from sqlalchemy which seem to give me weird results so that didn't work. example of how it doesn't work:

musicdata = music.query.order_by(func.rand()).first()

Error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: rand
[SQL: SELECT music.gametrackname AS music_gametrackname, music.gamealbum AS music_gamealbum, music.artists AS music_artists, music.platform AS music_platform, music.year AS music_year, music.genre AS music_genre, music.timelength AS music_timelength, music.gamelink AS music_gamelink 
FROM music ORDER BY rand()
 LIMIT ? OFFSET ?]

The name of the `rand` function is an implementation detail of the database. In SQLite it's `random`. — snakecharmerb, Sep 18 '22 at 08:28

score 1 · Answer 1 · answered Sep 18 '22 at 06:12

1

You could order the query randomly, and then return the first result:

from sqlalchemy.sql.expression import func

musicdata = music.query.filter_by(artists = 'Sierra Studios').order_by(func.rand()).first()

answered Sep 18 '22 at 06:12

RJ Adriaansen

9,131
2
12
26

I was an idiot, I had the wrong musicdata variable in the example. It should have been `musicdata = music.query.all()` if I do `musicdata = music.query.all().order_by(func.rand()).first()` it would still have to pull all the data from all the rows. Is there a way I can randomly grab a row without querying every row first? In reality, I wont have a filter to use like the filter_by function. – PythonKiddieScripterX Sep 18 '22 at 06:23
What do you mean? You're just building the ORM Query object – RJ Adriaansen Sep 18 '22 at 06:41
I mean, how do I build the music class to query a random row without querying everything in the sqlite db? if I use `msuic.query.all()` it querries everything. Imagine if I had 1,000,000 rows. Wouldn't that calculation take a long time? So I am trying to NOT use `query_all()`. I wish there was something like a `query_random()` I could use that came with flask-sqlalchemy. Does that make more sense? I can just do what I am doing already, but everytime someone makes a request, it will have to query all rows, then get a random value instead of just getting a random row from the db. – PythonKiddieScripterX Sep 18 '22 at 06:50
Ah ok, check [this](https://stackoverflow.com/questions/11530196/flask-sqlalchemy-query-specify-column-names) for various options to limit the number of columns. You can then add `.order_by(func.rand()).first()` to that query – RJ Adriaansen Sep 18 '22 at 06:54
I apologize I am probably not explaining correctly. I wouldn't need to limit the number of columns in a query. I wouldn't need a limit or filter. My goal was to lower the compute time it takes to query a random row in a sqlite db. I just didn't want to have to query every single row in the database BEFORE use a random function. I wanted a random function to get a row from the database, not get everything from the database and then use a random function to get one item out of the list. I will give you 1+ for the answer but I will delete the post for not a clear enough question. – PythonKiddieScripterX Sep 18 '22 at 07:00
1

Yes we might be completely misunderstanding each other, but `query` doesn't query anything. It builds a [Query object](https://docs.sqlalchemy.org/en/14/orm/query.html). You're basically constructing an SQL statement that will only be run when you call `first()` – RJ Adriaansen Sep 18 '22 at 07:12
@snakecharmerb figured out the issue. its needs to be `func.random()` not `func.rand()`. I already tried `func.rand()` and I was getting errors so that is when I tried to make my own random statement but figured it wasn't fast enough. With your post and @snakecharmerb I got the right answer – PythonKiddieScripterX Sep 18 '22 at 08:32

score 1 · Answer 2 · answered Sep 18 '22 at 08:34

After working with @RJ and @snakecharmerb I figured out the answer. I need to use func.random() instead of func.rand() to pull a random row from my sqlite db.

from flask_sqlalchemy import SQLAlchemy
from flask import Flask, render_template
from sqlalchemy.sql.expression import func
import random

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mp3db.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
db.Model.metadata.reflect(db.engine)

class music(db.Model):
    __table__ = db.Model.metadata.tables['music']

@app.route('/vgmplayer')
def vgmplayer():
    musicdata = music.query.order_by(func.random()).first()
    print(musicdata.gamelink)
    return render_template("musicplayer.html")

Great that you've worked it out! – RJ Adriaansen Sep 18 '22 at 09:17 — RJ Adriaansen, Sep 18 '22 at 09:17

query random row from flask-sqlalchemy

2 Answers2