On Thu, Jul 11, 2019, at 7:41 AM, Abdeali Kothari wrote: > I am trying to use SQLAlchemy to do some smart joins for me without me having > to explicitly figure out the joins during queries. > (i.e. by figuring out the relationships on its own to figure out how the > tables are related to each other) > > I have an example where i have BookSeries -> Book -> Boot2AuthorTable -> > Author > to link a series to the authors who wrote the series. > > If I do something like: > >>> print(Query(BookSeries).join(Author)) > It throws an error: > InvalidRequestError: Don't know how to join to <class '__main__.Author'>; > please use an ON clause to more clearly establish the left side of this join > > Doing an explicit join one-by-one > >>> print(Query(BookSeries).join(Book).join(Book2Author).join(Author)) > SELECT ... > FROM bookseries > JOIN book ON bookseries.series_id = book.series_id > JOIN auth2book ON book.book_id = auth2book.book_id > JOIN author ON author.author_id = auth2book.author_id > > Seems to do what I expected it to do. > > I'm trying to figure out if there any way for me to not have to give it all > the tables in between and it auto-magically figured it out for me ? > Note: I understand that not all examples are as simple as this one. And there > are nuances about when to do join/leftjoin/etc. and also about multiple > possible paths existing between the tables. > Assuming those are not an issue for now. > > Also, the reason I do not want to mention the intermediate tables myself, is > because the schema of all the tables are not managed by me - as it is read > from an external database. > > Either sqlalchemy itself, extensions, or third party libraries, or any > pointers on logic to how I can solve something like this would be appreciated > !
SQLAlchemy only knows how to do such joins if there is a relationship() construct present. Since it seems you are saying you don't want to write the relationship() yourself, the automap extension will attempt to guess these for you: https://docs.sqlalchemy.org/en/13/orm/extensions/automap.html#many-to-many-relationships however automap may lack the necessary API granularity to implement *only* relationships and not figure everything else out too, but I'd suggest playing with it and maybe reading the source a bit to get some ideas for how you might adapt this to your exact needs. > > > > import sqlalchemy as sa > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm.query import Query > from sqlalchemy.orm import relationship > > Base = declarative_base() > > class BookSeries(Base): > __tablename__ = "bookseries" > pk_id = sa.Column(sa.String, primary_key=True) > series_id = sa.Column(sa.String) > series_name = sa.Column(sa.String) > books = relationship('Book', back_populates='book_series') > > > > class Book(Base): > __tablename__ = "book" > pk_id = sa.Column(sa.String, primary_key=True) > book_id = sa.Column(sa.String) > series_id = sa.Column(sa.String, sa.ForeignKey('bookseries.series_id')) > book_name = sa.Column(sa.String) > book_series = relationship('BookSeries', back_populates='books') > book_authors = relationship('Book2Author', back_populates='book') > > > class Book2Author(Base): > __tablename__ = "auth2book" > pk_id = sa.Column(sa.String, primary_key=True) > author_id = sa.Column(sa.String, sa.ForeignKey('author.author_id')) > book_id = sa.Column(sa.String, sa.ForeignKey('book.book_id')) > author = relationship('Author') > book = relationship('Book', back_populates='book_authors') > > > class Author(Base): > __tablename__ = "author" > pk_id = sa.Column(sa.String, primary_key=True) > author_id = sa.Column(sa.String) > author_name = sa.Column(sa.String) > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/fe10fd2d-c792-4257-b453-696262232df1%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/fe10fd2d-c792-4257-b453-696262232df1%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2ad733c3-fcc9-45a1-b246-31ffe9a3fbc1%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
