Python Flask and SQLAlchemy, selecting all data from a column

Question

I am attempting to query all rows for a column called show_id. I would then like to compare each potential item to be added to the DB with the results. Now the simplest way I can think of doing that is by checking if each show is in the results. If so pass etc. However the results from the below snippet are returned as objects. So this check fails.

Is there a better way to create the query to achieve this?

shows_inDB = Show.query.filter(Show.show_id).all()
print(shows_inDB)

Results:

<app.models.user.Show object at 0x10c2c5fd0>, 
<app.models.user.Show object at 0x10c2da080>, 
<app.models.user.Show object at 0x10c2da0f0>

Code for the entire function:

def save_changes_show(show_details):
    """
    Save the changes to the database
    """  
    try:
        shows_inDB = Show.query.filter(Show.show_id).all()
        print(shows_inDB)

        for show in show_details:

            #Check the show isnt already in the DB
            if show['id'] in shows_inDB:
                print(str(show['id']) + ' Already Present')
            else:

                #Add show to DB
                tv_show = Show(
                    show_id = show['id'],
                    seriesName = str(show['seriesName']).encode(),
                    aliases = str(show['aliases']).encode(),
                    banner = str(show['banner']).encode(),
                    seriesId = str(show['seriesId']).encode(),
                    status = str(show['status']).encode(),
                    firstAired = str(show['firstAired']).encode(),
                    network = str(show['network']).encode(),
                    networkId = str(show['networkId']).encode(),
                    runtime = str(show['runtime']).encode(),
                    genre = str(show['genre']).encode(),
                    overview = str(show['overview']).encode(),
                    lastUpdated = str(show['lastUpdated']).encode(),
                    airsDayOfWeek = str(show['airsDayOfWeek']).encode(),
                    airsTime = str(show['airsTime']).encode(),
                    rating = str(show['rating']).encode(),
                    imdbId = str(show['imdbId']).encode(),
                    zap2itId = str(show['zap2itId']).encode(),
                    added = str(show['added']).encode(),
                    addedBy = str(show['addedBy']).encode(),
                    siteRating = str(show['siteRating']).encode(),
                    siteRatingCount = str(show['siteRatingCount']).encode(),
                    slug = str(show['slug']).encode()
                )
                db.session.add(tv_show)

                db.session.commit()
    except Exception:
        print(traceback.print_exc())

score 1 · Answer 1 · answered Jan 05 '19 at 22:32

1

I have decided to use the method above and extract the data I wanted into a list, comparing each show to the list.

show_compare = []
shows_inDB = Show.query.filter().all()
for item in shows_inDB:
   show_compare.append(item.show_id)


for show in show_details:
    #Check the show isnt already in the DB
    if show['id'] in show_compare:
        print(str(show['id']) + ' Already Present')
    else:
         #Add show to DB

answered Jan 05 '19 at 22:32

iNoob

1,375
3
19
47

It worked great.... When i used `query.with_entities(User.username).all()`, it lists out the data with annoying brackets and commas. This above code makes it perfect...and it lists only the data in the column.... – Smack Alpha Jul 03 '19 at 04:58

score 1 · Answer 2 · answered Jan 05 '19 at 22:48

For querying a specific column value, have a look at this question: Flask SQLAlchemy query, specify column names. This is the example code given in the top answer there:

result = SomeModel.query.with_entities(SomeModel.col1, SomeModel.col2)

The crux of your problem is that you want to create a new Show instance if that show doesn't already exist in the database.

Querying the database for all shows and looping through the result for each potential new show might become very inefficient if you end up with a lot of shows in the database, and finding an object by identity is what an RDBMS does best!

This function will check to see if an object exists, and create it if not. Inspired by this answer:

def add_if_not_exists(model, **kwargs):
    if not model.query.filter_by(**kwargs).first():
        instance = model(**kwargs)
        db.session.add(instance)

So your example would look like:

def add_if_not_exists(model, **kwargs):
    if not model.query.filter_by(**kwargs).first():
        instance = model(**kwargs)
        db.session.add(instance)

for show in show_details:
    add_if_not_exists(Show, id=show['id'])

If you really want to query all shows upfront, instead of putting all of the id's into a list, you could use a set instead of a list which will speed up your inclusion test.

E.g:

show_compare = {item.show_id for item in Show.query.all()}
for show in show_details:
   # ... same as your code

Python Flask and SQLAlchemy, selecting all data from a column

2 Answers2