I have a list of teams names. For every team name in the list I want to get the row from the database but I want to make only one call to the database and the list of sql alchemy objects need to preserve the order of the original list. I will have duplicated names in the original list.
I need something like this, but working(the query I'm doing obviously doesn't work since it returns everything ordered by id)
teams_names = ['Liverpool', 'Arsenal', 'Utd', 'Liverpool']
Session.query(Team).filter(Team.name.in_(teams_names)).all()
teams_sa_obj = [#sa_liverpool, #sa_arsenal, #sa_utd, #sa_liverpool]
I usually do the reordering in Python. The idea is that you build a map of name to Team objects, then you look them up while iterating over the original list:
q = Session.query(Team).filter(Team.name.in_(teams_names))
team_map = {t.name: t for t in q}
teams = [team_map[n] for n in teams_names]
There are SQL-side solutions, but those are kind of awkward to do with SQLAlchemy.