query random row from flask-sqlalchemy

PythonKiddieScripterX 0
Viewed 43

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 ?]
sqlalchemy flask-sqlalchemy python
2 Answers

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

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")
PythonKiddieScripterX0
Related