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 !
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.
For more options, visit https://groups.google.com/d/optout.
