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))
to be more specific, instead of using automap directly, you could look at how
it traverses through all the tables in a MetaData collection to find linkages,
and you could write your own function:
def join(some_query, source, dest):
# ...
which finds a path between source and dest. There can of course be multiple
such paths but it's a pretty standard comp sci problem if this is what you re
looking to do :) Tables are nodes, edges are ForeignKey objects which you can
collect by iterating through Table.foreign_keys.
> 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
> !
>
>
>
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
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/f35b989a-2efe-43a4-aae7-eec1c3bf6aa6%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
