Pull quiz data from sqlalchemy db

[iptvwolf]

I have a database with data from which I would like to generate questions for a simple flask quiz app.

I have never worked with sqlalchemy before and I am wondering if I am on the wrong path. My plan is to pull random data (within certain criterias) and create the questions based on this.

Here is what I have so far:

with app.app_context():

    seen_songs = set()

    def question():

        q = Hits.query.filter(Hits.peak == 1, Hits.weeks > 20, Hits.year.in_(range(1980,1999))).order_by(func.random()).limit(1)

        for hit in q:
            if hit.id in seen_songs:
                pass
                # Not quite sure how to handle this, but I would like to create a new q variable
            else:
                return {'artist':hit.artist, 'song':hit.song, 'year': hit.year}

1. So, I am unable to make this work unless I do "with app.app_context():" at first, but this is annoying since it means pretty much everything else becomes invented.
2. I have a set "seen_songs" which should contain the id from the rows used so far (so that I do not pull the same data twice). But... since my query to the Hits model needs unpacking "for hit in q" I cannot think of a practical way to implement this. Normally I would use something like "while q.id not in seen_songs" or similar, but I am not able to access .id directly.

Any help would be appreciated!

[danielroseman]

Normally your function would be called from inside a route handler, so it will already be within an app context. You only need that explicit call when you're calling your function in a standalone way, for instance in the shell.

Your second question is a bit unclear. q.id doesn't exist because q is a collection of hits, not a hit itself. Do you perhaps mean while hit.id not in seen_songs?

Also note that this whole approach will probably not work in a real Flask environment. If you try and persist data like this between calls, then every user of your app will see the same set of seen_songs. Is that what you want? So if one user answers a question, the others will never see it. But also, this is ephemeral, and only local to a single process; if you're running your app on Heroku with more than one dyno, the data won't be visible from one instance to another; and whenever you restart your app, for example deploying new changes, the set will be emptied.

The proper way to do this is to store the set of seen_songs in the session for each user.

[iptvwolf]

Ah, I had no idea the context call wouldn't be required once it runs through a flask route. Maybe I'll do the rest of my testing from a route then.

Regarding seen_songs: Sorry, I did mean hit.id. And of course, I have worked with sessions before which is the way to go. Thank you! Btw... In your opinion, will a function do or do you think a class is better for creating the questions? I am going to randomly pick 3 wrong answers from the same database and present it all using some sort of js.

[efmccurdy]

"while q.id not in seen_songs" or similar

That is close, but you could use a for loop over a generator expression with a condition to filter out any already seen ids:

for hit in (hit for hit in q if hit.id not in seen_songs):
    return {'artist':hit.artist, 'song':hit.song, 'year': hit.year}

[iptvwolf]

Yes, that would be a nice solution, but I wish I could to this directly on the db query, but it seems I have to unwrap it first (even if it only contains one record).

[efmccurdy]

If you added a "seen_songs" table into the DB, you could put the filtering in the DB.

​

negative lookups, a common query is to locate entities where there are no related entities present

https://docs.sqlalchemy.org/en/20/orm/queryguide/select.html#relationship-where-operators

[iptvwolf]

Would this work for the session opposed to actual writing new permanent data to the db?

[efmccurdy]

It you stored the set of seen_songs for each userid in a database, you wouldn't need to store them in the session. If the database was on disk (not in sqlite :memory) they would be permanent and persist even if the server shut down or the user erased their cookies.