SQLAlchemy: retrieve all episodes from favorite_series of specific user

First of all you don't seem to be declaring your table names? Also, the whole point of bothering with orm is so you never have to write sql queries:

from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import ormimport sqlalchemy as dbBase = declarative_base()favorite_series = db.Table('favorite_series', Base.metadata,    db.Column('user_id', db.Integer, db.ForeignKey('User.id')),    db.Column('series_id', db.Integer, db.ForeignKey('Series.id')))class Episode(Base):    __tablename__ = 'Episode'    id = db.Column(db.Integer, primary_key=True)    season = db.Column(db.Integer)    episode_num = db.Column(db.Integer)    series_id = db.Column(db.Integer, db.ForeignKey('Series.id'))    def __init__(self, season, episode_num, series_id):        self.season = season        self.episode_num = episode_num        self.series_id = series_id    def __repr__(self):        return self.series.title + \               ' S' + str(self.season) + \               'E' + str(self.episode_num)class Series(Base):    __tablename__ = 'Series'    id = db.Column(db.Integer, primary_key=True)    title = db.Column(db.String)    episodes = orm.relationship('Episode', backref='series')    def __init__(self, title):        self.title = title    def __repr__(self):        return self.titleclass User(Base):    __tablename__ = 'User'    id = db.Column(db.Integer, primary_key=True)    name = db.Column(db.String)    favorite_series = orm.relationship('Series',         secondary=favorite_series, backref='users')    def __init__(self, name):        self.name = name    def __repr__(self):        return self.name

Now you can just access the attributes of your objects and let sql alchemy deal with keeping you DB in sync and issuing queries.

engine = db.create_engine('sqlite:///:memory:')session = orm.sessionmaker(bind=engine)()Base.metadata.create_all(engine)lt = User('Ludovic Tiako')the_wire = Series('The Wire')friends = Series('Friends')session.add_all([lt, the_wire, friends])session.commit() # need to commit here to generate the id fieldstw_s01e01 = Episode(1,1,the_wire.id)tw_s01e02 = Episode(1,2,the_wire.id)f_s01e01 = Episode(1,1,friends.id)f_s01e02 = Episode(1,2,friends.id)f_s01e03 = Episode(1,3,friends.id)session.add_all([tw_s01e01, tw_s01e02,                 f_s01e01, f_s01e02, f_s01e03])session.commit()the_wire.episodes # > [The Wire S1E1, The Wire S1E2]friends.episodes # > [Friends S1E1, Friends S1E2, Friends S1E3]

Finally, to answer your question:

lt.favorite_series.append(the_wire)session.commit()lt.favorite_series # > [The Wire][s.episodes for s in lt.favorite_series] # >> [[The Wire S1E1, The Wire S1E2]]

I don't know about Flask, but from the docs of Flask-SQLAlchemy, it seems it uses declarative, so the ORM. And so, you should have a session. I think it is accessible to you from db.session.

Anyway, if those assumptions are true, this is how you should do it:

query = db.session.query(User.id, Series.name, Episode.name).filter((Episode.series_id == Series.id) & \    (User.id == favorite_series.c.user_id) & (Series.id == favorite_series.c.id) & \    (User.id == 1))results = query.all();

It might not be the exact query you provided, but should do the same.

UPDATE: I just checked Flask-SQLALchemy code on github, it seems that db is an instance of SQLAlchemy, which has a session attribute, created by self.session = self.create_scoped_session(session_options) which returns a session object. So this should work.

Also, not that by doing that, you won't be using their BaseQuery, although I don't know what that would mean...

Check the documentation to know what to do exactly.