On 06/15/2016 09:53 PM, Kevin Murphy wrote:
Hi,
Is it possible to repeatedly add column expressions to a select object,
where the column expression involves outer joins? Ideally I'd like there
to be a several transformation functions that operate on the same select
object in a chain.
So far, the closest I've come is for the transformation functions to
accept and return both a select object (to which columns are appended)
and a join object (to which joins are appended), such that the joins are
added to the select at the end. It would be nice to be able to operate
on just the select object.
I'd like to mention another attempt at the original goal, which did not
raise an exception but generated invalid SQL (using SQLAlchemy 1.0.13).
The relevant code snippet from the runnable example below is this:
select_obj = select_obj.column(new_col).select_from(Join(
table_obj, concept_alias,
col == concept_alias.c.concept_id, isouter=True))
The resulting SQL contains a spurious `, main_table`fragment:
SELECT main_table.foo_concept_id, main_table.bar_concept_id,
concept_1.concept_name AS foo_concept_name, concept_2.concept_name AS
bar_concept_name
FROM main_table LEFT OUTER JOIN concept AS concept_1 ON foo_concept_id =
concept_1.concept_id*, main_table* LEFT OUTER JOIN concept AS concept_2
ON bar_concept_id = concept_2.concept_id
that SQL will occur if you use two JOIN objects separately in a query.
The full code follows. Note that this is a prototype of just one of a
series of transformation functions that would operate on the same select
object.
the issue is that your transform() does a select_from() which only adds
another FROM clause to the query, in this case a second JOIN so we get
the form of "SELECT .. FROM x JOIN y, x JOIN y2". For core select(),
we don't have a generative join() method like ORM Query does, so you
need to give it a finished join() object that includes everything at once:
def transform(metadata, table_name, select_obj):
"""Transform and return a Select object query by adding column
expressions
populated using outer joins. This Select object may be later passed to
different varieties of transformation function.
"""
table_obj = metadata.tables[table_name]
concept_table_obj = metadata.tables['concept']
j = None
for col in select_obj.c:
if col.name.endswith('_concept_id'):
new_name = col.name.replace('_concept_id', '_concept_name')
concept_alias = concept_table_obj.alias()
new_col = concept_alias.c.concept_name.label(new_name)
if j is None:
j = table_obj.outerjoin(concept_alias, col ==
concept_alias.c.concept_id)
else:
j = j.outerjoin(concept_alias, col ==
concept_alias.c.concept_id)
select_obj = select_obj.column(new_col)
if j is not None:
select_obj = select_obj.select_from(j)
return select_obj
from sqlalchemy import MetaData, Table, Column, Integer, String, select,
ForeignKey
from sqlalchemy.dialects import postgresql
from sqlalchemy.sql.expression import Join
def setup():
""" Create a `main_table` table, a query on it, and a related table that
will later be joined to.
"""
metadata = MetaData()
concept = Table('concept', metadata,
Column('concept_id', Integer),
Column('concept_name', String(512)))
main_table = Table('main_table', metadata,
Column('foo_concept_id', Integer,
ForeignKey('concept.concept_id')),
Column('bar_concept_id', Integer,
ForeignKey('concept.concept_id')))
select_obj =
select().column(main_table.c.foo_concept_id).column(main_table.c.bar_concept_id).select_from(main_table)
return metadata, select_obj
def transform(metadata, table_name, select_obj):
"""Transform and return a Select object query by adding column
expressions
populated using outer joins. This Select object may be later passed to
different varieties of transformation function.
"""
table_obj = metadata.tables[table_name]
concept_table_obj = metadata.tables['concept']
for col in select_obj.c:
if col.name.endswith('_concept_id'):
new_name = col.name.replace('_concept_id', '_concept_name')
concept_alias = concept_table_obj.alias()
new_col = concept_alias.c.concept_name.label(new_name)
select_obj = select_obj.column(new_col).select_from(Join(
table_obj, concept_alias,
col == concept_alias.c.concept_id, isouter=True))
return select_obj
metadata, select_obj = setup()
select_obj = transform(metadata, 'main_table', select_obj)
print("select_obj.compile(dialect=postgresql.dialect()))
Thanks,
Kevin
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
