On 11/16/2016 12:13 PM, Michael Williamson wrote:
On Wed, 16 Nov 2016 12:02:41 -0500
mike bayer <[email protected]> wrote:
On 11/16/2016 11:58 AM, Michael Williamson wrote:
Thanks, that seems to get most of the way there. The only problem is
that calling .name on the instance returns None:
print s.query(Employee).select_from(Employee).first().name
It seems like getting rid of Employee.name and renaming manager_name
and engineer_name to just name works -- is there a reason that's a
bad idea?
whoops, forgot the column_property():
Employee.name = column_property(func.coalesce(Engineer.engineer_name,
Manager.manager_name))
Ah, that seems better. Would it be dangerous to rename manager_name and
engineer_name to just name (so that, for instance, you can always just
set name without being concerned what the concrete type is), or would
that cause issues?
you can name them the same sure.
In any case, the help is much appreciated!
the select_from() isn't needed either, I was wondering why it needed
that:
print s.query(Employee).filter(Employee.name.in_(['e2', 'm1'])).all()
print s.query(Employee.name).all()
print s.query(Employee).select_from(Employee).first().name
output:
SELECT employee.id AS employee_id, coalesce(engineer.engineer_name,
manager.manager_name) AS coalesce_1, employee.type AS employee_type,
engineer.id AS engineer_id, engineer.engineer_name AS
engineer_engineer_name, manager.id AS manager_id,
manager.manager_name AS manager_manager_name
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id
WHERE coalesce(engineer.engineer_name, manager.manager_name) IN (?, ?)
2016-11-16 12:01:38,230 INFO sqlalchemy.engine.base.Engine ('e2',
'm1') [<__main__.Engineer object at 0x7fc9acf8de90>,
<__main__.Manager object at 0x7fc9acf20050>]
2016-11-16 12:01:38,232 INFO sqlalchemy.engine.base.Engine SELECT
coalesce(engineer.engineer_name, manager.manager_name) AS coalesce_1
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id
2016-11-16 12:01:38,232 INFO sqlalchemy.engine.base.Engine ()
[(u'e1',), (u'e2',), (u'm1',)]
2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine SELECT
employee.id AS employee_id, coalesce(engineer.engineer_name,
manager.manager_name) AS coalesce_1, employee.type AS employee_type,
engineer.id AS engineer_id, engineer.engineer_name AS
engineer_engineer_name, manager.id AS manager_id,
manager.manager_name AS manager_manager_name
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id
LIMIT ? OFFSET ?
2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine (1, 0)
e1
Joined inheritance lets you do that if you put the name on the
employee, but I'd like to keep the name on the individual
subtypes. For my actual use case, some of the columns on one of
the subtypes is calculated from other bits of SQL: in other
words, the way the columns are written out in SQL needs to differ
for each type, but I'd like to be able to write queries to select
the columns without having to explicitly write out the union each
time.
So for better motivation, imagine if Engineer.name was defined as:
@hybrid_property
def name(self):
return "E" + str(self.id)
@name.expression
def name(cls):
return ("E" + cls.id.cast(String())).label("name")
while Manager.name remains an ordinary column.
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
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 [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.
