On 06/24/2016 11:49 AM, T Johnson wrote:
I'm using SQLAlchemy Core. A user prepares an "on" expression for a join query
and passes it to a function. The goal of the function is to hide some messy details the
must be done to perform the join. Part of these details include doing a subselect. So in
the implentation, the subselect is turned into an alias, but now the on clause that was
passed in no longer references the proper table. I'd like to know how to transform an
expression so that all references to tableA are replaced with alias name tableB.
Pseudocode:
cols = [... in terms of tableA and tableB]
onclause = tableA.c.colX == tableB.c.colX
alias = sqla.select(cols).alias('hi')
updated_onclause = magic(onclause, tableA, alias)
So I need magic() to return an expression equal to:
alias.c.colX == tableB.c.colX
My hack it to manipulate the generated sql and then use sqla.text(), which is
certainly not ideal.
So how can I manipulate an arbitrary expression (intended for use as a join
clause or where clause), replacing all appearances of a table with an alias
of that same table?
this is a pretty common thing for the ORM and the Core tutorial used to
mention it but it's too esoteric of a topic for the basic tutorial so I
removed it.
The most basic form of this is the replace_selectable() method:
http://docs.sqlalchemy.org/en/rel_1_0/core/selectable.html?highlight=replace#sqlalchemy.sql.expression.FromClause.replace_selectable
Underneath, that method is using systems like
sqlalchemy.sql.util.ClauseAdapter and most fundamentally the logic in
sqlalchemy.sql.visitors. I don't know that the docstrings are in the
public-facing API documentation so I'd peruse the source. In
sqlalchemy.sql.util there's lots of systems that traverse and modify
selectables in different ways.
The replace_selectable() method might be all you need. But the topic of
regenerating selectables with different FROM objects replacing things is
a huge topic throughout SQLAlchemy, the implementations are mostly in
Core but the use cases are entirely in the ORM, and there are lots of
varieties of this kind of operation, concerning what objects are
replaced in what circumstances.
I blogged about it eight years ago at this point, hoping some computer
scientist type of person would come to me and say, "you're just doing
the XYZ technique, here's a book you can read on that!" so I'd know what
I was doing, but that never happened. That post is here:
http://techspot.zzzeek.org/2008/01/23/expression-transformations/ and
the concepts should be roughly the same today.
Anyway, see if that method works as is, though within the ORM the use
cases are almost always not quite that simple.
--
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.
For more options, visit https://groups.google.com/d/optout.
