querying from a many to many relationship sqlalchemy

Question

I have this database scheme, a many-to-many relationship:

Base = declarative_base()

association_table = Table('association', Base.metadata,
    Column('book_id', Integer, ForeignKey('book.book_id')),
    Column('author_id', Integer, ForeignKey('author.author_id')),
)

class Book(Base):
    __tablename__ = 'book'
    book_id = Column(Integer, primary_key=True)
    title = Column(String(50), nullable = False)
    authors = relationship('Author', secondary = association_table, backref=backref('books', lazy= 'dynamic'))

class Author(Base):
    __tablename__ = 'author'
    author_id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable = False)

where I want to query data from. I know how to query from objects I created, like:

a=Author(name='Author One')
b=Book(title='Book Title One')
session.add(b)
session.add(a)
session.commit() 
a.books.append(b)
session.commit()
# repeat above steps with new variables a2 and b2
# a.books.append(b2) 
for i in a.books:
   print(i.title)

However, how do I query directly from the table to see what authors are related to a specific book? I.e., the next time I wan to use the database, but don't have the objects anymore. Things I tried:

for u in session.query(Book.title).\
        filter(Book.book_id==Author.author_id).\
        filter(Author.name=='Author One').\
        all():
    print(u) # doesn't seem to work, returns only one element.

x = session.query(Author).filter(Book.title.any(name='Book Title One')).all()
# gives AttributeError: Neither 'AnnotatedColumn' object nor 'Comparator' object has an attribute 'any' error.

But they all seem to fail, or return the incorrect amount.

Related: https://stackoverflow.com/questions/40699642/how-to-query-many-to-many-sqlalchemy, https://stackoverflow.com/questions/32575502/how-to-query-many-to-many-based-on-some-constraints-in-flask-sqlalchemy — Ilja Everilä, Aug 12 '18 at 10:15

score 5 · Accepted Answer · answered Aug 12 '18 at 08:56

You can recreate your objects in a new session by querying the database. For example, you can fetch an author

  author = session.query(Author).filter_by(name='Author One').one()

then just iterate over their books collection:

  print('Author\'s books:')
  for b in author.books:
      print(b.title)

Author's books:
Book One
Book Three

Or use the author object to query the Book model:

  query = session.query(Book).filter(Book.authors.contains(author))
  print('Books\' authors:')
  for b in query:
      print(b.title, ', '.join(a.name for a in b.authors))

Book's authors:
Book One Author One
Book Three Author Two, Author One

If you don't want to fetch the author object, you can query the Book model using the author's name like this:

  query = session.query(Book).filter(Book.authors.any(name='Author One'))
  print('Books by Author One:')
  for b in query:
      print(b.title, ', '.join(a.name for a in b.authors))

Books by Author One:
Book One Author One
Book Three Author Two, Author One

querying from a many to many relationship sqlalchemy

1 Answers1