I was hoping to not do graph traversals myself, because for the more
complex cases it gets complicated, and was wondering if there was someone
who had figured out the heuristics for some of the more complex cases ^_^
I will take a look at automap, I did see it - but did not realize that it
also handles relationships.
Thanks for the input!
On Thu, Jul 11, 2019 at 7:49 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
>
> 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 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/f35b989a-2efe-43a4-aae7-eec1c3bf6aa6%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/f35b989a-2efe-43a4-aae7-eec1c3bf6aa6%40www.fastmail.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/CA%2BBKPDWv58NpNKrFLDKgOX8HAV1kekZou23MNmV8Uku1LZk6LA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
