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
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.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
